Take Focus off a Modeless Userform and onto Worksheet?

[Guest]

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?

 

[NickHK]

Maybe this ?

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

NickHK

 

[Peter T]

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

 

[Guest]

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]

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