troubleshooting Question

IE Style navigation for Excel

Avatar of dvivek_aca
dvivek_acaFlag for India asked on
Microsoft Excel
2 Comments1 Solution416 ViewsLast Modified:
Hi Experts,

I am trying to get a piece of code / solution that will be enable me to navigate back and forth  on a excel worksheet. To explain, Lets say i move from Sheet 1 to Sheet 5 and to Sheet 7. If i then click a 'back button' i should go to Sheet 5 and if i hit the button again i should go to Sheet 1.

The navigation should happen in the 'Active Workbook'. So if i flip to another workbook, then i should be able to move back and forth within it based on the sheets selection i had made in that workbook.

I am not sure if this is something impossible.. using the code below, i have been able to achieve 'going back' provided this code is put tn the sheet i am trying to navigate. But what i am looking at is a code that will go into my 'Personal Macro sheet'. Thereby i need not put this code in every workbook i open.

Thanks
Vivek
' For the Code to work, create a sheet called 'Hist' in the workbook
''Code that goes into ThisWorkbook area
Public Sh As String, sht As Worksheet, asht As Workbook, r As Integer
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set sht = ThisWorkbook.Sheets("hist")
    sht.Cells.ClearContents
End Sub
Private Sub Workbook_Open()
    Set sht = ThisWorkbook.Sheets("hist")
    Set asht = ActiveWorkbook
   r = sht.Cells(1, 2)
    If IsNull(r) Or r = 0 Then r = 1
    
    sht.Cells(r, 1) = asht.ActiveSheet.Name
    sht.Cells(r, 2) = r + 1
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  Set sht = ThisWorkbook.Sheets("hist")
    Set asht = ActiveWorkbook
   r = sht.Cells(1, 2)
   sht.Cells(r, 1) = asht.ActiveSheet.Name
   sht.Cells(1, 2) = r + 1
End Sub
 
''Code that goes into Module1
Public Sh As String, sht As Worksheet, asht As Workbook, r As Integer
Sub go_back()
    Set sht = ThisWorkbook.Sheets("hist")
    Set asht = ActiveWorkbook
    r = sht.Cells(1, 2)
   r = r - 1
   If r <= 0 Then
        MsgBox "Reached last item in the history"
    Else
        s = sht.Cells(r, 1)
        asht.Sheets(s).Select
        sht.Cells(1, 2) = r
    End If
End Sub
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros