Forum Discussion
Suppress this first initialization of the private macro
I have a "private worksheet" below that monitors U30 THRU U629. My macro starts off by clearing all contents of U30 thru u629 prompting this private macro to initiate. I would like to suppress this first initialization of the private macro (since this is just a reset) and allow only subsequent activation:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Range("$AW$21:$AX$629").AutoFilter Field:=1
If Not (Application.Intersect(Range(" U30:U629"), Target) Is Nothing) Then
If MsgBox("Select NO until ADDITIONAL SHARES are manually entered. Have you finished manually entering ADDITIONAL SHARES?", vbQuestion + vbYesNo, "") = vbYes Then
Call ManualCalculate
End If
End If
End Sub
- I assume you’re clearing the range in another macro? Before you clear, you’ll want to turn off events using
Application.EnableEvents = False
Then after you clear the range, reset Application.EnableEvents = True
5 Replies
- I assume you’re clearing the range in another macro? Before you clear, you’ll want to turn off events using
Application.EnableEvents = False
Then after you clear the range, reset Application.EnableEvents = TrueMichael thank you! With your help my final code to fix this issue looks like this and is embedded in the macro that does the clearing of the contents. This worked perfectly:
Application.EnableEvents = False
Range("Z10,Z8,R2,T2:T27,U2:U11,V2:W5,U30:U629").Select
Range("U30").Activate
Application.CutCopyMode = False
Selection.ClearContents
Application.EnableEvents = True- Glad it worked! Something you can also do to make code run more quickly is to avoid selecting and activating ranges. Instead, use: Range("Z10,Z8,R2,T2:T27,U2:U11,V2:W5,U30:U629").ClearContents