Web Style 'Back' Button VBA Code

[Steve_D]

Hi

I have been trying to get a back button to work on an 2003 excel document that works like a website.

In the web toolbar there is a facility to go back and forward (very much like using internet explorer). I want a button on the actual sheet that does this command. If i record a macro and click the web 'back' button it records the end result of the process, not the process itself -

eg if sheet 1 and sheet 2 link to sheet 3 and I click a hyperlink from sheet 1 to sheet 3 then create a back button on sheet 3 and record a macro by clicking on the web toolbar back button the macro records the end result - eg Application.Goto Reference:="Sheet1!R14C8". However if i then go to sheet 3 through sheet 2, the back button I created takes me to sheet 1 not back to sheet 2.

Anyone know the code I could use to make it actually go 'back' to the previous page?

Any help would be greatly appreciated

[Phil_V]

You'd need to use the workbook event to detect the sheet changing, and use that to store the most previous sheet, (either a single step or a list, depending what you want).
Your 'Back' button would then have to reference that variable / list, to work out which sheet to go back to.

[royUK]

Try this to go to the previous sheet

Please Login or Register  to view this content.

This for the nxt sheet

Please Login or Register  to view this content.

[Phil_V]

I don't think that is what is wanted, if I am understanding that macro correctly.

Won't that just step backwards and forwards through the worksheets in the order they are in the workbook?
I think the OP wants something that will step back through a 'History' of what sheets have been browsed.

[royUK]

A web back button steps back one page, you can't use the back button to jump back several pages. It's not 100% clear what the OP intends

[modytrane]

I haven't tried the code yet, but I think Roy's code would take you back and forth to previous or next sheet in order [sheet1, sheet2, sheet3 etc].

I think the OP wants to go back to the sheet he came from. So, if you are on sheet 3 and go to sheet 7, then clicking the back button on sheet 7 would take you back to sheet 3. But if you were on sheet 1 and then got to sheet 7, clicking the same back button would take you to sheet1.

I think you would have to create a list of sheets [history log] to keep track of previous sheets visited, so you can go back in that order.

modytrane

[Steve_D]

Thanks for the Replies

Modytrane is correct - Roys suggestion simply moves to the previous or next sheet - Sheet 2 has Sheet 1 as previous and Sheet 3 as next. This is not however the sheet you were on previously. I need it to work like a web browser back button.

How do i create a history log if that is needed?

Is there not a simple way to tell the macro to do the process the web back button does in excel toolbar?

[royUK]

You need to capture the sheet before moving, unfortunately the Sheet deactivate event captures the active sheet which is the on you have moved to

[Andy Pope]

If the web toolbar back/forward button work as required try this which should give the save effect as clicking them.

Please Login or Register  to view this content.

[romperstomper]

In case it's of interest, JKP has a free add-in called GoBack here that does this. The code is protected though, so you'd have to use it as is.

[Phil_V]

I'm not sure if this is the best method, but it works.
It will keep a history of '10', but you can change that by adjusting the red number.
The code should be pasted into the 'ThisWorkbook' area of the VBAProject.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Steve_D]

Thanks for the help. Got it to work now.

Used the simple command bar function Andy put up.

Phil - thanks for the info on recording history - will come in handy for other work.

Does anyone know of the command button IDs for the other buttons in Excel like this one? - application.CommandBars.FindControl(ID:=1017).Execute - is for the back button.

Would be really useful to have :-)

[romperstomper]

This will list them on the active sheet:

Please Login or Register  to view this content.

[Steve_D]

Thats great. Really useful

Thanks

[francoisehles]

Phil_V
when you delete one of the worksheets that is in the history
a "run-time error '424': object requires"

how do I get past that?
Thank you in advance!

[arlu1201]

francoisehles,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.