Is there anyway to have Excel via VBA do a repetitive calc while a userform
is showing? So when I say:

Userform.Show

I want to mimic what would happen if I held down the F9 key. Now this could
either be based on:

1) a Loop with a timer
2) or it could be continous (triggered after the .show Method) and disabled
when I press a command button on the shown userform.

Any ideas?

Thanks

EM

There is no difference between the methods 1 and 2 - you would use method 1
to do method 2, so looks like you are set.

However, if calculation is set to automatic, there is not reason to do
anything else. If a change is made that requires calculation, calculation
will occur. The only reason I could think of to do what you describe is to
either update links or use the worksheet as a clock.

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
Is there anyway to have Excel via VBA do a repetitive calc while a
userform
is showing? So when I say:

Userform.Show

I want to mimic what would happen if I held down the F9 key. Now this
could
either be based on:

1) a Loop with a timer
2) or it could be continous (triggered after the .show Method) and
disabled
when I press a command button on the shown userform.

Any ideas?

Thanks

EM

Sorry Tom let me be more specific. I am trying to do something rather goofy.
I have set up a bunch of formulas on a sheet = CHAR(Randbetween(1,250)).
When I calc the sheet, the function generates random characters (like the
beginning of the Matrix Movie). Its really graphic thing. If I hold down
the f9 button, I can generate the affect. However I don't know how to
sustain this while my userfor is shown. My useform is see-through. I want
to display the form and simultaneously have the cells flickerin underneath.
Sounds silly, but I want to do it. I know how to calc within a loop. But
once the loop finishes, the form is shown and the effect is lost. Likewse if
I put the loop after the .Show method of the form, the form shows with no
affect, and then when it closes, the graphic start. I want to see the grapic
while the form is shown.

Thanks

"Tom Ogilvy" wrote:

There is no difference between the methods 1 and 2 - you would use method 1
to do method 2, so looks like you are set.

However, if calculation is set to automatic, there is not reason to do
anything else. If a change is made that requires calculation, calculation
will occur. The only reason I could think of to do what you describe is to
either update links or use the worksheet as a clock.

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
Is there anyway to have Excel via VBA do a repetitive calc while a
userform
is showing? So when I say:

Userform.Show

I want to mimic what would happen if I held down the F9 key. Now this
could
either be based on:

1) a Loop with a timer
2) or it could be continous (triggered after the .show Method) and
disabled
when I press a command button on the shown userform.

Any ideas?

Thanks

EM

Put the loop in the activate event of the userform.

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote in message
...
Sorry Tom let me be more specific. I am trying to do something rather
goofy.
I have set up a bunch of formulas on a sheet = CHAR(Randbetween(1,250)).
When I calc the sheet, the function generates random characters (like the
beginning of the Matrix Movie). Its really graphic thing. If I hold
down
the f9 button, I can generate the affect. However I don't know how to
sustain this while my userfor is shown. My useform is see-through. I
want
to display the form and simultaneously have the cells flickerin
underneath.
Sounds silly, but I want to do it. I know how to calc within a loop. But
once the loop finishes, the form is shown and the effect is lost. Likewse
if
I put the loop after the .Show method of the form, the form shows with no
affect, and then when it closes, the graphic start. I want to see the
grapic
while the form is shown.

Thanks

"Tom Ogilvy" wrote:

There is no difference between the methods 1 and 2 - you would use
method 1
to do method 2, so looks like you are set.

However, if calculation is set to automatic, there is not reason to do
anything else. If a change is made that requires calculation,
calculation
will occur. The only reason I could think of to do what you describe is
to
either update links or use the worksheet as a clock.

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
Is there anyway to have Excel via VBA do a repetitive calc while a
userform
is showing? So when I say:

Userform.Show

I want to mimic what would happen if I held down the F9 key. Now this
could
either be based on:

1) a Loop with a timer
2) or it could be continous (triggered after the .show Method) and
disabled
when I press a command button on the shown userform.

Any ideas?

Thanks

EM

in the form

Private Sub UserForm_Initialize()
gbCalc = True
doCalc
End Sub

Private Sub UserForm_Terminate()
gbCalc = False
End Sub

in a normal module

Public gbCalc As Boolean

Public Sub doCalc()
Static dtNextCalc As Date
If gbCalc Then
ActiveSheet.Calculate
dtNextCalc = Now + TimeSerial(0, 0, 2)
Application.OnTime dtNextCalc, "doCalc"
ElseIf dtNextCalc Now Then
Application.OnTime dtNextCalc, "doCalc", False
dtNextCalc = 0
End If
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ExcelMonkey wrote :

Sorry Tom let me be more specific. I am trying to do something
rather goofy. I have set up a bunch of formulas on a sheet =
CHAR(Randbetween(1,250)). When I calc the sheet, the function
generates random characters (like the beginning of the Matrix Movie).
Its really graphic thing. If I hold down the f9 button, I can
generate the affect. However I don't know how to sustain this while
my userfor is shown. My useform is see-through. I want to display
the form and simultaneously have the cells flickerin underneath.
Sounds silly, but I want to do it. I know how to calc within a loop.
But once the loop finishes, the form is shown and the effect is lost.
Likewse if I put the loop after the .Show method of the form, the
form shows with no affect, and then when it closes, the graphic
start. I want to see the grapic while the form is shown.

Thanks

"Tom Ogilvy" wrote:

There is no difference between the methods 1 and 2 - you would use
method 1 to do method 2, so looks like you are set.

However, if calculation is set to automatic, there is not reason to
do anything else. If a change is made that requires calculation,
calculation will occur. The only reason I could think of to do
what you describe is to either update links or use the worksheet as
a clock.

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in
message ...
Is there anyway to have Excel via VBA do a repetitive calc while a
userform
is showing? So when I say:

Userform.Show

I want to mimic what would happen if I held down the F9 key. Now
this
could
either be based on:

1) a Loop with a timer
2) or it could be continous (triggered after the .show Method) and
disabled
when I press a command button on the shown userform.

Any ideas?

Thanks

EM