VBA code when once cell is changed and others are not

[nikifi]

Hello,
I am very new to VBA programming (just looking at its possibilities). I am trying to complete a task that might be a little difficult. I am trying to write code that displays a message box if a certain cell has changed, but others haven't when the user attempts to close the file.

So for instance, it is important to update certain cells, if one specific cell is updated (in each row). I want to force the users to update the cells as it is important information neeeded for tracking, but it is only necessary to be updated when a certain cell has changed. I want to show the message only when the user tries to close the spreadsheet without updating the required cells. This way it is not invasive.To determine when it has changed, is to determine if the user made a change to the cell in the current session (since the user opened the file). I could make a box popup reminding the user that he/she needs to update the cells, but that can be annoying and loses its effectiveness after so long. Any help is appreciated. Thanks

 

[Bill Rockenbach]

Place these variables in any standard module

Option Explicit
Public Key As String
Public variableA As String
Public variableB As String
Public variableC As String

Place this code in the "Thisworkbook" code module

Option Explicit
Dim Terminate As Boolean

Private Sub Workbook_Open()
    Key = Sheet1.Range("A2")
    variableA = Sheet1.Range("B2")
    variableB = Sheet1.Range("C2")
    variableC = Sheet1.Range("D2")
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    TestForChagedKey
    If Terminate = True Then Cancel = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
TestForChagedKey
If Terminate = True Then Cancel = True
End Sub
Sub TestForChagedKey()
    Terminate = False
 
        If Sheet1.Range("A2") = Key Then
        Exit Sub
    Else
        If Sheet1.Range("B2")<> variableA And _
           Sheet1.Range("C2")<> variableB And _
           Sheet1.Range("D2")<> variableC Then Exit Sub
    End If
    
    MsgBox Sheet1.Range("A2") & "   " & Key & Chr(13) & _
           Sheet1.Range("B2") & "   " & variableA & Chr(13) & _
           Sheet1.Range("C2") & "   " & variableB & Chr(13) & _
           Sheet1.Range("D2") & "   " & variableC & Chr(13) & Chr(13) & _
           "Data needs changing.  Action Terminated"
    Terminate = True
 
End Sub

Excel Workbook
	A	B	C	D
1	Key Data	VariableA	VariableB	VariableC
2	Key2	A	B	C
Sheet1

Excel 2000

close and reopen the workbook to set teh original variables in teh standard module.

Adoped to your needs

 

[nikifi]

Thanks a lot Bill. This will definitely help me.