Excel 2007 : Excel Shortcut- switch to previous sheet

I wanted a shortcut that moved between the last two sheets selected.

Similar to how Alt+Tab works with windows.

example: I have Sheet1 until Sheet 10, I work in Sheet2 then I switch my work to Sheet10, I want to back to Sheet2 quickly by using shortcut. Not use CTRL + PageUp because it just move to one previous sheet from Sheet10 to Sheet9.
Its really helpful if we have many sheet & need to work quickly.

Thanks

Originally Posted by teylyn

edit: OK, I see you dont't want to use Ctrl - PageUp. As an alternative, create an index sheet that lists all sheets in the book. Have a link to the index sheet on all other sheets (maybe in row 1 and freeze panes after row 1). On the index sheet have hyperlinks to all sheets in the workbook. Now, navigating to any of the sheets is just two clicks away - 1st click to index sheet -- 2nd click to desired target sheet

hth

thanks before, yes we can create index and use hyperlink, but the point is there any shortcut for swith between last 2 selected sheet?

Originally Posted by DonkeyOte

You would need VBA, perhaps something like the below (to reside in ThisWorkbook)

Please Login or Register  to view this content.

assign PriorSheet to a key shortcut of choice (via ALT + F8 in Native XL)

I can not run it, because have error in Then Prior.Select
Notification "Object Variable or With Block Variable not set"
In fact i quite not familiar with VBA...

Originally Posted by rwgrietveld

If this is VERY important to you, you might make an add-in.

here is a start (quick). let us know if this is the way forward.

1) all sheets are displayed
2) Click one and that sheet is selected (not working yet)

use the picture I have provided.

Please Login or Register  to view this content.

To test:
Make a userform. Put this code in and make sure that this link is correct:

"C:\...\Sht.bmp"

I already tried to run it in VBA user form and appear form like in the attachment with list of all sheet, but i can not do anything after that, can not click the sheet even select the cell to work, is there something wrong when i run this?

Originally Posted by rwgrietveld

You did nothing wrong. It is as it is. My question was:

Is this something you can use. A few steps need to be done, but I was short in time to finish and wanted to know if this is a preferred solution?

Actually I just want to use a simple short cut, it sound lazy thing but very useful for me in same case, so i think the conclusion is no short cut for switch between last two sheets selected and we need to create macro / VBA code for this. By the way, many thank for the sharing, I will learn to use macro / VBA first.

Originally Posted by teylyn

fua, DonkeyOte's suggestion and shg's version work. you just have to click another worksheet before you use the keyboard shortcut, so that Excel has a sheet to go back to!!. If you just install the macro and then hit the shortcut, without clicking another sheet, there is nothing that Excel can go back to.

Try it!

Thanks all, finally it's work. As you say use shg's work and need to to click another worksheet before use the keyboard shortcut. At first time, i directly assign shortcut, so nothing happen.
I have other question, how to apply this macro for all workbook, so i don't need to create macro in each workbook and when i open new workbook automatically can use this function?

Thanks

I already tried to create PERSONAL.XLS with macro inside it. Now when i open new book then automatically named PERSONAL.XLS (previously default is Book1). I test the macro still can be run then i save to other file name called it test.XLS and still can run the macro, but when i close test.XLS file and i open it again there will be 2 excel file opened, fisrt PERSONAL.XLS and second test.XLS and macro can not be run in test.XLS file. Is there something problem or its really work like that?