0

I have an Excel spreadsheet that is acting as a form with a number of input boxes (which is as my client has requested). I have "Clear" and "Save" buttons the latter of which pushes the data through as a record to an Access database. I have been able to prompt using msgboxes so that the user can't clear without first saving, and subsequent saves are challenged (as they are most likely accidental dupes) but I am unable to figure out how to prompt for a re-save should the user make changes to the form after the first save (clearing the form resets the save flag, though). Can this even be done (userform is not an option)? Ideally I would need a macro that would detect when any one of a certain few cells has a change made to it.

Community
  • 1
  • 1
Zephyr
  • 138
  • 2
  • 13
  • 1
    Using the `worksheet_change`-event on the worksheet should at least detect any change in the worksheet. I assume you have some boolean to check whether the sheet has been saved or not, so I would think combining the two might provide a solution? – eirikdaude Jun 15 '15 at 11:03
  • Hi, yes I thought of that. Unfortunately I'm using now() to get date and time stamps, and I think worksheet_change would pick this up? Also it would trigger on clearing...? Yes, I'm using a binary flag. – Zephyr Jun 15 '15 at 11:13
  • Ah, OK - I think I've figured it out. Your comment made me go and revise my knowledge of event macros, so many thanks! :) – Zephyr Jun 15 '15 at 11:20
  • You can put `Application.EnableEvents` to false in the macro clearing the sheet, at least, to prevent any further events from triggering. As for `now()` triggering `worksheet_change`, I am not sure, but you can prevent this from happening by putting e.g. `if not intersect then ... end if` around what you want to happen when the worksheet is changed? However, according to [this answer](http://stackoverflow.com/a/11409569/4497791) formulas do not trigger `worksheet_change` – eirikdaude Jun 15 '15 at 11:22
  • [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) will get you started. – Siddharth Rout Jun 15 '15 at 11:49

1 Answers1

1

Place this in "ThisWorkbook"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim result As Boolean
Dim sheetName As String
Dim rangeName As String 
'You could alternatively create array variables below if interested in multiple sheets/cells
Dim mySheet As String
Dim myRange As String

'User Defined Input. What sheet and range are you interested in tracking changes for?
mySheet = "Worksheet Name"
myRange = "$C$3"

'Store what changed
sheetName = Target.Parent.Name
rangeName = Target.Address

If sheetName = mySheet And rangeName = myRange Then
    'A change occurred for a range you are interested in
    result = True
End If

'Did a change occur in your range of interest?
If result = True Then
    'This is where you could prompt to save changes.
    MsgBox "Change detected in " & sheetName & "!" & rangeName & "."
End If

End Sub
Corbin
  • 318
  • 1
  • 8