Update any changed cells

[SpoctorDock]

Hi all,

I'm pretty new to VBA, but I've dabbled a bit and learnt C in my time (although most has now seeped out one ear onto the floor). I'm attempting to write a macro that ultimately will automatically run when saving the file and changes the data in any cells that have been changed.

I'm a little stuck on the first part as all the other results on google are for specific cells or ranges. What I'm looking for is some idea on how to update any cells that have been changed (within a small range spanning multiple rows and columns) or if easier, update any cells that have a certain value in them.

Can anyone help?

 

[AlphaFrog]

I don't follow what you mean by; "update any cells that have been changed". Update them to what? If you've changed them, are they not updated? Perhaps if you gave a short example, that would help.

 

[HalfAce]

Hi Spock,
There are a couple sections to your question that should be addressed individually.

1) "a macro that ultimately will automatically run when saving the file"
For this you'll want to invoke the BeforeSave event. (Found in the ThisWorkbook module.)
The code that solves the rest of your question would go in there.

2) "update any cells that have been changed (within a small range spanning multiple rows and columns)"
For something like this you could record those values as they are when the sheet gets activated. (These values will be compared to the existing values in those cells upon saving - to determine if they've been changed.)
What is the range of interest for this?
Or - ("if easier, update any cells that have a certain value in them.")
This may indeed be the easiest method to use. What value(s) would you be looking for that you want changed?

And... for either scenario in the second part above, what would you want to change these values to? (Just back to their original value(s)?)

Another idea might be to either not allow changes to your range to begin with, or perhaps automatically reverting them back when they get changed in the first place, not necessarily waiting until the save event to do it.

 

[SpoctorDock]

My apologies.

The aim is to have the user enter "Y" or "y" in the cells and the macro will automatically change the value in the cells to the current date upon closing or saving the file.

The main problem I'm having is trying to figure out how to update cells within a range based on a value (in this case "y")



Oh, also, this is my code so far (it's a little mishmashed)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Range("I:R") Then
        Select Case Target.Value
        Case Is = "Y": Call Data_Entry
        Case Is = "y": Call Data_Entry
        Case Else: Call TestElse
        End Select
    End If
End Sub

Sub Data_Entry()
Sheet2.Range("I:R").Value = Date
End Sub

 

[HalfAce]

To use the BeforeSave event, this should work. (In the ThisWorkbook module.)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim MyRng As Range

'Set your range of interest to be from _
 A1 to C10 on sheet 1 - (amend this to suit)
With Sheets("Sheet1")
  Set MyRng = .Range("A1:C10")
End With

'Make the changes...
MyRng.Replace What:="Y", Replacement:=Date, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False

End Sub

Or, possibly easier would be to just insert something like this into the sheet module of the sheet these entries will be made in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRng As Range

'Set your range of interest to be from A1 to C10 - (amend this to suit)
Set MyRng = Range("A1:C10")

'Make the changes... as the "Y" entry gets made.
If Not Intersect(Target, MyRng) Is Nothing Then
   If UCase(Target.Value) = "Y" Then Target = Date
End If

End Sub

Hope it helps.

 

[HalfAce]

Or you could just amend your posted code to something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("I:R")) Is Nothing Then
        Select Case Target.Value
          Case "Y": Target = Date
          Case "y": Target = Date
        End Select
    End If
End Sub

You don't really need to use a select case statement here (with only 2 choices) but it sure won't hurt anything.