Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

[Solved] VBA: Macro Runs When Cell Value Changes

Can someone tell me how to make a call to a specific macro everytime a a cell value changes?

For example, if new data is entered into cell A1, then it would call the macro named "Process".

Thanks in advance!


Post your answer or comment

comments powered by Disqus
Hi!

I have a spreadsheet that gets values from Access using Microsoft query. The values queried change depending on a date variable, which I input in say cell A1. I have ticked the "Refresh Automatically when cell value changes" when setting up the parameters, but the query doesn't seem to refresh when I change the date in A1. Also I'm not sure, but it looks like sometimes it works, and sometimes it doesn't.. Any ideas?

hi my friends plz i need help
how Help me how automatically send email when cell value changes

Hey Dears,

I have excel 2003 file for Time card for several employees.
In this I have to run a macro when a cell value change, whatever the value is. < or >.

I have below macro to run when a cell value changes.
For example when i enter any value in A5, at the same time D5 is also change, and macro runs.
But during this macro execution D5 is changes again itself and this macro runs again and it is repeating the macro whenever D5 changes.

But I need macro to run only one time when D5 changes, not to repeat.
Please can anyone help me in this regard.
Thanks in advance.

regards
ebe

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'
  Range("CurrRec").Value = Range("AL38").Value
  
    If Sheets("Input").Range("AR3").Value <> olval Then
    ViewLogUp
        End If
  Exit Sub
       olval = Sheets("Input").Range("AR3").Value
End Sub


I want to run a macro when a cell value changes. I have the following
code to do this:

Private Sub WorkSheet_Change(ByVal Target As Range)

If Target.Address = "$E$5" And Target.Value = 1 Then
Call Archive
End If

End Sub

When I manually enter a 1 into cell E5, the macro runs fine. However,
E5 normally contains =IF(G5=5,1,0) so when G5 is 5, E5 is changed to 1
(auto calculate is on) but my macro does not run when this happens. It
will only run when I manually enter 1 or when I click the formula and
press enter to accept it. Why is my macro not running?

Hi,

I am pretty new in VBA, and I am trying to write simple program by looking at examples. I hit a performance problem and I need advise.

I have a main worksheet which is constantly taking in data from MT4 platform, and many data fields (cells) in the worksheet are being updated every other second. I only observe one cell that whenever it gets updated, (i.e. cell value changed by other VBA modules in the same worksheet, no user or manual interruption), I will trigger a module which will send an alert message via Outlook. All programs are written and run properly. However, I use the following procedure in the main worksheet to fire the procedure for sending alert messages.


	VB:
	
 Worksheet_Calculate() 
     
    If Sheets("Main").Range("L3").Value >= 1 Then 
         
        Call Send_alert 
         
    End If 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The approach has caused the Worksheet_Calculate procedure being fired all the time as there are many cells being updated every second. I believe that each update of a cell in the Main worksheet will trigger the worksheet_Calculate procedure. It slows down the entire PC performance.

I need some advise if worksheet_calculate is used in the correct manner, or should I use other worksheet event, which I can't think of any. To recap the issue, I just want to monitor a particular cell for change of value and fire a module to send alert if condition of that cell is met. But I do not want to procedure to be fired all the time when other cells are being updated.

Any experts out there whom can land a helping hand will be greatly appreciated.

Novalight

Hiya guys Im wondering if anyone can help, i need to know how i can automatically run goal seek when a certain cell value changes.

Hello

I want to be able to run a macro automatically everytime I change a cell. For example, I have cell K1 and K2 that contain a date and a sales rep name. I want the macro to run automatically everytime I change either of these two cells. Is there any way to do this?

Thanks,
Oscar

Hello,

There is a single, critical cell in a workbook that i need to be
notified when the value changes in any way. I would like to be
notified by email. My office runs on Exchange Server and Office 2003.

The value might change a couple of times prior to saving, so it
would be best if the email that was generated was done after saving and
on closing the spreadsheet.

I do not need to send the worksheet or any data, just a note that
states that the critical cell has changed.

Any help would be appreciated.

jonah

Hi Readers,

Could anyone please help with an annoying obstacle I have enountered?

I am using the 'Refresh automatically when cell value changes' option
for MS-Query criteria.

My criteria cell is a date, in the format: 2005-05-31.

I have sussed all the formatting issues, but my problem is this: My
criteria cell is actually the result of a formula used to calculate the
end of the month, from another cell containing the month I'm looking
at.

The criteria cell formula is as follows:

