How do I show a userform without it being active?

[GVF]

In Excel...I show a non-modal userform. When it appears on screen I don't want it active. I don't want the user to have to click on a cell to make the worksheet active again. Is there a way to accomplish this in VBA?
(The userform just shows data from cells that are out of view on the worksheet. Users tend to keep typing a bit before they notice that the worksheet is no longer active.) Disabling doesn't work because they can't dismiss the userform.

GVF

 

[fumei]

I don't know - but I am sure there is someone here who does.

In Word this is easy. You simply activate the application.

Sub UserForm_Activate()
Application.Activate
End Sub

This passes focus to the application immediately, and the userform is visible, but NOT active.

I am not an Excel person. I assumed there would be an Application.Activate...but there is not, at least not in 2002.

I quickly tried other things like an instruction to activate the sheet, but it has not worked. I am sure there is something. Essentially you want some instruction that will pass focus to the application immediately.

As I said, it is easy and straightforward in Word. I am surprised it seems more tricky in Excel.

Gerry
My paintings and sculpture

 

[GVF]

Yep...Tricky. Hope somebody helps.

Gvf

 

[fumei]

Come you Excel folks...just kidding. Seriously though, why is this difficult? As I posted, to do this in Word is easy. Can it possibly be that a part of Word VBA is better than Excel? Nah...impossible.

Although I do have a question for GVF.

You mention the users typing. This seems odd to me. SOMETHING is calling the userform. What is it? And why is it firing when the users are typing? Yes, they do tend to keep typing, but they must be doing something that is firing the userform.

Gerry
My paintings and sculpture

 

[CBasicAsslember]

If you only have one spread sheet open this should work.

Sub aTest()
UserForm1.Show vbModeless
AppActivate "Microsoft Excel"
End Sub

 

[GVF]

Fumei,
Thanks for the snippet...it works.
Background:
The user is entering data on the keyboard. The userform is fired by a command added to the "cell" shortcut menu. The user continues to enter data and then notices that they have to click on the spreadsheet again to make it active. That is (was) annoying. That is why I needed to re-activate the application and leave the userform on screen.
Once the userform is on screen - fields in the userform are updated automatically by an "OnEntry" routine.
All this worked very well except for the need to re-activate Excel.
As a side note...AppActivate "Microsoft Excel" didn't work at first. I have a custom App name in my add-in and I had to substiture that name for "Microsoft Excel".

GVF