Return to sheet where Macro was Launched

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,059
Office Version
  1. 365
Platform
  1. Windows
I have a VBA code that is launched from a userform. It basically goes to a sheet named "HOURS" runs a Filter and then prints. My problem is that I have my program setup to where I can launch the userfrorm (UserForm46) from a number of different sheets say Sheet (1), Sheet (2) or Sheet (3)... When the print macro is run from the userfrom it leaves me on the Sheet titled HOURS. I want to be able to go back to the sheet I lanched it from. Note that it could be from a number of different sheets to its not as easy as Sheets("Sheet (1)").Select . Sheet (1) may not be where its was launched from.

Any suggestions?

Thanks
Greg in Orlando
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
Greg

Can we see the code?

Are you activating/selecting sheets?

If you are, you don't need to and focus will stay with the worksheet the macro was launched from.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi there Greg, welcome to the board!

You can do this by setting a variable at the beginning of your code ..

Code:
Sub MycodeMacro()
    Dim ws as worksheet
    Set ws = Activesheet
    '..
    '.. your code
    '..
    ws.Activate
End Sub

I'm not really sure why you are changing sheets in the first place though. It gives me a suspicion that your code may not be as efficient as it could be. Would you mind posting your code?
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
use something like this

Code:
Sub SetName()
Dim ws As Worksheet
Set ws = ActiveSheet
MsgBox "Hello, you opened the workbook named " & ws.Name & "."

End Sub

You'll have to modify as needed
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,059
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for the help

Here is the code;

Private Sub CommandButton3_Click()
Sheets("HOURS").Activate
Selection.AutoFilter Field:=1, Criteria1:="TRUE"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1
End Sub

This is launched from a userform. The userform is activated from various sheets
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
Like I said no need for selecting/activating.
Code:
Private Sub CommandButton3_Click()
    With Sheets("HOURS")
        .Range("A1").AutoFilter
        .Range("A1").AutoFilter Field:=1, Criteria1:="TRUE"
        .PrintOut Copies:=1, Collate:=True ' , Preview:=True
        .Range("A1").AutoFilter Field:=1
    End With
End Sub
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,059
Office Version
  1. 365
Platform
  1. Windows
THANKS FOR ALL THE HELP

USING

Sub SetName()
Dim ws As Worksheet
Set ws = ActiveSheet
MsgBox "Hello, you opened the workbook named " & ws.Name & "."

End Sub


DID THE JOB. THANKS TO ALL!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,004
Messages
5,834,833
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top