screenupdating and macro status
 

I just finished a long project in excel of preparing report from huge data.
The entire project was broken into different macro because of its requirment.
Each macro actually hits a other application to bring in data.

Now i have about 20 macros which i run and final report get generated. I
have a master macro which calls all the macros.

like

SUb mainstart()

call macro1
call macro2
call macro3
call macro4

End sub

First problem:

If i insert screenupdating false in the start and screenupdating true in the
end of the master macro, it does not helps. Still we see macro doing some
activity in the middle.

Second problem:

I wish to get some idea about the macro which is running, can we diplay the
name of the macro which is running on the userform.
or some kind of progress button to help user understand that how much time
in left.

This forum had helped me most is getting things done. Please help..
Thanks!
Boss

screenupdating and macro status
 

Hello Boss

You could remove the screenupdating events from your separate macros and
load a userform and then call your other macros from the forms Activate event:

Create a form called 'UserForm1' that contains a Label called 'Label1' (set
the label to autosize)
Then change your MainStart() to

Sub MainStart()
With UserForm1
.label1.value = ""
.show
End With


then, within the Forms Activate event type:

Private Sub UserForm_activate()
Application.screenupdating = False

me.label1.value = "Running Macro 1"
DoEvents 'refreshes the label
Call Macro1

me.label1.value = "Running Macro 2"
DoEvents
Call Macro2

me.label1.value = "Running Macro 3"
DoEvents
Call Macro3
...etc

Application.screenupdating = True
unload me 'closes the form once complete
End Sub

I'm sure the guru's on the forum can 'refine' my code, but it should work OK.

HTH
Trevor Williams


"Boss" wrote:

I just finished a long project in excel of preparing report from huge data.
The entire project was broken into different macro because of its requirment.
Each macro actually hits a other application to bring in data.

Now i have about 20 macros which i run and final report get generated. I
have a master macro which calls all the macros.

like

SUb mainstart()

call macro1
call macro2
call macro3
call macro4

End sub

First problem:

If i insert screenupdating false in the start and screenupdating true in the
end of the master macro, it does not helps. Still we see macro doing some
activity in the middle.

Second problem:

I wish to get some idea about the macro which is running, can we diplay the
name of the macro which is running on the userform.
or some kind of progress button to help user understand that how much time
in left.

This forum had helped me most is getting things done. Please help..
Thanks!
Boss

screenupdating and macro status
 

The approach to the problem is perfect.
Thank you so much..

Thanks!
Boss

"Trevor Williams" wrote:

Hello Boss

You could remove the screenupdating events from your separate macros and
load a userform and then call your other macros from the forms Activate event:

Create a form called 'UserForm1' that contains a Label called 'Label1' (set
the label to autosize)
Then change your MainStart() to

Sub MainStart()
With UserForm1
.label1.value = ""
.show
End With


then, within the Forms Activate event type:

Private Sub UserForm_activate()
Application.screenupdating = False

me.label1.value = "Running Macro 1"
DoEvents 'refreshes the label
Call Macro1

me.label1.value = "Running Macro 2"
DoEvents
Call Macro2

me.label1.value = "Running Macro 3"
DoEvents
Call Macro3
...etc

Application.screenupdating = True
unload me 'closes the form once complete
End Sub

I'm sure the guru's on the forum can 'refine' my code, but it should work OK.

HTH
Trevor Williams


"Boss" wrote:

I just finished a long project in excel of preparing report from huge data.
The entire project was broken into different macro because of its requirment.
Each macro actually hits a other application to bring in data.

Now i have about 20 macros which i run and final report get generated. I
have a master macro which calls all the macros.

like

SUb mainstart()

call macro1
call macro2
call macro3
call macro4

End sub

First problem:

If i insert screenupdating false in the start and screenupdating true in the
end of the master macro, it does not helps. Still we see macro doing some
activity in the middle.

Second problem:

I wish to get some idea about the macro which is running, can we diplay the
name of the macro which is running on the userform.
or some kind of progress button to help user understand that how much time
in left.

This forum had helped me most is getting things done. Please help..
Thanks!
Boss

screenupdating and macro status
 

I just found one code for a progress bar. Its working fine... But how to
relate it to my situation.

Can somebody make somechanges and do it this was... progress happend when
one macro is finished. Else what Williams said was good.


Sub Main()
' Inserts random numbers on the active worksheet
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single

If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub
Cells.Clear
Application.ScreenUpdating = False
Counter = 1
RowMax = 100
ColMax = 25
For r = 1 To RowMax
For c = 1 To ColMax
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c
PctDone = Counter / (RowMax * ColMax)
With UserForm1
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
' The DoEvents statement is responsible for the form updating
DoEvents
Next r
Unload UserForm1
End Sub


Thanks!


"Boss" wrote:

The approach to the problem is perfect.
Thank you so much..

Thanks!
Boss

"Trevor Williams" wrote:

Hello Boss

You could remove the screenupdating events from your separate macros and
load a userform and then call your other macros from the forms Activate event:

Create a form called 'UserForm1' that contains a Label called 'Label1' (set
the label to autosize)
Then change your MainStart() to

Sub MainStart()
With UserForm1
.label1.value = ""
.show
End With


then, within the Forms Activate event type:

Private Sub UserForm_activate()
Application.screenupdating = False

me.label1.value = "Running Macro 1"
DoEvents 'refreshes the label
Call Macro1

me.label1.value = "Running Macro 2"
DoEvents
Call Macro2

me.label1.value = "Running Macro 3"
DoEvents
Call Macro3
...etc

Application.screenupdating = True
unload me 'closes the form once complete
End Sub

I'm sure the guru's on the forum can 'refine' my code, but it should work OK.

HTH
Trevor Williams


"Boss" wrote:

I just finished a long project in excel of preparing report from huge data.
The entire project was broken into different macro because of its requirment.
Each macro actually hits a other application to bring in data.

Now i have about 20 macros which i run and final report get generated. I
have a master macro which calls all the macros.

like

SUb mainstart()

call macro1
call macro2
call macro3
call macro4

End sub

First problem:

If i insert screenupdating false in the start and screenupdating true in the
end of the master macro, it does not helps. Still we see macro doing some
activity in the middle.

Second problem:

I wish to get some idea about the macro which is running, can we diplay the
name of the macro which is running on the userform.
or some kind of progress button to help user understand that how much time
in left.

This forum had helped me most is getting things done. Please help..
Thanks!
Boss