Warning Message Box when data is changed

[jiggaman84]

Hi

I want to alert users with a message box containing the following question: "Prior Valuation - Are you sure you want to edit this worksheet?" Ans(Y/N), should the user attempt to edit any cells within a specific range ("C2:AS100") on the worksheet, say Sheet3.

Ideally, it would only pop up the first time the user attempted to make a change to a value within the range. Therefore, if the user say 'Yes', it will not pop up again until the user returns to that worksheet again from another worksheet in the file and attempts to edit a cell value within the C2:AS100 range once more.

I have 300 files that I need this to be applied to, so I plan on implanting the macro from a central source workbook into each workbook using another macro, if that makes any sense.

Thanks in advance!

 

[craig.penny]

Paste this into the sheet's code module.

Dim NewActivate As Boolean

Private Sub Worksheet_Activate()
    NewActivate = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If NewActivate = False Then Exit Sub

Dim Rng As Range
Set Rng = Range("C2:AS100")
Dim Ans As Boolean

If Not Intersect(Target, Rng) Is Nothing Then
    Ans = MsgBox("Prior Valuation - Are you sure you want to edit?", vbYesNo)
End If

'do stuff here based on the value of Ans
NewActivate = False

End Sub

You will most likely need to save, close, and reopen your workbook for this to start working.

Let me know if it works for you.

 

[jiggaman84]

Thanks - this works well!

I now want to apply the code provided to a specific worksheet across 300 identical workbooks. The worksheet name is identical in each of the workbooks ( 'Jun. 30, 2012').

Is there a way to apply the macro and save each workbook? Ideally, I would like to embed the function in each file.

Thanks in advance!

 

[craig.penny]

I'm not sure I understand. Are you asking if there's a way to copy-paste this code into 300 workbooks? If yes, then are all the Excel Workbooks currently in the same location?