Selecting sheet after Userform
 

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

Selecting sheet after Userform
 

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


"Denny Behnfeldt" wrote in message
...
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

Selecting sheet after Userform
 

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

--
Regards,
Tom Ogilvy

"Denny Behnfeldt" wrote in message
...
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

Selecting sheet after Userform
 

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


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Denny Behnfeldt" wrote in message
...
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

Selecting sheet after Userform
 

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


"Denny Behnfeldt" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Denny Behnfeldt" wrote in message
...
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

Selecting sheet after Userform
 

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

--
Regards,
Tom Ogilvy




"Denny Behnfeldt" wrote in message
...
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


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Denny Behnfeldt" wrote in message
...
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