I have the following code, which runs a random number generator. The process
takes about 45 seconds to complete, so I would like display a pop-up box to
inform the user to wait for the process to complete. The code runs fine, but
when I add in code to show the userform I've created and then hide it, the
whole things just hangs and never finishes. Does anyone have a suggestion on
what may be wrong with calling the useform?

Sub TerminalValue()
'
' TerminalValue Macro
'

'
'Application.ScreenUpdating = False
'Application.Visible = False

Load UserForm1

UserForm1.Show
Application.Calculation = xlManual

RANDOM

Calculate

Application.Calculation = xlAutomatic

Sheets("FINANCIAL INPUTS").Select
Range("C10").Select
Range("C10").GoalSeek Goal:=0, ChangingCell:=Range("B9")
Range("B16").Select

Horizon

cashflow


Sheets("Input4").Select

Range("A1").Select

'Application.ScreenUpdating = True
UserForm1.Hide
'Application.Visible = True


End Sub

When you show a form, it is typically by default Modal. This means
that no code following the Show method will execute until the form is
hidden or unloaded. Only code within the shown form can execute. You
change the behavior to Modeless, in which case the form will be shown
and code will continue after the Show method. E.g.,

Useform1.Show vbMiodeless

The problem arises that a form shown modally cannot show a modeless
form. So, if your main form is shown modally, you cannot display the
"please wait" form modelessly. You'll have to display the main form
modally as well.

Another option would be to put a Label control on the main form, with
a big red font, telling the user to wait. This would eliminate the
modal/modeless problem entirely.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 11 Dec 2008 14:36:17 -0800, GH
wrote:

I have the following code, which runs a random number generator. The process
takes about 45 seconds to complete, so I would like display a pop-up box to
inform the user to wait for the process to complete. The code runs fine, but
when I add in code to show the userform I've created and then hide it, the
whole things just hangs and never finishes. Does anyone have a suggestion on
what may be wrong with calling the useform?

Sub TerminalValue()
'
' TerminalValue Macro
'

'
'Application.ScreenUpdating = False
'Application.Visible = False

Load UserForm1

UserForm1.Show
Application.Calculation = xlManual

RANDOM

Calculate

Application.Calculation = xlAutomatic

Sheets("FINANCIAL INPUTS").Select
Range("C10").Select
Range("C10").GoalSeek Goal:=0, ChangingCell:=Range("B9")
Range("B16").Select

Horizon

cashflow


Sheets("Input4").Select

Range("A1").Select

'Application.ScreenUpdating = True
UserForm1.Hide
'Application.Visible = True


End Sub

That info was very helpful Chip. The problem now is that if I run my dialog
box as modeless it appears washed out/blank and does not display as I
designed it. Do you know a way around this problem?

"Chip Pearson" wrote:

When you show a form, it is typically by default Modal. This means
that no code following the Show method will execute until the form is
hidden or unloaded. Only code within the shown form can execute. You
change the behavior to Modeless, in which case the form will be shown
and code will continue after the Show method. E.g.,

Useform1.Show vbMiodeless

The problem arises that a form shown modally cannot show a modeless
form. So, if your main form is shown modally, you cannot display the
"please wait" form modelessly. You'll have to display the main form
modally as well.

Another option would be to put a Label control on the main form, with
a big red font, telling the user to wait. This would eliminate the
modal/modeless problem entirely.


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 11 Dec 2008 14:36:17 -0800, GH
wrote:

I have the following code, which runs a random number generator. The process
takes about 45 seconds to complete, so I would like display a pop-up box to
inform the user to wait for the process to complete. The code runs fine, but
when I add in code to show the userform I've created and then hide it, the
whole things just hangs and never finishes. Does anyone have a suggestion on
what may be wrong with calling the useform?

Sub TerminalValue()
'
' TerminalValue Macro
'

'
'Application.ScreenUpdating = False
'Application.Visible = False

Load UserForm1

UserForm1.Show
Application.Calculation = xlManual

RANDOM

Calculate

Application.Calculation = xlAutomatic

Sheets("FINANCIAL INPUTS").Select
Range("C10").Select
Range("C10").GoalSeek Goal:=0, ChangingCell:=Range("B9")
Range("B16").Select

Horizon

cashflow


Sheets("Input4").Select

Range("A1").Select

'Application.ScreenUpdating = True
UserForm1.Hide
'Application.Visible = True


End Sub