[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
10/24/2009 at 11:10AM PDT, ID: 24840760 | Points: 250
[x]
Attachment Details

IE Style navigation for Excel

Asked by dvivek_aca in Microsoft Excel Spreadsheet Software

Tags: Excel, VBA, Navigate to Sheets

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 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:

' 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
[+][-]10/26/09 09:32 PM, ID: 25669153

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091111-EE-VQP-91 - Hierarchy / EE_QW_3_20080625