Take Focus off a Modeless Userform and onto Worksheet?
 

I've created a Userform that I showed using the xlModeless option to display
some info about the Row that's Active on a Worksheet. I'd like it to be
visible but not have focus kind of like Toolbars. However, it always takes
focus right after I show it. Activating a cell afterwards does not help. I
have to manually click on a cell to take the focus off of it. Any ideas?

Take Focus off a Modeless Userform and onto Worksheet?
 

Maybe this ?

Private Sub CommandButton1_Click()
UserForm1.Show vbModeless
ActiveCell.Select
End Sub

NickHK

"tc69" wrote in message
...
I've created a Userform that I showed using the xlModeless option to
display
some info about the Row that's Active on a Worksheet. I'd like it to be
visible but not have focus kind of like Toolbars. However, it always
takes
focus right after I show it. Activating a cell afterwards does not help.
I
have to manually click on a cell to take the focus off of it. Any ideas?

Take Focus off a Modeless Userform and onto Worksheet?
 

Another one,

Sub test()
UserForm1.Show vbModeless
AppActivate Application.Caption
End Sub

This might not work running from the VBE but should if from a button.

Regards,
Peter T

"tc69" wrote in message
...
I've created a Userform that I showed using the xlModeless option to
display
some info about the Row that's Active on a Worksheet. I'd like it to be
visible but not have focus kind of like Toolbars. However, it always
takes
focus right after I show it. Activating a cell afterwards does not help.
I
have to manually click on a cell to take the focus off of it. Any ideas?

Take Focus off a Modeless Userform and onto Worksheet?
 

Woo Hoo! Thanks Man! That did it! It works like a dream! It does work
from the VBE. It even works with another Excel session with same Workbook
name open which I was worried about since the Help said "If there is more
than one instance of the application named by title, one instance is
arbitrarily activated."

"Peter T" wrote:

Another one,

Sub test()
UserForm1.Show vbModeless
AppActivate Application.Caption
End Sub

This might not work running from the VBE but should if from a button.

Regards,
Peter T

"tc69" wrote in message
...
I've created a Userform that I showed using the xlModeless option to
display
some info about the Row that's Active on a Worksheet. I'd like it to be
visible but not have focus kind of like Toolbars. However, it always
takes
focus right after I show it. Activating a cell afterwards does not help.
I
have to manually click on a cell to take the focus off of it. Any ideas?

Take Focus off a Modeless Userform and onto Worksheet?
 

Actually Help is correct and might be an issue in relatively rare scenarios,
eg
- multiple instances with no workbook fully maximized,
- same wb open as Read Only in multiple instances
- some code that changes app captions so all are the same.

Perhaps safer to do something like this

Dim s As String
s = Application.Caption
Application.Caption = s & "unique-string"
AppActivate Application.Caption
Application.Caption = s ' restore previous caption
or
Application.Caption = "" ' reset default caption

Regards,
Peter T


"tc69" wrote in message
...
Woo Hoo! Thanks Man! That did it! It works like a dream! It does work
from the VBE. It even works with another Excel session with same Workbook
name open which I was worried about since the Help said "If there is more
than one instance of the application named by title, one instance is
arbitrarily activated."

"Peter T" wrote:

Another one,

Sub test()
UserForm1.Show vbModeless
AppActivate Application.Caption
End Sub

This might not work running from the VBE but should if from a button.

Regards,
Peter T

"tc69" wrote in message
...
I've created a Userform that I showed using the xlModeless option to
display
some info about the Row that's Active on a Worksheet. I'd like it to
be
visible but not have focus kind of like Toolbars. However, it always
takes
focus right after I show it. Activating a cell afterwards does not
help.
I
have to manually click on a cell to take the focus off of it. Any
ideas?