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

Free Microsoft Excel 2013 Quick Reference

Detect when cell data has changed?

Is there a way in VBA to be notified when a cell has changed with the row and column number and the cell contents?

I am hoping for some event to be fired, but if not I can keep checking a changed flag if that is what it takes.

Thanks,
Bob Hiller


Post your answer or comment

comments powered by Disqus
Hello experts.

My question is:

How do I detect the cell data change in order to run a Macro when this happens?

Example:

If data in cell D5 changes then run Macro1

I'm using Excel 2003. Thanks in advance.

Hi all, I've got a worksheet where I want a value in column G to change to today's date when the data is changed in the same row in Column F. It's a dropdown in column F, but I don't think that matters. This is what I've got so far, and I'm a bit stumped

Private Sub Worksheet_change(ByVal Target As Range)
If Not Application.Intersect(Target, "F5:F60") Is Nothing Then
If Target.Cells.Count > 1 Then
Exit Sub
Else
Dim iCurrentRow As Integer
iCurrentRow = Target.Row
Set Form(G, iCurrentRow) = TODAY()

Else 'nothing changes if the status doesnt change
Exit Sub

End Sub

I'm stuck on that Set Form row there, I know it's kind of wrong, but this is my best guess, I'm not sure how to use that iCurrentRow properly, or how to change the data in a cell from this page. Any help would be great, thanks!

I would like to know how to using conditional formatting OR VBA code to highlight the changes in a cell that has changed from it's orginial value. I've tried the track changes option , but for some reason I can can not get it to print and I have to set my options to a shared workbook to enable it.

I prepare a weekly planing workbook and would like to have the workbook highlight any changes made. The workaround file I have now is too big:-( Any help is appreciated:-)

hi, i have the vba below who alow me to hightlight changes if the orginial cell value has changed. the problem is that it's stay highlight week after week and the goal is to know if there was a change last week. So the ideal is to insert a vba that would say hightlight if the cell value has changed between 7 days ago and today. what changes do I need to do?
p.s: i work with excel 2010 thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "F10:F309"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = 6
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

I have a worksheet that has live data populting some cells, I need to write the time when the cell's data changes. the cell S:24 is a cycle counter, when cell s:24 changes I need to write the time it changed into a new cell.

At the bottom, I have the entire module in case I’m missing something that
may be related but I’m missing… Otherwise...

In this module, when K1 cell is selected it returns the current worksheet
back to it’s default using a template. Once this routine runs I turn the
value of cell K1 to “default”. Now, when any Cell value is changed, I want to
turn the value of K1 to “Changed”. However right after I rebuild the
worksheet and set the value of K1 to "default", the value of K1 is
immediately being changed back to “Changed”. Could I be using the wrong
“Worksheet_Change” sub?
--------------------------

'------------------------------------------------------------------------
' [default/Changed!] Button - Re-Build Program Summary Template
'------------------------------------------------------------------------
If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary True 'Runs rebuild with Prompt
Range("K1").Value = "default"
End If

--------------------------
Sub at the bottom of the page intended to change K1 to “Changed” when any
Cell value is modified. *********************
--------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
Range("K1").Value = "Changed" '<---- 2 -----
ws_exit:
Application.EnableEvents = True
End Sub

--------------------------
Entire code ****************************
--------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Dim srcBettingTemplateWs As Worksheet
Dim raceParkPrefix As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String
Dim ImportRequested As String

Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcBettingTemplateWs = Sheets("@TempleteBetting")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")

raceParkPrefix = Left(srcProgramDataInputWs.Range("H3").Value, 3)

'------------------------------------------------------------------------
' [BET] Button - Create Bet Sheet
'------------------------------------------------------------------------
If Target.Address = "$A$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
Dim exists As Boolean
Dim ExistingBettingWsName As Worksheet
Dim NewBettingWsName As Variant

Range("N3").Select

NewBettingWsName = Format(srcProgramDataInputWs. _
Range("F3").Value, "mm-dd ") & _
Left(srcProgramDataInputWs.Range("H3").Value, 3)