=TEXT(DATE(YEAR(TODAY()),D21+1,0),"YYYY"&"-"&"MM"&"-"&"DD")

The problem is, that when the month cell is updated, my criteria cell
formula then recalculates my new 'end month date' but my SQL report (on
another worksheet) doesn't refresh.

I have found that directly typing in my new 'end month date' forces the
refresh, so I believe the problem is as follows:

The 'auto refresh' function only 'watches' the formula, which of course
remains static, and not the 'value' of the cell which IS changing.

Does anyone know a way around this at all?

I know this could be done using VBA, but I'd much prefer not to employ
its use, if possible, for reasons not worth boring you all with )

I am sincerely grateful for any comments or suggestions anyone may
have.

Thanks!

Elliot

Hi Readers,

Could anyone please help with an annoying obstacle I have enountered?

I am using the 'Refresh automatically when cell value changes' option
for MS-Query criteria.

My criteria cell is a date, in the format: 2005-05-31.

I have sussed all the formatting issues, but my problem is this: My
criteria cell is actually the result of a formula used to calculate the
end of the month, from another cell containing the month I'm looking
at.

The criteria cell formula is as follows:

=TEXT(DATE(YEAR(TODAY()),D21+1,0),"YYYY"&"-"&"MM"&"-"&"DD")

The problem is, that when the month cell is updated, my criteria cell
formula then recalculates my new 'end month date' but my SQL report (on
another worksheet) doesn't refresh.

I have found that directly typing in my new 'end month date' forces the
refresh, so I believe the problem is as follows:

The 'auto refresh' function only 'watches' the formula, which of course
remains static, and not the 'value' of the cell which IS changing.

Does anyone know a way around this at all?

I know this could be done using VBA, but I'd much prefer not to employ
its use, if possible, for reasons not worth boring you all with :o)

I am sincerely grateful for any comments or suggestions anyone may
have.

Thanks!

Elliot

This is driving me nuts! It is mainly due to the fact that I am a new user to this. I have a workbook that contains several worksheets. I have the correct formulas in place, and I have created a Macro (Called Test_1) that seems to work so far. How ever I would like the Macro to run when cells G12 through G122, and H12 through H122 (these are on a work sheet named Inv.) have a value greater than 0. Or are changed from their current value. I have tried several times to get the VB code correct, but I just keep missing something. Any suggestions?

hi,

i'm having a little problem with this one. i've got a cell that if the value is changed a macro has to be run.

i'm using the selection change event of the worksheet but all if seems to do is fire if the cell is selected not if the cells value is changed?

this is what i have so far?

Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
Dim Grade As Integer

For i = 1 To 100
    If Cells(i, "E") = "System Grade:" Then Exit For
Next

Grade = Cells(i, "F")

 If Target.Address = "$F$" & i Then
        MsgBox ("Battery Calculation are being recalculated")
  End If
  
End Sub
how can i get it to fire only if the cells value is changed? i.e. so if the target cell value doesn't equal grade?

Thanks

jon

Hi,

I use the following macro to insert a header onto a chart located in a
worksheet called "chart" based on a cell reference in a different
worksheet called "jur":

Sub RefHeader()
With ActiveSheet
.PageSetup.LeftHeader = "&""Times New Roman,Bold""&14 " &
Sheets("jur").Range("A2").Text
End With
End Sub

I would like to have the above macro automatically run whenever the
value of a specific cell (C3) in the "jur" worksheet changes. Is this
possible? I have a feeling it must be done with a worksheet change
event function, and I have read the helpful Websites of David
McRitchie, Ron de Bruin, and Charles Pearson, but I fear that I'm too
inexperienced to understand how to make it work without a bit more
nudging along.

Thanks,
Scott

Hi all! I hope someone can help me out with this macro. I'm new to VBA and I'm really struggling. I'm obviously not a developer of any kind, but I am trying to help out my local Fire Department with this time sheet workbook. Here's what I'm trying to do.

