this is probably really simple and i'm not thinking...

I am currently using a modified version of a progress bar kinbdly made
available on the J-Walk website [link: http://j-walk.com/ss/Excel/tips/tip34.htm]

The steps involved are as follows.

1. Assign a button to run a macro which shows a UserForm, e.g.

Sub MacroWithProgressBar()
UserForm1.Show
End Sub

2. Create a routine which runs on activation of the UserForm, and
calls the required macro e.g.

Private Sub UserForm1_activate()
Call MyMacro
End Sub

3. Then a couple of lines are added into any loops to display the
progress (works by altering the length of a label which has
baskground colour).


If you're still following, now for my question:

I have several macros in my workbook for which I would like to display
a progress bar. With the current setup, I would need to create a
different UserForm for each macro because it always calls the same Sub
on activation.

Is it possible to pass a variable to Activate?

In the process of writing this I have thought of a possible
solution :)

I could define a new global variable, e.g:

Public ThisMacroWantsAProgressBar as String

then I would only need to create one initial sub for each macro, e.g.

Sub MacroWithProgressBar1()
ThisMacroWantsAProgressBar = MyMacro1
UserForm1.Show
End Sub


Sub MacroWithProgressBar2()
ThisMacroWantsAProgressBar = MyMacro2
UserForm1.Show
End Sub


etc. and so now the activate sub looks like:

Private Sub UserForm1_activate()
Call ThisMacroWantsAProgressBar
End Sub


THanks

Update:

My idea didn't work. The Call command doesn't accept procedures only,
not variables.

So any workarounds someone might have would be appreciated.



On Jan 14, 10:51 am, brzak wrote:
this is probably really simple and i'm not thinking...

I am currently using a modified version of a progress bar kinbdly made
available on the J-Walk website [link:http://j-walk.com/ss/Excel/tips/tip34.htm]

The steps involved are as follows.

1. Assign a button to run a macro which shows a UserForm, e.g.

Sub MacroWithProgressBar()
UserForm1.Show
End Sub

2. Create a routine which runs on activation of the UserForm, and
calls the required macro e.g.

Private Sub UserForm1_activate()
Call MyMacro
End Sub

3. Then a couple of lines are added into any loops to display the
progress (works by altering the length of a label which has
baskground colour).

If you're still following, now for my question:

I have several macros in my workbook for which I would like to display
a progress bar. With the current setup, I would need to create a
different UserForm for each macro because it always calls the same Sub
on activation.

Is it possible to pass a variable to Activate?

In the process of writing this I have thought of a possible
solution :)

I could define a new global variable, e.g:

Public ThisMacroWantsAProgressBar as String

then I would only need to create one initial sub for each macro, e.g.

Sub MacroWithProgressBar1()
ThisMacroWantsAProgressBar = MyMacro1
UserForm1.Show
End Sub

Sub MacroWithProgressBar2()
ThisMacroWantsAProgressBar = MyMacro2
UserForm1.Show
End Sub

etc. and so now the activate sub looks like:

Private Sub UserForm1_activate()
Call ThisMacroWantsAProgressBar
End Sub

THanks

On 14 Jan, 10:58, brzak wrote:
Update:

My idea didn't work. TheCallcommand doesn't accept procedures only,
not variables.

So any workarounds someone might have would be appreciated.

On Jan 14, 10:51 am, brzak wrote:



this is probably really simple and i'm not thinking...

I am currentlyusinga modified version of a progress bar kinbdly made
available on the J-Walk website [link:http://j-walk.com/ss/Excel/tips/tip34.htm]

The steps involved are as follows.

1. Assign a button to run a macro which shows a UserForm, e.g.

Sub MacroWithProgressBar()
* * UserForm1.Show
End Sub

2. Create a routine which runs on activation of the UserForm, and
calls the required macro e.g.

Private Sub UserForm1_activate()
* *CallMyMacro
End Sub

3. Then a couple of lines are added into any loops to display the
progress (works by altering the length of a label which has
baskground colour).

If you're still following, now for my question:

I have several macros in my workbook for which I would like to display
a progress bar. With the current setup, I would need to create a
different UserForm for each macro because it always calls the same Sub
on activation.

Is it possible to pass avariableto Activate?

In the process of writing this I have thought of a possible
solution :)

I could define a new globalvariable, e.g:

Public ThisMacroWantsAProgressBar as String

then I would only need to create one initial sub for each macro, e.g.

Sub MacroWithProgressBar1()
* * ThisMacroWantsAProgressBar = MyMacro1
* * UserForm1.Show
End Sub

Sub MacroWithProgressBar2()
* * ThisMacroWantsAProgressBar = MyMacro2
* * UserForm1.Show
End Sub

etc. and so now the activate sub looks like:

Private Sub UserForm1_activate()
* *CallThisMacroWantsAProgressBar
End Sub

THanks- Hide quoted text -

- Show quoted text -

I have just hit the same problem, trying to use a call statement with
a variable. The way I have got round it is to use the Run command
instead something like:

Private Sub UserForm1_activate()
Application.Run "'" & Thisworkbook.Name & "'!" &
ThisMacroWantsAProgressBar
End Sub

James

