web-style back button

[lpking2005]

hi,

im in need of some help...

I have a workbook which contains alot of sheets, i have a Back button and home button at the top of each sheet.

Im having trouble finding a way of making the back button remember the previous selected sheets so the user can always go back to the previous sheet they came from.

Ive found some thing on various forums but nothing works very well.

I did find something which looked promising, but i get an error when the code runs.
This uses the back button from the "web" Commandbar:

Application.CommandBars.FindControl(ID:=1017).Execute ' Back

any help would be appreciated!
thanks!

 

[gsbelbin]

One way to do this is to create two Public variables; say, CurrentSheet and PreviousSheet as worksheets ie in a separate module write

Public CurrentSheet as Worksheet
Public PreviousSheet as Worksheet

Then in the code for ThisWorkbook enter the following

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Set PreviousSheet = CurrentSheet 
    Set CurrentSheet = Sh
End Sub
 
Private Sub Workbook_Open()
    Set CurrentSheet = ActiveSheet
End Sub

Create your button and add the following code for its click event

Private Sub CommandButton1_Click()
    If Not(PreviousSheet Is Nothing) Then
        PreviousSheet .Select
    End If
End Sub

This doesn't allow the user to move back more than one sheet. If you wanted that then it would be a lot more complicated in that you'd have to keep the history as an array or on a sheet. It probably also needs some error handling.

Cheers

Gordon

 

[lpking2005]

thanks for the reply, but i do need a way of going back more.

 

[lpking2005]

please can anyone help me with this?

 

[gsbelbin]

Working on it now

 

[lpking2005]

thank you!!

 

[gsbelbin]

Right, here goes ...

In a module enter the following

Public History As Collection
Sub Back()
    If History.Count > 1 Then
        Application.EnableEvents = False
        History.Item(History.Count - 1).Select
        Application.EnableEvents = True
        History.Remove History.Count
    End If
End Sub

In the ThisWorkbook module enter this

Private Sub Workbook_Open()
    Set History = New Collection
    History.Add ActiveSheet
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    History.Add Sh
End Sub

Then add buttons to each of your sheets and just have Back in their Click procedures ie they just call the Back procedure in the other module.

The History collection holds the list of sheets you've visited in order. It gets instantiated when you open the workbook. If you are testing and already have the workbook open it won't have been instantiated and will fail. All you need to do is run the Workbook_Open procedure ie put your cursor in it and press F5.

I think I've covered everything but give it a try and let me know how you get on.

Cheers

Gordon

 

[lpking2005]

Many thanks!! that works absolutely perfect!!

 

[gsbelbin]

Just thinking about this a bit more. I'm not sure how the Collection object stores information. If it stores a copy of each worksheet and your sheets are quite large then there is the potential for this approach to use a lot of memory if someone flicks through a lot of sheets without using the Back button. An alternative which is probably safer is to just store the worksheet names in the Collection. So your revised code would be as folows.

The Back procedure

Sub Back()
    If History.Count > 1 Then
        Application.EnableEvents = False
        Sheets(History.Item(History.Count - 1)).Select
        Application.EnableEvents = True
        History.Remove History.Count
    End If
End Sub

And the code in the ThisWorkbook module

Private Sub Workbook_Open()
    Set History = New Collection
    History.Add ActiveSheet.Name
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    History.Add Sh.Name
End Sub

The rest would be the same.

Cheers

Gordon

 

[gsbelbin]

I've looked at this a bit more and found that when you add a sheet to a collection it just adds a pointer to the actual sheet. So it doesn't make a copy. Makes sense really but I wasn't sure. So the first version of the code I gave will not result in memory problems and you can use either version safely.

Cheers

Gordon