Back button macro to remember history

[Magriza]

Good morning all,

I'm after a little help (well, a lot of help actually)! I've created a menu using VBA and I have this nifty little back button. I can make it work for the last sheet viewed, but not for more than one sheet.

For example: If I am on sheet1, run a macro to go to sheet2, I can use my back button to return to sheet1. However, if I go from sheet1 to sheet2 and then sheet3 (all via macros), I cannot get back to sheet1 using my button.

Is there any way to create history somewhere and then move backwards along that history every time the back button is pressed?

I've thought about writing the active sheet to a hidden sheet somehow and then moving back up the list, but my VBA skills are not up to the task.

Any help would be much appreciated.

Asante sana!

 

[venkat1926]

wild guess
can you not put in the back button macro an input box where you can indicate the sheet you want to go. In that case you can go either backward or forward. at any stage if you want to go to sheet 1 only then you can modify the macro to go sheet 1 only everytime you click the backbutton.

 

[Magriza]

Sadly, that's something that I won't work. The workbook has 38 sheets and the naming of them isn't very logical (except to me).

Mostly, the navigation is done from a front page, but the user may delve into 4 or 5 sheets and I need the back button to take the user back to any one of those sheets before the main page.

 

[venkat1926]

you may have valid reasons to have back button. are you having this button in the first sheet (frontpage) or in frontpage application. any how I did some experiments.

the code I am sending you is a workbook event codel
right click excel icon (left of file Menu) and click view code
type or copy paste the code given below.

see the comments inthe code(which are essentially for me). which may of help to you . the code may be little clumsy perhaps this may not be useful to you now but sometime you can use, if you require after modificatin this enables you to go to any sheet from ANY sheet.

this is only en experinet as far as I am concerend (perhaps I am reinventing wheel). so if there is error or bug revert to the newsgroup.
venkat
the event code is
==============

'activate sheet1. pressing control click all the other sheets
'now type in column E(or any other  empty column) type the name of the sheets
'from E1 down and hit enter
'all the sheet names will be entered in all the sheets.
'select the sheet name where you want to go you will automatically go to that sheet.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False

If Target.Column <> 5 Then GoTo line1

'in the above line dont type exit sub because the enableevents is false and if
'you exit sub at this stage  enableevents will remain false. you have to turn it true

On Error GoTo line1

'this line is necessary if you select any cell in column E(5) which is blank or no sheetname other than the cells having name of the sheets

Worksheets(Target.Value).Activate
Cells.Find(ActiveSheet.Name).Activate

' I find that when you select a cell after going to the required sheet, cell selesction shifts to the cell having name of the
'sheet from where the event was enabled.So the above line (perhpas a clumsy attempt)

line1:
Application.EnableEvents = True
End Sub

 

[Magriza]

Hi venkat1926,

Thanks for the reply, however your code isn't quite what I'm after. That would be useful for navigating around the workbook, but I can already do that.

Perhaps it would help if I explain what I already have in place.

I have a public declaration:

Public lastSheet As Worksheet

And then I have this before my macros that navigate to other pages

Set lastSheet = ActiveSheet

Finally, this takes me back to the last active sheet.

Sub Back()
    
    lastSheet.Activate

End Sub

What I can't seem to figure out is how to go back further than just the last sheet!

If you can help with that it would be great! I'd be this happy:

 

[venkat1926]

will this of fhelp try

Sub test()
Dim lastsheet As Worksheet
Set lastsheet = ActiveSheet
Dim i As Integer
i = ActiveSheet.Index
Worksheets(i - 1).Activate
Worksheets(i + 1).Activate
'the above two lines are  only an example yu can choose  i-1, i-2 etc
'at some stage as the no. of indexes get exhausted it will give error
'appropriately introduce a line--on error exit sub
End Sub

 

[Magriza]

Hi venkat1926,

Unfortunately that doesn't work either. That takes me to the worksheet before the one I'm on. But my user won't always need to go back to that one. They could go from sheet 28 to sheet 17 and then sheet 23 and then sheet 10.

I would want them to be able to press the back button to take them from sheet 10 to 23, then 17 and then 28.

The solution still eludes me....

 

[venkat1926]

I think it takes any where

if activesheet is 8 does that not mean i=8
if you want to go to 13
will not worksheets(i+5).activate take to sheet 13.

from sheet 28 to 17
worksheets(i-11).acttivate

you have to create an inut box wherre you type -11 you get ,
is that what you want.

venkat

 

[Magriza]

Hi venkat,

Having an input box defeats the pupose of a back button. I already have a toolbar to aid navigation as well as a group of buttons. I'm just trying to find a way of going back more than one by pressing a single button.