I have a workbook with an update button on (which has a macro behind it).
When I press this button I want a userform to open and display a flashing
message for the duration of the macro. I have tested all the bits seperately
and they all work fine - the update macro by itself works and updates the
data and the userform opens and display a flashing message if opened by
itself.

The problem I have is, if I put the userform into the update macro, once I
click the button the userform displays, but none of the code in the macro
works. I have tried adding the DoEvents code in but this doesn't seem to help.

Can anyone help me?

Code below:
------------------------------------------

Sub UpdateData()
DoEvents
UserForm1.Show
~ My Code
UserForm1.Hide
End Sub

------------------------------------------

Private Sub UserForm_Activate()
Label1.Visible = False
StartLoop:
If Label1.Visible = False Then Label1.Visible = True Else Label1.Visible =
False
RunPause
GoTo StartLoop
End Sub

------------------------------------------

Sub RunPause()
PauseTime = 0.75 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
End Sub

------------------------------------------

Private Sub UserForm_Terminate()
End
End Sub

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

Cheers Markus, will give this a go.

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

Markus,

Just tried it and it still doesn't really do what I need (as I need the text
box to flash to make sure the users attention is got), so I have code to make
it visible and then hide if every 3/4 of a second. I think this is where I
have the problem because I just create an endless loop of displaying/hiding
the text box or userform (whichever way I try it) and I don't progress to the
next code.

Is there anyway I can run two losts of code in parallel?? (e.g. run my
update macro at the same time as continually looping through the hide/show
code)

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

Having thought about it further, it really needs to be a UserForm because the
update part of the macro opens other workbooks and so when this happens the
text box will disappear as it's only linked to the sheet on the Master. Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

Hi Chris,

by using Application.ScreenUpdate = False after making the
textbox or form visible and before any other steps you can
use them the whole time while the macro is running... with
the side-effect that no one finds out that your macro
opens other workbooks... at the end of the macro you just
turn it back to true.

About the two parallel macro codes... I don't think this
is possible... but I need to think about it again... maybe
there is a solution ;o) I remember that there is a code
that works in excel after excel97... do you work with
excel97?

So far

Best

Markus



-----Original Message-----
Having thought about it further, it really needs to be a
UserForm because the
update part of the macro opens other workbooks and so
when this happens the
text box will disappear as it's only linked to the sheet
on the Master. Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you
just
use the userform for displaying the alert while the
macro
is working, just use a text box from shapes instead of
the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

.

Hi Chris,

nevertheless, this is the link to the "processing message
box" which does not work with excel97... still thinking
about that... but maybe it helps you:

http://www.enhanceddatasystems.com/E...ExcelProgressB
ar.htm

This is from Robin Hammonds site.

So far

Best

Markus


-----Original Message-----
Cheers Markus, will give this a go.

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you
just
use the userform for displaying the alert while the
macro
is working, just use a text box from shapes instead of
the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

.

Your best bet is to show the userform modeless
userform1.Show vbModeless

then in your update code, have it periodically perform the action on the
userform that makes it blink or flash the way you want.

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Having thought about it further, it really needs to be a UserForm because
the
update part of the macro opens other workbooks and so when this happens
the
text box will disappear as it's only linked to the sheet on the Master.
Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

Markus,

Thanks for all the help - I'll try out some of the things you have
suggested. So you know I'm using Excel 2000.

Chris

"Markus Scheible" wrote:

Hi Chris,

by using Application.ScreenUpdate = False after making the
textbox or form visible and before any other steps you can
use them the whole time while the macro is running... with
the side-effect that no one finds out that your macro
opens other workbooks... at the end of the macro you just
turn it back to true.

About the two parallel macro codes... I don't think this
is possible... but I need to think about it again... maybe
there is a solution ;o) I remember that there is a code
that works in excel after excel97... do you work with
excel97?

So far

Best

Markus



-----Original Message-----
Having thought about it further, it really needs to be a
UserForm because the
update part of the macro opens other workbooks and so
when this happens the
text box will disappear as it's only linked to the sheet
on the Master. Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you
just
use the userform for displaying the alert while the
macro
is working, just use a text box from shapes instead of
the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

.

Hi Chris,

Thanks for all the help - I'll try out some of the things
you have
suggested. So you know I'm using Excel 2000.

no prob...


Best

Markus

Tom,

Don't suppose you could expand on that could you??

Cheers,

Chris

"Tom Ogilvy" wrote:

Your best bet is to show the userform modeless
userform1.Show vbModeless

