VBA - Seek user confirmation when changing non blank cells

[TORPIDO]

hi yall

i have below code which mostly works except it also asks confirmation for empty cells, i want it to ignore the rule for blank cells where value are being set for first time, is this doable?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldValue As Variant
    Dim newValue As Variant
    Dim userResponse As Integer

    If Target.Count > 1 Then Exit Sub

    Application.EnableEvents = False

    If Not IsEmpty(Target) Then
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value

        If oldValue <> newValue Then
            userResponse = MsgBox("Do you want to change the value?", vbYesNo + vbQuestion, "Confirm Change")

            If userResponse = vbNo Then
                Target.Value = oldValue
            Else
                Target.Value = newValue
            End If
        End If
    End If

    Application.EnableEvents = True
End Sub

 

[kevin9999]

Try this:
If oldValue <> newValue AND oldValue <> "" Then

 

[nemmi69]

try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldValue As Variant
    Dim newValue As Variant
    Dim userResponse As Integer

    If Target.Count > 1 Then Exit Sub

    Application.EnableEvents = False

    If Not IsEmpty(Target) Then
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value

        If oldValue <> newValue And IsEmpty(Target.Value) = False Then
            userResponse = MsgBox("Do you want to change the value?", vbYesNo + vbQuestion, "Confirm Change")

            If userResponse = vbNo Then
                Target.Value = oldValue
            Else
                Target.Value = newValue
            End If
        End If
    End If

    Application.EnableEvents = True
End Sub

 

[TORPIDO]

try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldValue As Variant
    Dim newValue As Variant
    Dim userResponse As Integer

    If Target.Count > 1 Then Exit Sub

    Application.EnableEvents = False

    If Not IsEmpty(Target) Then
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value

        If oldValue <> newValue And IsEmpty(Target.Value) = False Then
            userResponse = MsgBox("Do you want to change the value?", vbYesNo + vbQuestion, "Confirm Change")

            If userResponse = vbNo Then
                Target.Value = oldValue
            Else
                Target.Value = newValue
            End If
        End If
    End If

    Application.EnableEvents = True
End Sub

Now its not applying any changes on the blank cell, no confirmation is asks, just rolls back the value to blank

 

[nemmi69]

OK, Try this

Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newValue As Variant
    Dim userResponse As Integer

    If Target.Count > 1 Then Exit Sub

    Application.EnableEvents = False
    If Not IsEmpty(OldValue) Then
        newValue = Target.Value

        If OldValue <> newValue Then
            userResponse = MsgBox("Do you want to change the value?", vbYesNo + vbQuestion, "Confirm Change")

            If userResponse = vbNo Then
                Target.Value = OldValue
            Else
                Target.Value = newValue
            End If
        End If
    End If

    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   OldValue = Target.Value
End Sub

 

[TORPIDO]

OK, Try this

Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newValue As Variant
    Dim userResponse As Integer

    If Target.Count > 1 Then Exit Sub

    Application.EnableEvents = False
    If Not IsEmpty(OldValue) Then
        newValue = Target.Value

        If OldValue <> newValue Then
            userResponse = MsgBox("Do you want to change the value?", vbYesNo + vbQuestion, "Confirm Change")

            If userResponse = vbNo Then
                Target.Value = OldValue
            Else
                Target.Value = newValue
            End If
        End If
    End If

    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   OldValue = Target.Value
End Sub

fantastic, this i why i say AI cannot remove humans... i tried 3 AI (Bard, GPT, Bing) non could figure it out
thanks a lot

 

[kevin9999]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldValue As Variant
    Dim newValue As Variant
    Dim userResponse As Integer
    
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not IsEmpty(Target) Then
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value
        If oldValue <> newValue And oldValue <> "" Then
            userResponse = MsgBox("Do you want to change the value?", vbYesNo + vbQuestion, "Confirm Change")
            If userResponse = vbNo Then
                Target.Value = oldValue
            Else
                Target.Value = newValue
            End If
            Else
            Target.Value = newValue
        End If
    End If
    Application.EnableEvents = True
End Sub