More UserForm problems

[Les]

Hi

Following subs:

Private Sub CommandButton3_Click()
frmCalculator.Show vbModeless
ActiveCell.Activate
End Sub

Private Sub Worksheet_Activate()
frmCalculator.Show vbModeless
ActiveCell.Activate
End Sub

The button click sub works exactly as I need it. i.e. opens
userform then leaves cell activated and no focus on userform.
I would have thought the same would happen in the second sub but
it leaves the focus on the userform.

Any ideas why there seems to be a lack of consistency, or indeed
any pointers on how to achieve the effects of the first in the
second?

I am struggling with these userforms - to me it would appear that
I should get out of expecting my efforts to result in what I'm
trying to do! The more I try, the more I feel it's not my
inexperience, rather excel 'features' at fault.

regards,

 

[Bob Kilmer]

If it is any consolation, managing focus is always a nuisance, no matter how
good you are.

 

[Les]

Hi Bob

Thanks for your help. I am now using your suggestion, though I
find a mixture from each of the 2 solutions works best for me
(xlMinimized followed by xlNormal). What a way to have to go to
get it working though!

I certainly wouldn't have stumbled on that one, even though I
tried so many combinations of different things.

What would I do without the great help I get from people on this
newsgroup - give up probably.

And, by the way, quote:

would get over the fact the the dialog just opened -- uh, in

non-technical terms.

works just great fror me :=)

regards,
--
Les Hay, Livingston. Scotland

I find that the following works, in that it leaves the focus on the sheet.

Private Sub Worksheet_Activate()
'leaves window maximized
frmCalculator.Show vbModeless
Application.WindowState = xlMinimized
Application.WindowState = xlMaximized
End Sub

'or

Private Sub Worksheet_Activate()
'leaves window "normal"
frmCalculator.Show vbModeless
Application.WindowState = xlNormal
End Sub



The percieved lack of consistency stems from the order in which object
events occur in various contexts. Some priorities are built in. It sometimes
helps to implement every event known and trace the order that events occur.
Reading about focus and order of events helps. I discovered the above by
trial and error, reasoning that if we jerk the application around enough, it
would get over the fact the the dialog just opened -- uh, in non-technical
terms.

Bob Kilmer