SOLVED
Home

Suppress this first initialization of the private macro

%3CLINGO-SUB%20id%3D%22lingo-sub-477995%22%20slang%3D%22en-US%22%3ESuppress%20this%20first%20initialization%20of%20the%20private%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477995%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20%22private%20worksheet%22%20below%20that%20monitors%20U30%20THRU%20U629.%20My%20macro%20starts%20off%20by%20clearing%20all%20contents%20of%20U30%20thru%20u629%20prompting%20this%20private%20macro%20to%20initiate.%20I%20would%20like%20to%20suppress%20this%20first%20initialization%20of%20the%20private%20macro%20(since%20this%20is%20just%20a%20reset)%20and%20allow%20only%20subsequent%20activation%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3C%2FP%3E%3CP%3EActiveSheet.Range(%22%24AW%2421%3A%24AX%24629%22).AutoFilter%20Field%3A%3D1%3C%2FP%3E%3CP%3EIf%20Not%20(Application.Intersect(Range(%22%20U30%3AU629%22)%2C%20Target)%20Is%20Nothing)%20Then%3C%2FP%3E%3CP%3EIf%20MsgBox(%22Select%20NO%20until%20ADDITIONAL%20SHARES%20are%20manually%20entered.%20Have%20you%20finished%20manually%20entering%20ADDITIONAL%20SHARES%3F%22%2C%20vbQuestion%20%2B%20vbYesNo%2C%20%22%22)%20%3D%20vbYes%20Then%3CBR%20%2F%3ECall%20ManualCalculate%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-477995%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478307%22%20slang%3D%22en-US%22%3ERe%3A%20Suppress%20this%20first%20initialization%20of%20the%20private%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478307%22%20slang%3D%22en-US%22%3EI%20assume%20you%E2%80%99re%20clearing%20the%20range%20in%20another%20macro%3F%20Before%20you%20clear%2C%20you%E2%80%99ll%20want%20to%20turn%20off%20events%20using%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20after%20you%20clear%20the%20range%2C%20reset%20Application.EnableEvents%20%3D%20True%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478719%22%20slang%3D%22en-US%22%3ERe%3A%20Suppress%20this%20first%20initialization%20of%20the%20private%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478719%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324602%22%20target%3D%22_blank%22%3E%40MichaelMays%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMichael%20thank%20you!%20With%20your%20help%20my%20final%20code%20to%20fix%20this%20issue%20looks%20like%20this%20and%20is%20embedded%20in%20the%20macro%20that%20does%20the%20clearing%20of%20the%20contents.%20This%20worked%20perfectly%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3ERange(%22Z10%2CZ8%2CR2%2CT2%3AT27%2CU2%3AU11%2CV2%3AW5%2CU30%3AU629%22).Select%3CBR%20%2F%3ERange(%22U30%22).Activate%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.ClearContents%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478739%22%20slang%3D%22en-US%22%3ERe%3A%20Suppress%20this%20first%20initialization%20of%20the%20private%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478739%22%20slang%3D%22en-US%22%3EGlad%20it%20worked!%20Something%20you%20can%20also%20do%20to%20make%20code%20run%20more%20quickly%20is%20to%20avoid%20selecting%20and%20activating%20ranges.%20Instead%2C%20use%3A%20Range(%22Z10%2CZ8%2CR2%2CT2%3AT27%2CU2%3AU11%2CV2%3AW5%2CU30%3AU629%22).ClearContents%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-478788%22%20slang%3D%22en-US%22%3ERe%3A%20Suppress%20this%20first%20initialization%20of%20the%20private%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-478788%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324602%22%20target%3D%22_blank%22%3E%40MichaelMays%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWow%20I%20select%20and%20activate%20in%20multiple%20macros%20causing%20my%20entire%20process%20to%20take%20about%206mins.%20I%20always%20wondered%20how%20to%20make%20things%20run%20faster.%20I%20will%20begin%20implementing%20your%20suggestion.%20Ill%20get%20back%20to%20you%20soon%20with%20the%20result%20as%20I%20have%20much%20code%20to%20modify.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-479853%22%20slang%3D%22en-US%22%3ERe%3A%20Suppress%20this%20first%20initialization%20of%20the%20private%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-479853%22%20slang%3D%22en-US%22%3EGlad%20to%20hear%20it.%20One%20other%20idea%20you%20might%20already%20be%20doing%20is%20setting%20Application.ScreenUpdating%20%3D%20False%20at%20the%20beginning.%20Then%20at%20the%20end%2C%20set%20it%20back%20to%20true.%20This%20will%20definitely%20help%20it%20run%20faster%3C%2FLINGO-BODY%3E
Greg Bonaparte
Contributor

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

5 Replies
Solution
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
Highlighted

@MichaelMays 

 

Michael 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

@MichaelMays 

 

Wow I select and activate in multiple macros causing my entire process to take about 6mins. I always wondered how to make things run faster. I will begin implementing your suggestion. Ill get back to you soon with the result as I have much code to modify. Thanks

Glad to hear it. One other idea you might already be doing is setting Application.ScreenUpdating = False at the beginning. Then at the end, set it back to true. This will definitely help it run faster
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies