SOLVED

Progress bar while running macro

%3CLINGO-SUB%20id%3D%22lingo-sub-1419481%22%20slang%3D%22en-US%22%3EProgress%20bar%20while%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419481%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20attached%20my%20macro.%20i%20need%20to%20hide%20things%20happening%20while%20running%20macro%20and%20need%20to%20display%20progress%20bar%20till%20complete%20the%20macro.%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20anyone%20help%20me%20to%20do%20this%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1419481%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419536%22%20slang%3D%22en-US%22%3ERe%3A%20Progress%20bar%20while%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20toggle%20screen%20updating%20off%20and%20on%20during%20execution%2C%20insert%20%3CSTRONG%3EApplication.Screenupdating%20%3D%20False%3C%2FSTRONG%3E%20of%26nbsp%3B%3CSTRONG%3EApplication.Screenupdating%20%3D%20True%3C%2FSTRONG%3E%20in%20your%20code%20where%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20display%20progress%2C%20and%20provided%20you%20have%20some%20kind%20of%20counter%20(e.g.%20Step%20....%20of%205000)%2C%20you%20could%20use%20something%20like%20%3CSTRONG%3EApplication.Statusbar%20%3D%20%22Step%20%22%20%26amp%3B%20counter%20%26amp%3B%20%22%20of%205000%20completed%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419599%22%20slang%3D%22en-US%22%3ERe%3A%20Progress%20bar%20while%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Riny%2C%3C%2FP%3E%3CP%3Ethank%20you%20for%20your%20respond.%20i%20need%20to%20show%20some%20visual%20showing%20by%20user%20form%20function.%20here%20attached%20with%20my%20macro.%20can%20you%20please%20help%20to%20do%20this%20for%20my%20excel%20please%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1419721%22%20slang%3D%22en-US%22%3ERe%3A%20Progress%20bar%20while%20running%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1419721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3BHad%20a%20quick%20look%20at%20your%20set%20of%20macros.%20I%20suspect%20that%20many%20of%20them%20are%20straight%20recordings%20without%20any%20clean-up%20performed.%20I%20notice%20numerous%20unnecessary%20scroll%20commands%2C%20select%20something%2C%20do%20nothing%2C%20select%20something%20else%2C%20format%20something%20and%20then%20format%20the%20same%20selection%20again%20etc.%20It%20really%20needs%20cleaning-up.%26nbsp%3BCan't%20really%20test%20any%20of%20it%2C%20as%20you%20link%20to%20files%20on%20your%20own%20system.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20button%20on%20your%20first%20sheet%20starts%20up%20a%20sub%20called%20%22CommandButton1_Click()%22.%20I've%20inserted%20the%20code%20I%20suggested%20in%20my%20previous%20post.%20You'll%20need%20to%20copy%20the%20Statusbar%20piece%20before%20each%20of%20the%20following%20calls%20and%20increase%20the%20step%20number%20by%20one%20(I've%20done%20two%20for%20you).%20Note%20that%20the%20Statusbar%20gives%20visual%20feedback%20in%20the%20bottom%20left-hand%20side%20of%20the%20screen.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20CommandButton1_Click()%0A%0AApplication.Screenupdating%20%3D%20False%0A%0AGetFile1%0A%0AApplication.Statusbar%20%3D%20%22Step%201%20of%2022%20completed%22%0A%0AMacro1%0A%0AApplication.Statusbar%20%3D%20%22Step%202%20of%2022%20completed%22%0A%0AGetFile2%0AMacro2%0AMacro3%0ADeleteBlankRows%0AMacro4%0AMacro5%0AMacro6%0AMacro7%0AMacro8%0AMacro10%0AMacro11%0AGetFile2%0AMacronext1%0AMacronext2%0AMacronext3%0AMacro1next%0AMacro2next%0AMacro3next%0AMacro4next%0Asummery%0A%0AApplication.Screenupdating%20%3D%20True%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIf%20you%20insist%20on%20using%20a%20UserForm%2C%20use%20Google%20to%20find%20detailed%20instructions.%20It's%20not%20something%20I'm%20familiar%20with.%20Sorry!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi ,

 

here attached my macro. i need to hide things happening while running macro and need to display progress bar till complete the macro. 

can anyone help me to do this

3 Replies
Highlighted

@Ravindu94 

To toggle screen updating off and on during execution, insert Application.Screenupdating = False of Application.Screenupdating = True in your code where needed.

 

To display progress, and provided you have some kind of counter (e.g. Step .... of 5000), you could use something like Application.Statusbar = "Step " & counter & " of 5000 completed"

 

Highlighted

@Riny_van_Eekelen 

 

Hi Riny,

thank you for your respond. i need to show some visual showing by user form function. here attached with my macro. can you please help to do this for my excel please

Highlighted
Best Response confirmed by Ravindu94 (Contributor)
Solution

@Ravindu94 Had a quick look at your set of macros. I suspect that many of them are straight recordings without any clean-up performed. I notice numerous unnecessary scroll commands, select something, do nothing, select something else, format something and then format the same selection again etc. It really needs cleaning-up. Can't really test any of it, as you link to files on your own system.

 

The button on your first sheet starts up a sub called "CommandButton1_Click()". I've inserted the code I suggested in my previous post. You'll need to copy the Statusbar piece before each of the following calls and increase the step number by one (I've done two for you). Note that the Statusbar gives visual feedback in the bottom left-hand side of the screen.

Private Sub CommandButton1_Click()

Application.Screenupdating = False

GetFile1

Application.Statusbar = "Step 1 of 22 completed"

Macro1

Application.Statusbar = "Step 2 of 22 completed"

GetFile2
Macro2
Macro3
DeleteBlankRows
Macro4
Macro5
Macro6
Macro7
Macro8
Macro10
Macro11
GetFile2
Macronext1
Macronext2
Macronext3
Macro1next
Macro2next
Macro3next
Macro4next
summery

Application.Screenupdating = True

End Sub

If you insist on using a UserForm, use Google to find detailed instructions. It's not something I'm familiar with. Sorry!