Very new to VBA in Excel, got asked to do some validation on cell change and got a bit stuck.
So, user needs to enter a monetary value into a cell, let's say D16, so I thought I'd hook into the _Change event on the Worksheet which works quite well.
However, I need the rest of the worksheet to not complete the calculation when an entry has been submitted into D16, basically, when 500000 is entered, other cells gets updated with values from another worksheet.
My code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D16") Then
Dim numeric
numeric = IsNumeric(Target)
If numeric = False Then
MsgBox "error"
Exit Sub
/// this is where I need to "stop" the calculations from firing
End If
End If
End Sub
To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by pressing F9), in the Calculation options section, under Workbook Calculation, click Manual. Note: When you click Manual, Excel automatically selects the Recalculate workbook before saving check box.
Click in the grey margin to the left of a line of code to set (or remove) a breakpoint for it. Alternatively, press F9 to toggle a breakpoint on or off. A surprisingly useful short-cut key is SHIFT + CTRL + F9, which removes all of the breakpoints that you've set.
I hope below code helps. You need to paste this in sheet code section.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim rng As Range
Set rng = Range("D16")
If Not Intersect(Target, rng) Is Nothing And IsNumeric(Target) Then
If Target.Value >= 500000 Then
MsgBox "Value is greater than 500000"
End If
End If
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With