Check if there is a change in cell

[GEMINI528]

Good day Excel Gurus,

I have the working VBA code below but I need some revision. The code below checks every cell if there is a change. Example, If I made change on cell “D9”, it will ask “Do you want to save changes…?” and also same with “D10”, “D11”, and so on.

What revision I want is when you reach “D42”, or when I exit on the loop that the only time it will check if there are changes made on the sheet.

Any help would be appreciated.

Thank you.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim rngA As Range
    Dim rngDE As Range

    Dim lRec As Long
    Dim lRecRow As Long
    Dim lLastRec As Long
    Dim lastRow As Long
    Dim lCellsDE As Long
    Dim lColHist As Long
    Dim LRsp As Long

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

      If Not Intersect(Target, Range("D9:D42")) Is Nothing Then
        Application.EnableEvents = False
         Beep
         LRsp = MsgBox("Do you want to Save Changes made? ", vbQuestion + vbYesNo, "CHANGES")
        If LRsp = vbYes Then
          UpdateLogRecord
        Else
          ClearDataEntry
        End If 
    End If 

  On Error GoTo 0

    Set rngA = ActiveCell
    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("dBase")
    Set rngDE = inputWks.Range("OrderEntry")
    lCellsDE = rngDE.Cells.Count
    lColHist = 3

Application.EnableEvents = False
    Select Case Target.Address
      Case Me.Range("OrderSel").Address
        Me.Range("CurrRec").Value = Me.Range("SelRec").Value
      Case Me.Range("Code").Address
        If Range("CheckID") = True Then
          Me.Range("OrderSel").Value = Me.Range("Code").Value
          Me.Range("CurrRec").Value = Me.Range("SelRec").Value
        Else
          Me.Range("OrderSel").ClearContents
          Me.Range("CurrRec").Value = 0
          Me.Range("ClearVar").ClearContents
        Application.EnableEvents = True
        End If
      Case Else
        GoTo exitHandler
    End Select
    
    With historyWks
      lastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
      lLastRec = lastRow - 1
    End With

    With historyWks
          lRec = inputWks.Range("CurrRec").Value
      If lRec > 0 And lRec <= lLastRec Then
          lRecRow = lRec + 1
          .Range(.Cells(lRecRow, lColHist), .Cells(lRecRow, lCellsDE)).Copy
          rngDE.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
          rngA.Select
      End If
    End With
Application.EnableEvents = True
exitHandler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit Sub
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

[Logit]

.
You want to be notified if a change occurs ANYWHERE on the sheet ?

[GEMINI528]

.
You want to be notified if a change occurs ANYWHERE on the sheet ?

Yes sir. If there are more than 1 changes, I would like the notification once only NOT every time I change the value of each cell.

Thank you for your prompt response.

[Logit]

.
Hmmm .... help me understand.

At first I was thinking you wanted to create a log on another sheet that would list all the changes someone might make to your workbook.

I am now thinking you want to be asked if the changes should be SAVED ?

If that is correct, rather than using the Worksheet_Change(ByVal Target As Range) event ... perhaps asking when the workbook closes ?


Maybe a more complete description / explanation of your work process .... goal ... is in order ?

[GEMINI528]

Sorry for the confusion...

Well, theoretically the workbook is being save. There are 2 Macro, when there are changes, you have the option to save it, “Sub UpdateLogRecord()”, If you don’t want to save it, It will just clear the sheet “Sub ClearDataEntry()”, If it is cleared, it will not be stored when you save the workbook.

Thank you.

[Logit]

.
Gotcha !

There are several things you will need to do to accomplish your goal.

In the ThisWorkbook module, you can place this macro :

Private Sub Workbook_BeforeClose(Cancel As Boolean)

	'The name of your macro
	SaveOrNotMacro

    	Application.DisplayAlerts = False
  
    	Application.Quit

End Sub

The SaveOrNotMacro (or any other name you like) is the macro you will create in a Regular Module. This macro will give the user the opportunity to
save or not save the changes made .... when they close the workbook.

The macro will have similar logic from your posted macro :

Sub SaveOrNot ()
Dim LRsp As Long

Beep
        LRsp = MsgBox("Do you want to Save Changes made? ", vbQuestion + vbYesNo, "CHANGES")

        If LRsp = vbYes Then
          	UpdateLogRecord
        Else
		'leave this area blank as it will cause the logic to go straight to the
		'Workbook_BeforeClose event ... closing the workbook without saving.
        End If
End Sub

I haven't tested the above macro here. There may be some required changes.