Return a value to the previous value??

[ShaunTur]

I'v got a private sub for a worksheet change that looks up to see if values are blank which decides if the user can do a change or not.
Heres my code so far:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column <> 2 Then Exit Sub
    
    If WorksheetFunction.CountA(Sheets("Order Form").Range("C7", "I300")) = 0 Then
    Exit Sub
    Else
    MsgBox "Cant Change Products Through The Week"
    
    End If
End Sub

I want to insert a code after the msgbox that returns the value back to the value before the change?
or do i have to set up a worksheet_selectionchange macro too?
Really appreciate the help
Thanks
Shaun

 

[Ruddles]

Try this:-

    With Application
      .EnableEvents = False
      .Undo
      .EnableEvents = True
    End With

 

[ShaunTur]

Or is there a way to set a variable for any selected cell

Worksheet_Selection ()
myvalue = activecell.value
end sub

then call that variable from the other sub

 

[ShaunTur]

YOU ARE A LEGEND. love u :D
thanks

 

[Ruddles]

a) I hope you are female

b) my invoice is in the mail

 

[Ruddles]

On a serious note, if you're going to do this sort of thing, please test what happens under every possible circumstance. Check that the code behaves predictably when the user:-

• selects a range of cells starting and/or ending in column B, wiping from left-to-right and/or right-to-left, and presses the Delete key to clear the entire range
• selects one or more entire rows upwards and/or downwards or one or more entire columns left-to-right and/or right-to-left, and presses the Delete key to clear them
• copies a value from elsewhere in the worksheet and pastes it into column B, or into multiple columns and/or rows

Finally a word about Application.EnableEvents. If you set it to False and your program stops or abends for any reason before it's reset to True, VBA will be left in a state where events are not enabled and macros will not run.

If you suspect this might be the case after an abend, open the Immediate window (Ctrl-G) and type ?application.enableevents followed by Enter. If it reports False, type application.enableevents=true followed by Enter to re-enable them.