I have a workbook that has a separate sheet for each month Jan-Dec, labeled as such. Across the top of each are my dates (1-31), and down column A are the categories for our time reporting (i.e., Training, Meetings, Duty, etc.). I also have 2 extra sheets for data about 2 of our categories (I'll explain this in a minute).

Now, for 2 of our categories I need to get a little more detail on what they did. So when a user enters their hours for that day under one of these categories (like C16), I need to prompt the user for a brief text description of the work. This description will be required if they enter 'any' time under this category, so if they hit OK I need to save this information and if they hit CANCEL or don't enter a description, they should not be allowed to enter time. Once they hit OK with a description entered in the box, I need to take that description and place that text in one of those extra sheets I spoke of, along with the date where they entered the time (from my column header), and the hours they entered. I just need to place this information in this extra sheet row by row, just to track it. When I place this data in the extra sheet, I'd like to be able to find the next blank row, copy the date in Col A, Description from InputBox in Col B, and the hours in Col C. Then make sure it goes back to the original Monthly sheet the user was entering into. These extra sheets will be locked to the users, it's only for my reporting purposes. I have tried several things, and have come to a "sticking" point.

Here's the code I have thus far, but I receive errors on the Range line (Range("B1").Select) and I don't know enough about VBA to understand the problem:

'Run macro TrainingDesc when cell is changed
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("C16:AG19")) Is Nothing Then TrainingDesc
End Sub

'TrainingDesc Input Macro to build database list
Function TrainingDesc()
Sheets("January").Select
Dim TrgDesc$
TrgDesc = InputBox("Please enter a description for the training.", "Training Description")
Range("C16:AG19").Select
Sheets("Category E Data").Select
Range("B1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TrgDesc
Sheets("January").Select
End Function

I have yet to add the Date and Hours to the extra sheet too. I haven't gotten that far. For the life of me I have been pulling my hair out on this one just to get the InputBox data saved. It's probably a real simple fix, but I just can't see it. Any help is greatly appreciated!!! Thank you!

Resource Planning.xlsx

Hello all, I need help with an macro to notify me (by changing a cell background color to red), when the value (always number format) changes in any cells in the row. In the file I've uploaded, I want the background of cell E3 to change to red, if any of the values in cells F3:AN3 change from their current values. The numbers in cells F3:AN3 will be entered manually or thru copy and paste of the row, and there won't be any formulas. Likewise, if any values in cells F4:AN4 are changed, I would like cell E4 to change to a red background, and so on for each of the rows in the chart. Not all rows will always have a value, so I would be looking for changes from "" to any #, or from one # to another #, or from any # to "". Ideally this would be an event macro that does not have to be run manually.

The follwing is the code I've started working with:

	VB:
	
 Range) 
    If Not Intersect(Target, Range("F3:AN3")) Is Nothing Then KeyCellsChanged 
End Sub 
 
Private Sub KeyCellsChanged() 
     
    Dim Cell As Object 
    For Each Cell In Range("E3") 
        Cell.Interior.ColorIndex = 3 
         
    Next Cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, this macro seems to run regardless of whether the number in the cell is changed, as long as I press enter it highlight E3 as red.

Any help is much appreciated!

Hi
I have a sheet named "Risk Register" and another named "Outside Residual Risk Threshold". I need an event macro on the worksheet "View code" section, so that any time a value changes in column AF on "Risk Register", the filter on rows 8:39 in "Outside Residual Risk Threshold" is refreshed. Both sheets are protected.

The code I tried in the "view code" or "Outside Residual Risk Threshold" was as below...


	VB:
	
 Worksheet_Calculate() 
    On Error Resume Next 
    If Me.FilterMode = True Then 
        With Application 
            .EnableEvents = False 
            .ScreenUpdating = False 
        End With 
         
        Me.Protect Password:="risk*123", DrawingObjects:=True, Contents:=True, Scenarios:=True _ 
        , AllowFormattingCells:=True, AllowFormattingRows:=True, UserInterfaceOnly:=True 
         'Change range and filter criteria to suit
        Me.Range("A8:N39").AutoFilter Field:=5, Criteria1:="" 
         
         
        With ActiveWorkbook 
            .CustomViews("Mine").Delete 
            .CustomViews.Add ViewName:="Mine", RowColSettings:=True 
            Me.AutoFilterMode = False 
            .CustomViews("Mine").Show 
        End With 
         
         
        With Application 
            .EnableEvents = True 
            .ScreenUpdating = True 
        End With 
    End If 
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I haven't set this up correctly, since everytime you alter any cell in any worksheet, including other files open in Excel at the same time, this event kicks off. Also, it always ends on the "Outside Residual Risk Threshold" sheet, when I need it to end on the sheet you started on before the event ran (generally "Risk Register", but not always).

Are you able to tell me how best to write in a target range (changes in Col AF) that would trigger the event to kick off, so that it doesn't occur at any other time? And also how to stop it ending on the "Outside Residual Risk Threshold" sheet when it's finished it's run?

Thanks in advance
Elizabeth

looking for the VBA code that will increment a cell value by 1 when the macro is run.
Thanks.

Hey All,

