Previous Sheet macro?

[de8212]

I have a workbook with ~30 sheets. I'd like a command button on each sheet that will take me back to the previous sheet that I was on.

For example, if I am on Sheet 23 and then go to Sheet 5. I want a button on sheet 5 to take me back to Sheet 23.

Thanks for any help.

 

[John Davis]

I have a workbook with ~30 sheets. I'd like a command button on each sheet that will take me back to the previous sheet that I was on.

For example, if I am on Sheet 23 and then go to Sheet 5. I want a button on sheet 5 to take me back to Sheet 23.

Thanks for any help.

How about:

ActiveSheet.Previous.Select?

 

[John Davis]

I have a workbook with ~30 sheets. I'd like a command button on each sheet that will take me back to the previous sheet that I was on.

For example, if I am on Sheet 23 and then go to Sheet 5. I want a button on sheet 5 to take me back to Sheet 23.

Thanks for any help.

I don't think my last post is what you were looking for, but I think you may find a solution here:

http://www.pcreview.co.uk/forums/excel-go-previous-sheet-t3844744.html

 

[jmthompson]

Sub moveback()
Dim lastsheet As Long
lastsheet = (Right(ActiveSheet.CodeName, (Len(ActiveSheet.CodeName) - 5)) - 1)
Sheets("Sheet" & lastsheet).Select
End Sub

 

[klarowe]

And another option for you (also added a next sheet as well). This will also test to make sure the sheet is visible and if it is not, it moves onto the next visible sheet.

Sub NextSheet()
     Set Sh = ActiveSheet
         On Error Resume Next
     Do While Sh.Next.Visible <> xlSheetVisible
     If Err <> 0 Then Exit Do
     Set Sh = Sh.Next
     Loop
          Sh.Next.Activate
   On Error GoTo 0
End Sub
Sub PrevSheet()
     Set Sh = ActiveSheet
         On Error Resume Next
     Do While Sh.Previous.Visible <> xlSheetVisible
     If Err <> 0 Then Exit Do
     Set Sh = Sh.Previous
     Loop
          Sh.Previous.Activate
     On Error GoTo 0
End Sub

You can then create a form button and assign it to the macro, or create a command button and replace the "Sub____" with the command button click sub

 

[de8212]

Thanks for the help.

The pcreview link was the only one I could get to work but instead of going back to the last sheet I visited, it simply went back tot he tab right before the one I was on.

The others I couldn't even get to work. I'm sure it's something I am doing wrong.

Any other detailed steps?

 

[daverunt]

The pcreview link appears to do what you are asking.

If you click on the button on a page it will take you to the last page you were on. So if you are on sheet 5 then go to sheet 23 and press the button there it will go to sheet 5. Press the button there and it will return to sheet 23.
For each button on each sheet assign this macro

Put this in a module

Public lastsheet As String
Sub Select_Last()
Sheets(lastsheet).Select
End Sub

Put this in the ThisWorkbook sheet of the VBA project. Otherwise you get the problem you are describing.

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

 

[klarowe]

Did you try the code I posted? If you post the Sub PrevSheet into any module then create a form button and assign it to "PrevSheet" it should work perfectly. I'm using that exact code in my project now. But you have to assign it to a form control button (not activeX)

 

[de8212]

dave
Thanks for the clarification. Not sure what I did wrong the first time but it works now.

klarowe - I did try it but as I said, i'm sure I messed something up. Thanks for your help as well.