thanks for that, that's perfect for what i need. i hadn't previoulsy
come across the run command, but yes, you learn something new
everyday.

You're suggested method also provides a way of calling macros from
different workbooks:

Sub CallProcedureInAnotherWorkbook()
Application.Run "'" & ActiveWorkbook.Name & "'!" &
"AndThisMacroWantsAProgressBar"
End Sub

where ActiveWorkbook.Name can be replaced by other means,i.e.

Sub CallProcedureIn (WkBook as String, Procedure as String)
Application.Run "'" & WkBook & "'!" & "ExportResults"
End Sub

or simply, staying with your suggestion and keeping it as a global
variable.

Anyway, thanks again for the post.



On Jan 16, 5:21 pm, wrote:
On 14 Jan, 10:58, brzak wrote:



Update:

My idea didn't work. TheCallcommand doesn't accept procedures only,
not variables.

So any workarounds someone might have would be appreciated.

On Jan 14, 10:51 am, brzak wrote:

this is probably really simple and i'm not thinking...

I am currentlyusinga modified version of a progress bar kinbdly made
available on the J-Walk website [link:http://j-walk.com/ss/Excel/tips/tip34.htm]

The steps involved are as follows.

1. Assign a button to run a macro which shows a UserForm, e.g.

Sub MacroWithProgressBar()
UserForm1.Show
End Sub

2. Create a routine which runs on activation of the UserForm, and
calls the required macro e.g.

Private Sub UserForm1_activate()
CallMyMacro
End Sub

3. Then a couple of lines are added into any loops to display the
progress (works by altering the length of a label which has
baskground colour).

If you're still following, now for my question:

I have several macros in my workbook for which I would like to display
a progress bar. With the current setup, I would need to create a
different UserForm for each macro because it always calls the same Sub
on activation.

Is it possible to pass avariableto Activate?

In the process of writing this I have thought of a possible
solution :)

I could define a new globalvariable, e.g:

Public ThisMacroWantsAProgressBar as String

then I would only need to create one initial sub for each macro, e.g.

Sub MacroWithProgressBar1()
ThisMacroWantsAProgressBar = MyMacro1
UserForm1.Show
End Sub

Sub MacroWithProgressBar2()
ThisMacroWantsAProgressBar = MyMacro2
UserForm1.Show
End Sub

etc. and so now the activate sub looks like:

Private Sub UserForm1_activate()
CallThisMacroWantsAProgressBar
End Sub

THanks- Hide quoted text -

- Show quoted text -

I have just hit the same problem, trying to use a call statement with
a variable. The way I have got round it is to use the Run command
instead something like:

Private Sub UserForm1_activate()
Application.Run "'" & Thisworkbook.Name & "'!" &
ThisMacroWantsAProgressBar
End Sub

James

thank you for your post, this is solves my previous problem and a new
one I had.

Your method also provides a means for calling procedures from other
workbooks, i.e.

Sub CallProcedureIn(WkBook as String, Procedure as STring)
Application.Run "'" & WkBook & "'!" & Procedure
End Sub

as you point out, once it's a string then you don't have the problem
of passing variables.

Thanks again.


On Jan 16, 5:21 pm, wrote:
On 14 Jan, 10:58, brzak wrote:



Update:

My idea didn't work. TheCallcommand doesn't accept procedures only,
not variables.

So any workarounds someone might have would be appreciated.

On Jan 14, 10:51 am, brzak wrote:

this is probably really simple and i'm not thinking...

I am currentlyusinga modified version of a progress bar kinbdly made
available on the J-Walk website [link:http://j-walk.com/ss/Excel/tips/tip34.htm]

The steps involved are as follows.

1. Assign a button to run a macro which shows a UserForm, e.g.

Sub MacroWithProgressBar()
UserForm1.Show
End Sub

2. Create a routine which runs on activation of the UserForm, and
calls the required macro e.g.

Private Sub UserForm1_activate()
CallMyMacro
End Sub

3. Then a couple of lines are added into any loops to display the
progress (works by altering the length of a label which has
baskground colour).

If you're still following, now for my question:

I have several macros in my workbook for which I would like to display
a progress bar. With the current setup, I would need to create a
different UserForm for each macro because it always calls the same Sub
on activation.

Is it possible to pass avariableto Activate?

In the process of writing this I have thought of a possible
solution :)

I could define a new globalvariable, e.g:

Public ThisMacroWantsAProgressBar as String

then I would only need to create one initial sub for each macro, e.g.

Sub MacroWithProgressBar1()
ThisMacroWantsAProgressBar = MyMacro1
UserForm1.Show
End Sub

Sub MacroWithProgressBar2()
ThisMacroWantsAProgressBar = MyMacro2
UserForm1.Show
End Sub

etc. and so now the activate sub looks like:

Private Sub UserForm1_activate()
CallThisMacroWantsAProgressBar
End Sub

THanks- Hide quoted text -

- Show quoted text -

I have just hit the same problem, trying to use a call statement with
a variable. The way I have got round it is to use the Run command
instead something like:

Private Sub UserForm1_activate()
Application.Run "'" & Thisworkbook.Name & "'!" &
ThisMacroWantsAProgressBar
End Sub

James