Selecting sheet after Userform

  • Thread starter Thread starter Denny Behnfeldt
  • Start date Start date
D

Denny Behnfeldt

I have a Userform that is activated from one toolbar while any of 7 sheets
(MON, TUE, etc.) is active. When the Userform is done doing it's work and is
closed, I want to return to the sheet from which it was launched. Any ideas
on how I would do this?

This newsgroup is incredibly helpful!
Thanks alot,
Denny
 
Denny,

try this

In the declarations section at the top of your form module
Private shOrigin as worksheet

In UserForm_Initialize
Set shOrigin = ActiveSheet

In UserForm_Terminate
shOrigin.Select
Set shOrigin = Nothing

Robin Hammond
www.enhanceddatasystems.com
 
at the top of the userform module

Dim sh as Worksheet

in the initialize event

set sh = Activesheet

in the code that closes the userform

sh.Activate
 
Thanks for both responses.
For Tom's:
I am getting a "Run Time '91' Error, object variable or With Block variable
not set". This is when I put "sh.Activate" in the closing code of the Close
CommandButton I use.

"At the top of userform module" I assume is under (General), (Declarations).
I understand the logic, but must be missing something.

I tried Robin's solution also, and I don't get an error, but I don't get the
right sheet selected.

Any ideas what I'm missing?
Denny
 
It sounds like you are not setting the original sheet correctly.

In the declarations section at the top of your form module(the very top line
of the code, above any subs or functions)

Private shOrigin as worksheet

In UserForm_Initialize
Set shOrigin = ActiveSheet
'add this just to check it is being set properly
Msgbox(shOrigin.Name)

In UserForm_Terminate
'add this to see what is happening
MsgBox(shOrigin.Name)

shOrigin.Select
Set shOrigin = Nothing

Robin Hammond
www.enhanceddatasystems.com
 
In the userform Module I had this code:

'----< Declarations >-------------
Dim sh As Worksheet

'----< Code >--------------------
Private Sub CommandButton1_Click()
' activate a random sheet
num = Int(Rnd() * Sheets.Count + 1)
Sheets(num).Activate
End Sub

Private Sub CommandButton2_Click()
sh.Activate
Unload Me
End Sub

Private Sub UserForm_Initialize()
Set sh = ActiveSheet
End Sub


works fine for me. I close the form with Commandbutton1
 
Back
Top