then in your update code, have it periodically perform the action on the
userform that makes it blink or flash the way you want.

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Having thought about it further, it really needs to be a UserForm because
the
update part of the macro opens other workbooks and so when this happens
the
text box will disappear as it's only linked to the sheet on the Master.
Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

If this helps, I could do it another way - the reason I want a userform is to
display a flashing message saying not to touch the keyboard/mouse, because
while the update is running if the user switches to a different program the
marco crashes, so is there a way to basically disable all keystrokes and
mouse clicks while the macro is running, or a way that when the user tries to
change the window to ignore this and keep Excel running??

Does anyone have code for this??

"Markus Scheible" wrote:

Hi Chris,

by using Application.ScreenUpdate = False after making the
textbox or form visible and before any other steps you can
use them the whole time while the macro is running... with
the side-effect that no one finds out that your macro
opens other workbooks... at the end of the macro you just
turn it back to true.

About the two parallel macro codes... I don't think this
is possible... but I need to think about it again... maybe
there is a solution ;o) I remember that there is a code
that works in excel after excel97... do you work with
excel97?

So far

Best

Markus



-----Original Message-----
Having thought about it further, it really needs to be a
UserForm because the
update part of the macro opens other workbooks and so
when this happens the
text box will disappear as it's only linked to the sheet
on the Master. Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you
just
use the userform for displaying the alert while the
macro
is working, just use a text box from shapes instead of
the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

.

In a new workbook create userform1

Put a label in the center (label1). Size it about as big as the userform
and fill it with your message - use a large font size.

Now run this code.

Sub Main()
UserForm1.Show vbModeless
DoEvents
For i = 1 To 100
With Worksheets("sheet1")
For Each cell In .Range("A1:A100").Offset(0, i - 1)
cell.Value = Int(Rnd() * 100 + 1)
Next
End With
' just to make it slower.
For k = 1 To 100000
j = j + 1
Next
If i Mod 10 = 0 Then
a = Int(Rnd * 256 + 1)
b = Int(Rnd * 256 + 1)
c = Int(Rnd * 256 + 1)
UserForm1.Label1.ForeColor = RGB(a, b, c)
DoEvents
End If
Next
Unload UserForm1
End Sub


the macro is doing work when it writes random numbers to the sheets. This
just demonstrates a concept. Obviously it is more adaptable to a looping
situation, but if you have a long list of task you would do

task1
update form
task2
update form
task3
update form
task4

etc.


--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Tom,

Don't suppose you could expand on that could you??

Cheers,

Chris

"Tom Ogilvy" wrote:

Your best bet is to show the userform modeless
userform1.Show vbModeless

then in your update code, have it periodically perform the action on the
userform that makes it blink or flash the way you want.

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Having thought about it further, it really needs to be a UserForm
because
the
update part of the macro opens other workbooks and so when this
happens
the
text box will disappear as it's only linked to the sheet on the
Master.
Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

Hi Chris,


display a flashing message saying not to touch the
keyboard/mouse, because
while the update is running if the user switches to a
different program the
marco crashes, so is there a way to basically disable all

why would the macro crash when the user changes the
window? The only reason for that I could assume is using
ActiveWindow commands and all that stuff that excels macro
recorder uses... but you should not use activewindow and
selection commands but address all ranges and cells
directly... then the macro would not crash no matter what
the user does...

Best

Markus




keystrokes and
mouse clicks while the macro is running, or a way that
when the user tries to
change the window to ignore this and keep Excel running??

Does anyone have code for this??

"Markus Scheible" wrote:

Hi Chris,

by using Application.ScreenUpdate = False after making
the
textbox or form visible and before any other steps you
can
use them the whole time while the macro is running...
with
the side-effect that no one finds out that your macro
opens other workbooks... at the end of the macro you
just
turn it back to true.

About the two parallel macro codes... I don't think
this
is possible... but I need to think about it again...
maybe
there is a solution ;o) I remember that there is a code
that works in excel after excel97... do you work with
excel97?

So far

Best

Markus



-----Original Message-----
Having thought about it further, it really needs to be
a
UserForm because the
update part of the macro opens other workbooks and so
when this happens the
text box will disappear as it's only linked to the
sheet
on the Master. Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you
just
use the userform for displaying the alert while the
macro
is working, just use a text box from shapes instead
of
the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

.


.

Markus,

I don't know why the macro crashes, but I think it has something to do with
a memory read error (I have a seperate post for this in the Application
Errors section).

There are currently no ActiveWindow commands in to create this error.

"Markus Scheible" wrote:

Hi Chris,


