Change focus from Userform back to Sheet

[rocketslinger]

How do I programmatically change focus from a userform back to
worksheet?

 

[Chip Pearson]

Try

AppActivate Application.Caption


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[rocketslinger]

Tried that. Doesn't work. Unless I am placing the code in the wrong
section of my code. Where would be the best place to insert the code?

 

[Guest]

I have not found that it is possible to have a Userform visible and to focus on the worksheet.

VBA does not allow non modal Userforms. (Which is what it sounds like you are trying to accomplish)

You can still programmatically modify the worksheet while the userform is visible, but the user can not modify the worksheet while the Userform is visible.

You may have to find another way around what you are trying to do. :\

 

[evanhaarst]

I have been looking for a solution to this problem for a long time. I
still don't have it. But I don't agree with the previous reply.

You CAN have a userform visible while you work in the worksheet.
I have a permanent userform visible simply by a macro like
Worksheet.activate ... Userform.Show.
I enter codes from a listbox in the userform to cells in the worksheet.
Subsequently, I click on the worksheet to set the focus on the
worksheet and then I can work in the sheet. Meanwhile the userform is
still visible!
As this is possible, I am looking for a programming code that
automatically brings back the focus to the worksheet (after I have
chosen from the listbox in the userform).

Userform.Hide is therefor not appropriate.
Worksheet.activate does not do what we want.

We'll wait for suggestions...

 

[Chip Pearson]

Use AppActivate to return focus back to the worksheet. E.g.,

Private Sub CommandButton1_Click()
AppActivate Application.Caption
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com