Call Multiple Private Subs with single routine (1 Viewer)

dvarvel

Registered User.
Local time
Today, 05:02
Joined
Nov 28, 2017
Messages
22
I have appx 20 private subs and they are each associated to a form button. Is there a way to create a macro/module/code that will run all of the Subs sequentially, that I can assign to a single form button? I would also like to have a progress bar, so the user doesn't think the system is locked up.

Thanks!
 

Minty

AWF VIP
Local time
Today, 12:02
Joined
Jul 26, 2013
Messages
10,355
Move all the code into one sub ?
Put a counter in and increment it by 1 each time one of the tasks is complete, change a label caption to indicate x out y processes complete.
 

plog

Banishment Pending
Local time
Today, 07:02
Joined
May 11, 2011
Messages
11,613
Sub MainClick()
' runs 20 sub routines when button is clicked

' code to let user know code is running here

sub1_Name()
sub2_Name()
...
sub20_Name()


' code to let user know code has completed here

End Sub
 

dvarvel

Registered User.
Local time
Today, 05:02
Joined
Nov 28, 2017
Messages
22
Each of my subs run a loop, and I tried combining them, but it gave me a compile error. Each sub is looping through a different query and creating a pdf report by state and exporting them to a specific network drive. Also, I don't know how to write the progress bar code.

My knowledge in VBA is only what I have taught myself by trial and error and what I have learned here.
 

dvarvel

Registered User.
Local time
Today, 05:02
Joined
Nov 28, 2017
Messages
22
Thanks plog! I will try that! I'll keep you posted.
 

dvarvel

Registered User.
Local time
Today, 05:02
Joined
Nov 28, 2017
Messages
22
plog - When I create the button and I start typing in the sub name, it gives me a compile error.
Private Sub MainExport_Click()
Test1_Click()
End Sub
 

plog

Banishment Pending
Local time
Today, 07:02
Joined
May 11, 2011
Messages
11,613
Whats the error specifically? Is Test1_Click in the same module? Is it a valid name?
 

dvarvel

Registered User.
Local time
Today, 05:02
Joined
Nov 28, 2017
Messages
22
Sorry, I'm not very good with vba, at all. I did not set up any modules. I am using the Subs created by the click buttons.
 

dvarvel

Registered User.
Local time
Today, 05:02
Joined
Nov 28, 2017
Messages
22
I got it to work. :) What type of coding would I need to add to let the user know that the job is running and when the job is completed?

Update:: I got it to work. :D
 
Last edited:

plog

Banishment Pending
Local time
Today, 07:02
Joined
May 11, 2011
Messages
11,613
I'd use Minty's method. Put a text box on the screen that you then change the message of before each sub runs.
 

isladogs

MVP / VIP
Local time
Today, 12:02
Joined
Jan 14, 2017
Messages
18,186
But if you do want a progress bar as well there are several on this forum.
Just type progress bar into the Search item
Or try this one of mine

 

Attachments

  • ExampleProgBar.accdb
    480 KB · Views: 115
  • ProgBar.PNG
    ProgBar.PNG
    20.3 KB · Views: 470
Last edited:

Mark_

Longboard on the internet
Local time
Today, 05:02
Joined
Sep 12, 2017
Messages
2,111
The trick I use in these cases is pretty straight forward. To elaborate on what Minty posted,

1) Put a label on your form called "Lbl_Progress"
2) In your button code where you will be calling each sub, do the following before each sub is called

Code:
Me.Lbl_Progress.Caption = "Now processing <Something>"

You would replace the <Something> for each time you do this with something YOU will remember as the programmer, say "Sales for Alaska".

After you are done with all of the subs simply have a last line that reads

Code:
Me.Lbl_Progress.Caption = ""

So your users will not be bothered by the last action done.
 

Users who are viewing this thread

Top Bottom