display a flashing message saying not to touch the
keyboard/mouse, because
while the update is running if the user switches to a
different program the
marco crashes, so is there a way to basically disable all

why would the macro crash when the user changes the
window? The only reason for that I could assume is using
ActiveWindow commands and all that stuff that excels macro
recorder uses... but you should not use activewindow and
selection commands but address all ranges and cells
directly... then the macro would not crash no matter what
the user does...

Best

Markus




keystrokes and
mouse clicks while the macro is running, or a way that
when the user tries to
change the window to ignore this and keep Excel running??

Does anyone have code for this??

"Markus Scheible" wrote:

Hi Chris,

by using Application.ScreenUpdate = False after making
the
textbox or form visible and before any other steps you
can
use them the whole time while the macro is running...
with
the side-effect that no one finds out that your macro
opens other workbooks... at the end of the macro you
just
turn it back to true.

About the two parallel macro codes... I don't think
this
is possible... but I need to think about it again...
maybe
there is a solution ;o) I remember that there is a code
that works in excel after excel97... do you work with
excel97?

So far

Best

Markus



-----Original Message-----
Having thought about it further, it really needs to be
a
UserForm because the
update part of the macro opens other workbooks and so
when this happens the
text box will disappear as it's only linked to the
sheet
on the Master. Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you
just
use the userform for displaying the alert while the
macro
is working, just use a text box from shapes instead
of
the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

.


.

Tom,

Many thanks for this. The only problem with using this is that between
updates to the text box/userform there'd be a gap of about 30 seconds - 1
minute which is not really feasible for a flashing text message.

"Tom Ogilvy" wrote:

In a new workbook create userform1

Put a label in the center (label1). Size it about as big as the userform
and fill it with your message - use a large font size.

Now run this code.

Sub Main()
UserForm1.Show vbModeless
DoEvents
For i = 1 To 100
With Worksheets("sheet1")
For Each cell In .Range("A1:A100").Offset(0, i - 1)
cell.Value = Int(Rnd() * 100 + 1)
Next
End With
' just to make it slower.
For k = 1 To 100000
j = j + 1
Next
If i Mod 10 = 0 Then
a = Int(Rnd * 256 + 1)
b = Int(Rnd * 256 + 1)
c = Int(Rnd * 256 + 1)
UserForm1.Label1.ForeColor = RGB(a, b, c)
DoEvents
End If
Next
Unload UserForm1
End Sub


the macro is doing work when it writes random numbers to the sheets. This
just demonstrates a concept. Obviously it is more adaptable to a looping
situation, but if you have a long list of task you would do

task1
update form
task2
update form
task3
update form
task4

etc.


--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Tom,

Don't suppose you could expand on that could you??

Cheers,

Chris

"Tom Ogilvy" wrote:

Your best bet is to show the userform modeless
userform1.Show vbModeless

then in your update code, have it periodically perform the action on the
userform that makes it blink or flash the way you want.

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Having thought about it further, it really needs to be a UserForm
because
the
update part of the macro opens other workbooks and so when this
happens
the
text box will disappear as it's only linked to the sheet on the
Master.
Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

If a single command takes 30 seconds, for example opening a workbook, then
you could not have vba code running in a userform in parallel. In general,
vba code execution is not multithreaded or asynchronous.

If you mean you call a subroutine that takes 30 seconds, if it executes more
than one instruction, it could update your userform.

--
Regards,
Tom Ogilvy



"cdb" wrote in message
...
Tom,

Many thanks for this. The only problem with using this is that between
updates to the text box/userform there'd be a gap of about 30 seconds - 1
minute which is not really feasible for a flashing text message.

"Tom Ogilvy" wrote:

In a new workbook create userform1

Put a label in the center (label1). Size it about as big as the
userform
and fill it with your message - use a large font size.

Now run this code.

Sub Main()
UserForm1.Show vbModeless
DoEvents
For i = 1 To 100
With Worksheets("sheet1")
For Each cell In .Range("A1:A100").Offset(0, i - 1)
cell.Value = Int(Rnd() * 100 + 1)
Next
End With
' just to make it slower.
For k = 1 To 100000
j = j + 1
Next
If i Mod 10 = 0 Then
a = Int(Rnd * 256 + 1)
b = Int(Rnd * 256 + 1)
c = Int(Rnd * 256 + 1)
UserForm1.Label1.ForeColor = RGB(a, b, c)
DoEvents
End If
Next
Unload UserForm1
End Sub


the macro is doing work when it writes random numbers to the sheets.
This
just demonstrates a concept. Obviously it is more adaptable to a
looping
situation, but if you have a long list of task you would do