exists = False
For Each ExistingBettingWsName In ThisWorkbook.Sheets
If ExistingBettingWsName.Name = NewBettingWsName Then
exists = True
Exit For
End If
Next
If exists Then
MsgBox "Betting Worksheet for [ " & NewBettingWsName & _
" ] already exists. [RENAME] or [DELETE] that Worksheet and try
again."

Else
If MsgBox("Create Race Betting Worksheet for [" &
NewBettingWsName & "]", _
vbYesNo) = vbYes Then
Dim NewBettingWs As Worksheet
Dim NewBettingWsTabColor As Variant
Dim raceParkPrefixList As Variant
Dim src As Variant

i = 6
raceParkPrefixList = srcProgramDataInputWs.Range("N" &
i).Value
Do Until raceParkPrefixList = ""
raceParkPrefixList = srcProgramDataInputWs.Range("N" &
i).Value
If raceParkPrefix = raceParkPrefixList Then
NewBettingWsTabColor = srcProgramDataInputWs.Range("O" & i).Value
i = i + 1
Loop
Range("N3").Select

srcBettingTemplateWs.Copy before:=ActiveSheet
Set NewBettingWs = ActiveSheet
With NewBettingWs
.Name = NewBettingWsName
.Unprotect
.Tab.ColorIndex = NewBettingWsTabColor 'or replace with
index number

src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 0
Do Until src = ""
srcBettingTemplateWs.Rows("11:22").Copy .Cells((j *
12) + 11, 1)
i = i + 12
j = j + 1
src = srcProgramDataInputWs.Cells(i, 2).Value
Loop

.Protect
End With
End If
End If
End If

'------------------------------------------------------------------------
' [default/Changed!] Button - Re-Build Program Summary Template
'------------------------------------------------------------------------
If Target.Address = "$K$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
ReBuildProgramSummary True
Range("K1").Value = "default" '<--- 1 -----
End If

'------------------------------------------------------------------------
' [IMPORT] Button - Import in different Race Track file
'------------------------------------------------------------------------
If Target.Address = "$B$1" And ActiveSheet.Name <> _
srcProgramSummaryTemplateWs.Name Then
Dim SelectedTxtInputFile As Variant
SaveDriveDir = CurDir
MyPath = ThisWorkbook.Path & "/RaceData-XLS-Ready"
ChDrive MyPath
ChDir MyPath

SelectedTxtInputFile = Application.GetOpenFilename( _
"Race Program Input Files (*.txt),*.txt", , _
"Select which RACE Program to import", , False)

If SelectedTxtInputFile = "False" Then
Range("N3").Select
Else
srcProgramDataInputWs.Unprotect
' srcProgramDataInputWs.Range("A3:H242").ClearContents
srcProgramDataInputWs.Range("A3:H900").ClearContents

With srcProgramDataInputWs.QueryTables.Add(Connection:= _
"TEXT;" & SelectedTxtInputFile _
, Destination:=srcProgramDataInputWs.Range("A3:H900"))
.Name = "ImportProgramData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
srcProgramDataInputWs.Range("H2").Value = _
Format(srcProgramDataInputWs.Range("F3").Value, "mm-dd ") & _
Left(srcProgramDataInputWs.Range("H3").Value, 3)
srcProgramDataInputWs.Protect
ReBuildProgramSummary False 'call sub and turn off prompt
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
Range("K1").Value = "Changed" '<---- 2 -----
ws_exit:
Application.EnableEvents = True
End Sub

Hi everyone. It's my first post in these parts, so please be gentle!
I'm new to Excel programming, and am developing a dashboard application to access OLAP data from SSAS using the Excel CUBE functions.

This mostly works really well, except for one persistent problem:

I need to be able to hide some worksheet rows based upon the number of members returned by a CUBESET function.
I implemented this by using CUBESETCOUNT to populate a cell with the number of members returned by the CUBESET, then in the worksheet_change event, I hide the rows that I don't want to be visible.