I have a Macro that will take a Value from a cell and display that value in a Textbox.

What i want to do is to automate the Macro to update everytime that specific cell value changes.

What would be the best way in doing this???

Thanks

Tom

i am just trying to copy a cell value when that cell value changes.

the cell value (b3) is generated by a formula based on the value of two other cells(B1+B2) ,when either of those cell values change

i am trying to create a macro that copies B3 (everytime it changes and adds it to a list starting at B4

i have tried setlinkondata and worksheet change but still my macro only runs once
not much programing experience in VB so any cut and past guidane appretiated

The following macro is linked to an IF statement in cell W59. I would like the macro to play only ONCE when the IF statement is true. The macro will continuously play the sound if the reference value in cell 059 changes. Is there a way to modify this method so that the macro only plays a sound ONCE?

Private Declare Function PlaySound Lib
"winmm.dll" _
  Alias "PlaySoundA" (ByVal lpszName As String, _
  ByVal hModule As Long, ByVal dwFlags As Long) As Long

    Const SND_SYNC = &H0
    Const SND_ASYNC = &H1
    Const SND_FILENAME = &H20000

Function SoundMe() As String
    Call PlaySound("c:windowsmediatada.wav", _
      0, SND_ASYNC Or SND_FILENAME)
    SoundMe = ""
End Function


Is there a way to make it so that if a cell value changes, a macro is called?

Hi All

I hope you can help me refine the attached sample spreadsheet with my attempt at a macro which when cell c3 has the value changed from a cell drop down list should automatically run the DoFilter macro. I cannot figure out how to do this so have to add a picture and assign the macro to it.

The macro should look at the new value in Sheet2 C3 and use the value as the filter criteria for the filtering of column A in sheet1.

I hope this makes sense.

the code I am using is:

Code:

Public Sub DoFilter()

    Dim School As String
    
    School = Sheets(2).[C3].Value
    
    If School = "School" Then
        Sheets("Sheet1").Select
        Rows("4:4").Select
        Selection.AutoFilter
        Sheets("Sheet2").Select
    Else
        Sheets("Sheet1").Select
        Rows("4:4").Select
        Selection.AutoFilter
        Selection.AutoFilter
        Selection.AutoFilter Field:=1, Criteria1:="" & School & ""
        Sheets("Sheet2").Select
    End If
End Sub

Code End:


Hello friends,

This is my first post and I think this forum has really brilliant people cooperating.

My inquirie is that I would like to create a macro in excel that can hide and unhide columns automatically when a cell value changes.

ie. When A1=pizza hide column d and show column c
When A1=burger hid column c and show column d

I want to apply this macro to a file that contains a lot of columns for each type of information.

I will appreciate any help I can get about this.

Hi to everybody:

I'm doing a function to comprobate that a cell value has 11 digits.
I'm not use only a validation because the the cell value can start with a 0.
So I make this function

	VB:
	
 
 'This function is to get all the characters from a cell
Function ValidaSeguro(SS As String) As Boolean 
    Dim strArray() As String 
    Dim lLoop As Long, lCount As Long 
    Dim Respuesta As Integer 
     
     
     
    lCount = Len(SS) 
    If lCount  11 Then 
        ValidaSeguro = False 
        Respuesta = MsgBox("Don't have 11 digits") 
         
        Exit Function 
         
    End If 
     
     
    Redim strArray(lCount - 1) 
     
     
    For lLoop = 0 To lCount - 1 
         
         
        If EsDigito(Mid(SS, lLoop + 1, 1)) = False Then 
            ValidaSeguro = False 
            Respuesta = MsgBox("No digit" + Mid(SS, lLoop + 1, 1)) 
            Exit Function 
        End If 
    Next lLoop 
     
    ValidaSeguro = True 
     
     
     
End Function 
 
 'function to know if is digit a character
Function EsDigito(caracter As String) As Boolean 
     
    Select Case caracter ' Evalúa Número.
    Case 0 
        EsDigito = True 
         
    Case 1 
        EsDigito = True 
    Case 2 
        EsDigito = True 
    Case 3 
        EsDigito = True 
    Case 4 
        EsDigito = True 
    Case 5 
        EsDigito = True 
    Case 6 
        EsDigito = True 
    Case 7 
        EsDigito = True 
    Case 8 
        EsDigito = True 
    Case 9 
        EsDigito = True 
    Case Else ' Otros valores.
        EsDigito = False 
    End Select 
     
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I need to execute this function when a cell change or use the function as a validation, any sugestion?


No luck finding an answer? You could always try Google.