Problem running code behind a userform

[Guest]

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

 

[Markus Scheible]

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

 

[Guest]

Cheers Markus, will give this a go.

Chris

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

 

[Guest]

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

 

[Guest]

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]

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

 

[Markus Scheible]

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/ED/Pages/ExcelProgressB
ar.htm

This is from Robin Hammonds site.

So far

Best

Markus

 

[Tom Ogilvy]

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.

 

[Guest]

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]

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

 

[Guest]

Tom,

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

Cheers,

Chris

 

[Guest]

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??

 

[Tom Ogilvy]

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.

 

[Markus Scheible]

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

 

[Guest]

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.

 

[Guest]

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]

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.

 

[Guest]

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.