Return to sheet where Macro was Launched

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,341
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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,214,773
Messages
6,121,476
Members
449,034
Latest member
Raygers

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
Back
Top