This works sometimes, but often the OLAP data takes a second or two to be returned, by which time the worksheet_change event has fired and looked at the value in the CUBESETCOUNT cell BEFORE it has been populated, resulting in a type mismatch error in the code.
I've also tried using the worksheet_calculate event, but with no success.

So, I suppose that this is all a roundabout way of asking whether it is possible to detect when the calculation process has finished, so that I can execute the code to hide the rows only when I know that the cell containing the CUBESETCOUNT value is valid?

I'd really value the input of the group, as I've exhausted my knowledge, and am starting to think that it can't be done.

Thanks in anticipation. Simon.

Hi,

I have a dropdrown that is linked to a cell (Data!E26). I would like to know how in VBA how it is done so that when a selection is made, a macro (clearFormulas) will automatically run. The dropdown is on a different sheet and I understand that the Worksheet_Change function will only work if the target cell is actually selected and changed. How can i do this if the Data!E26 is being modified by the dropdown?

Thank you in advance for your help!!

Hi Everyone,

I've got an ODBC Query which is set to automatically refresh if the
selection criteria cell (to which it is linked) changes.

Is there a way to programatically detect that the refresh has occured?
I need to update another cell after any refresh.

Perhaps I need only to detect if the selection criteria cell contents
has changed.

If anyone can suggest either solution, that would be great.

Regards

Greg Glynn

Good day,

I have observed an interesting problem with Excel 2003, in that the
excel charts (any type) do not update when the source data is changed
when one has a large excel workbook (I am currently at 160 MBytes).
This is a new behaviour that appeared when I surpassed 65,536 or more
dependencies to unique references (the level or number of links between
formulas).

Any ideas on how to fix this would be greatly appreciated.

Thanks,

James

Hi all, probably a bit of simple coding - but I would like my Excel sheet (set to manual refresh) to calculate when cell value "Y2" changes.

Y2 is controlled by a data validation drop down, when the users changes the value I would like the sheet to refresh - rather than pressing F9.

Thanks in advance

Hi,

For some reason, the charts in my excel workbook do not update automatically when the data are changed. I have set Calculation to Automatic, but it still doesn't work. They update only if I close and reopen the workbook. Is it due to a problem with setting? How can I get the charts to automatically update?

Thanks

Hi,

I'm trying to automate a spreadsheet that downloads a set of tickers from Bloomberg and then fills across a range with a series of data.

I have most of the VBA code I need, but my problem is that on opening the spreadsheet, the code moves on to fill the range before all the tickers have downloaded correctly.

I've tried using application.wait and setting up loops until the time reaches a certain value, but these seem to stop Excel updating the list of tickers (presumably the loops stop excel gathering the data?).

I suspect that what I need to use is something like the querytable. afterrefresh, but am not sure I'm using it correctly since my knowledge of VBA is very rudimentary.

Any help much appreciated - in particular:-
(i) is there a way of getting the sheet to open, and then just wait for a period of time while still allowing the tickers to download, and/or

(ii) any way to detect when the tickers have finished downloading before the next step of code can commence?

Thanks & best wishes,

Hi all,

I'm trying to target individual cells whose value has been updated / changed after every Calculate event.

The worksheet I'm working on is pulling live data from an external source (updates almost every second), and I'm hoping to highlight each cell that has changed with a color code (representing which band it falls in) each time a Worksheet_Calculate() event takes place but I'm not sure how.

I just want the changed cell to flash once, which I know how using the OnTime method. I just don't know how to determine/trace which cells have changed. *Comparing with previous values cell-by-cell is NOT feasible as the data is too extensive.

Any solutions?

Thanks in advance.

I need to detect if a user has changed the sheet tab name. We have a tree UI that needs to dynamically be updated to reflect the current sheet names, however I am having difficulty finding any event or means of detecting when a user has changed the sheet name. Other than running a background thread to refresh the tree with current excel sheet names, I would like to have a more elegant solution. Any advice you all can provide is greatly appreciated.

Thanks in advance,
Amit Nayar

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 All,

I understand how to set up a worksheet so that a macro is called when the
value of a specific cell changes (i.e. when Cell A1 changes), but how do you
call a macro when a cell within a range changes (a1:a10).