task1
update form
task2
update form
task3
update form
task4

etc.


--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Tom,

Don't suppose you could expand on that could you??

Cheers,

Chris

"Tom Ogilvy" wrote:

Your best bet is to show the userform modeless
userform1.Show vbModeless

then in your update code, have it periodically perform the action on
the
userform that makes it blink or flash the way you want.

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Having thought about it further, it really needs to be a UserForm
because
the
update part of the macro opens other workbooks and so when this
happens
the
text box will disappear as it's only linked to the sheet on the
Master.
Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable

It's not just one command, but it almost works like that. The code I'm trying
to run behind the user form open up another workbook, updates that one and
takes the summary sheet into a master file to give an overall summary.

Example code below:

Workbooks.Open "T:\Resourcing\Frontline\South West\Measures Logs\Measures
Log Master.xls", 0, 1
'Application.ScreenUpdating = True
Application.Run "'Measures Log Master.xls'!UpdateData"
Application.ScreenUpdating = False
Sheets("Summary").Select
Sheets("Summary").Name = "South West"
Sheets("South West").Move After:=Workbooks("Measures Log Master - All
Areas.xls").Sheets("Summary")
FormatSheet
Sheets("Summary").Select
Windows("Measures Log Master.xls").Activate
ActiveWindow.Close

Workbooks.Open "T:\Resourcing\Frontline\South East\Measures Log\Measures
Log Master.xls", 0, 1
'Application.ScreenUpdating = True
Application.Run "'Measures Log Master.xls'!UpdateData"
Application.ScreenUpdating = False
Sheets("Summary").Select
Sheets("Summary").Name = "South East"
Sheets("South East").Move After:=Workbooks("Measures Log Master - All
Areas.xls").Sheets("Summary")
FormatSheet
Sheets("Summary").Select
Windows("Measures Log Master.xls").Activate
ActiveWindow.Close

Whilst it is running this code I want to either have a message on a user
form running (which isn't a problem until I try and make the label flash,) or
to disable the user being able to switch to anything else.

"Tom Ogilvy" wrote:

If a single command takes 30 seconds, for example opening a workbook, then
you could not have vba code running in a userform in parallel. In general,
vba code execution is not multithreaded or asynchronous.

If you mean you call a subroutine that takes 30 seconds, if it executes more
than one instruction, it could update your userform.

--
Regards,
Tom Ogilvy



"cdb" wrote in message
...
Tom,

Many thanks for this. The only problem with using this is that between
updates to the text box/userform there'd be a gap of about 30 seconds - 1
minute which is not really feasible for a flashing text message.

"Tom Ogilvy" wrote:

In a new workbook create userform1

Put a label in the center (label1). Size it about as big as the
userform
and fill it with your message - use a large font size.

Now run this code.

Sub Main()
UserForm1.Show vbModeless
DoEvents
For i = 1 To 100
With Worksheets("sheet1")
For Each cell In .Range("A1:A100").Offset(0, i - 1)
cell.Value = Int(Rnd() * 100 + 1)
Next
End With
' just to make it slower.
For k = 1 To 100000
j = j + 1
Next
If i Mod 10 = 0 Then
a = Int(Rnd * 256 + 1)
b = Int(Rnd * 256 + 1)
c = Int(Rnd * 256 + 1)
UserForm1.Label1.ForeColor = RGB(a, b, c)
DoEvents
End If
Next
Unload UserForm1
End Sub


the macro is doing work when it writes random numbers to the sheets.
This
just demonstrates a concept. Obviously it is more adaptable to a
looping
situation, but if you have a long list of task you would do

task1
update form
task2
update form
task3
update form
task4

etc.


--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Tom,

Don't suppose you could expand on that could you??

Cheers,

Chris

"Tom Ogilvy" wrote:

Your best bet is to show the userform modeless
userform1.Show vbModeless

then in your update code, have it periodically perform the action on
the
userform that makes it blink or flash the way you want.

--
Regards,
Tom Ogilvy

"cdb" wrote in message
...
Having thought about it further, it really needs to be a UserForm
because
the
update part of the macro opens other workbooks and so when this
happens
the
text box will disappear as it's only linked to the sheet on the
Master.
Any
ideas??

Chris

"Markus Scheible" wrote:

Hi unknown,

maybe this is no answer to your question, but if you just
use the userform for displaying the alert while the macro
is working, just use a text box from shapes instead of the
userform, and you don't have all this trouble...

Best

Markus


-----Original Message-----
Message unavailable