command button to return to active work sheet (SOLVED)

[sharky1978]

hi all

I have a work planner with 52 weeks in it,

I have a command button that allows me to go from the work planner to another sheet containing a holiday planner

I would like to add a command button on the holiday planner to take me back to the last active sheet i had been working in before selecting the go to holiday planner cmd button

is this possible and how would I go about it

(im still very new to VBA and macros)

thanks

Andy

[dmcgov]

review this link, that should get you going. if not, let me know

https://www.extendoffice.com/documen...ive-sheet.html

[PaulM100]

Or check this link https://www.mrexcel.com/forum/excel-...worksheet.html

[sharky1978]

hi paul
I have followed the instructions on the link but I cant work out what I have done wrong I keep getting, the error message then the cmd button wont do anything

the error message is "You have not switched sheets yet since opening the file!"

[PaulM100]

This means that you have not switched the sheets.

[sharky1978]

if I switch sheets the cmd button wont do anything when I go back in, and that message will not appear again

I think I have put the public variable in the wrong place, im still very new to vba so going to check I did the first part correctly

[PaulM100]

Upload a sample file and your current code. This will be the easiest way to get a solution

[sharky1978]

""Public MyPrevSheet As String


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MyPrevSheet = Sh.Name
End Sub

Sub GoToPreviousSheet()

If Len(MyPrevSheet) > 0 Then
Sheets(MyPrevSheet).Activate
Else
MsgBox "You have not switched sheets yet since opening the file!"
End If

End Sub
"
im not sure how to add the code to the post correctly sorry

[sharky1978]

I also tried

Private Sub CommandButton1_Click()

End Sub

Sub GoToPreviousSheet()

If Len(MyPrevSheet) > 0 Then
Sheets(MyPrevSheet).Activate
Else
MsgBox "You have not switched sheets yet since opening the file!"
End If

End Sub

[PaulM100]

Ok, try this:

Add the code below into ThisWorkbook module in VBA. Double click and paste it

Please Login or Register  to view this content.

Into a standard module add this code:

Please Login or Register  to view this content.

Create a button using Insert - Shapes and assign the code to it like: Right click - assign macro - SelectLastActive

[sharky1978]

lol your awesome cheers Paul

why cant they make it that easy on them help websites

[PaulM100]

Glad to help. Please mark the thread as Solved if that took care of your solution.
Also, each user may have a different problem/solution/approach, therefore the difficulty.