I know this is simple but I can't find the right arguments?

Any ideas?

Ta

Andi

Hello all, I am using the Bloomberg add-in to download data about a company, and then copy and pastevalue the data into another worksheet. Then, I repeat for the next company. I have about 150 companies to research and the list keeps growing, so I have written a macro to copy and paste the company ID into the Bloomberg command, and then copy and paste the data that comes up into the other worksheet, and then repeat for the next company.

The problem is that the macro runs so fast, that the data in the Bloomberg add-in doesn't have time to update after the new company ID is posted into it. There needs to be a "delay" of at least 4 seconds between when the company ID is posted and when the data is copied and pasted, to allow for the data to update.

I have already tried two possible solutions: 1) The Application.Wait function, which just stops both the macro and the download, and 2) pasting the Bloomberg function all over again along with the company ID. Neither of these have worked.

Is there a solution that would delay the macro a few seconds, but allow the download to continue for the data to update? Maybe it would include a function that detects that the data has changed, and allows the data to actualize before copying and pasting. Any help?

Thanks.

Need help to write formula or suggest a way of doing.

When Cell F3 is not equal to "CURRENT"
then where (G3:G10) contains X
change X to A.

Hi all,

Hope this comes across clearly (also, please consider the cells/rows/columns noted below as examples ... )

I'm looking to create a macro that will:

1. "read" down a column I designate (i.e. column 'C'),
2. then notice when a cell differs from a previous cell (i.e. cells c1-c10 have text "V123" and cell c11-c13 changes to text "V193", and cell c14-c20 changes to text "V223" - in essence flag these "group" changes),
3. then apply a conditional format to those "groups" (i.e. apply format to each group c1-c10, c11-c13, c14-c20) so I can review. Specifically, I'd like to insert a row above where the change occurs so I can sum the totals in the group previous (in column 'D', as example).

Worth noting, the values in the columns are products that shipped, so vary per order.

As always, any help is appreciated !
jkm

I want to have todays date entered cell A1 whenever anything is first entered into cell A2. The date entry must remain the same and not change to refer to the computers clock. If cell A2 is changed then the date in A1 should change and update to the date on which it was changed.
Is this possible?
RC

Hello,

I can't figure out how to refresh data in a cell with a dropdown list when the source list change.

Say I have a drop down list with 4 options: Phase 1, Phase 2, Remediation and Year End. The user selects Phase 1. In the source list, Phase 1 is changed to Phase 4. I don't know why, but Excel does not refresh the users choice and where it had Phase 1 now read Phase 4.
I have attached an example. Any help is really appreciated.

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!

Im trying to change a worksheets cell value when an end-user changes the
value of a txtBox on a customized form.

Essentially Im pulling information from an excel file and some of that
information may need to be changed by the end-user.

how to I submit any changes back to the spreadsheet once a end-user
changes a field?

Here is the code for the lookup feature to give you an idea of what Im
working with.
================================================== =
Dim rng As Range

With Worksheets(1)
Set rng = .Columns(6).Find(txtCustSSN.Text)

If Not rng Is Nothing Then
'Populate frmCustLookup with Customer Information
txtLname.text = .Cells(rng.Row, 3).Value
txtFname.text = .Cells(rng.Row, 4).Value
txtMname.text = .Cells(rng.Row, 5).Value

Else: MsgBox "Customer Data Not Found"

End If

End With

End Sub
================================================== ===

So for example we look up Ms. Smiths record (by SSN), and need to
change her last name since she has recently been married.

when the user types: Andersen over top of Smith how do I send that
update back to the worksheet?

I'VE CREATED A COMMAND BUTTON "CMDUPDATERECORD" TO SUBMIT THE
CHANGES, BUT IM NOT SURE WHAT CODE TO USE.

Thank you for your advice, I know the solution should be easy, but my
coding is a little rusty.

--
Mcasteel
------------------------------------------------------------------------
Mcasteel's Profile: http://www.excelforum.com/member.php...o&userid=15698
View this thread: http://www.excelforum.com/showthread...hreadid=274237


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