Navigate to the most recent worksheet

[wormaji]

I'm working with a workbook which has got 11 worksheets. I have frequently switch between worksheets. Mostly I work between different pairs of worksheets. If I use the mouse, sometimes I have to scroll left or right to find the worksheet that I to switch to.


I was hoping that there would be some kind of a keyboard shortcut like "control + tab" available in Microsoft Excel. But I could not find anything. I also looked at the meditation but surprisingly I was not able to find anything. To me, this should be a common problem and I was hoping there would be a solution out there. Even if it is in terms of using a macro, that is also acceptable.


Thank you for any inputs
PS: I am using microsoft excel 2010 for now.

 

[Yongle]

Try this

In ThisWorbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set OtherSheet = Sh
End Sub

In standard module
- OtherSheet variable declaration must be placed at top of module above all procedures

Public OtherSheet As Worksheet

Sub ToggleShheets()
    OtherSheet.Activate
End Sub

In Excel window
{alt}{F8} to see Macro list \ select OtherSheet \ Options \ insert your preferred short-cut key

Now you can always toggle back to previous sheet using that shortcut

 

[Kamolga]

You can use CTRl+PgUp and CTRL+PgDn to move from one sheet to another.

 

[Yongle]

@Kamolga
OP is asking for solution to move between any 2 sheets
- Your suggestion activates the next sheet (up or down)

Going from sheet1 to sheet11 would require {PageDown} to be pressed 10 times

 

[Kamolga]

You can keep pressing it, it goes quite fast, so very good to go from sheet1 to sheet 11 but from 1 to 7, I stopped in 8 and going back to 3 I stopped in 1
I have to admit I did not really understood the question, as I thought he was looking for a macro shortcut or in the commandbar (I prefer than shortcuts with 3 buttons) such as

[LEFT][COLOR=#222222][FONT=Verdana]sheets(sheets.count).select[/FONT][/COLOR][/LEFT]

to go to the last sheet in one shot and sheets(1).select to come back. Indeed having a public long such as i saved as the index of the desactivate sheet would answer the question with sheets(i).select to go to the previous sheet

 

[wormaji]

@Yongle Thank you for your response. It took me some bit of time because I had to understand what a standard module is and also where to place the code.Finally, it seems to work.
However, this will work only when I save the entire workbook is macro enabled workbook. I have some macros that are there in personal.xlsb VBA project. I understand that these macros will be executed for any Microsoft Excel workbook. These macros were given to me by another person for productivity purposes. I tried placing the macro that you have provided within this VBA project but it does not work. I keep getting an error
"Error 91 - object variable or with block not set" and it refers to the othersheet variable


I had placed to the code for the workbook in the thisworkbook and the code for the togglesheets in a new module module 2. I also tried placing the code in the existing module which was module 1 at the top of the other procedures.


That does not work. Is there any way to make this available across all Microsoft Excel workbooks

@kamloga - Thank you for your response. I actually have to work in a workbook which has got 12 worksheets and have to go back-and-forth between worksheet that are five or more worksheets apart. I tried to place them together, but then I have to also switch between different pairs of worksheets. In addition, I have a medical condition due to which I am not able to use the mouse or keyboard often and have to rely on speech recognition. These macros help a lot in my case.

 

[Yongle]

Ah - it was easy with a single workbook but is now trickier and needs some more consideration.
To use my original approach would require using a Class module (in ipersonal workbook) to handle event triggers in all open workbooks
There may be an alternative method to provide similar functionality.

I am away from my PC for a few days so cannot test anything - will update the thread after that.

 

[jkpieterse]

Perhaps my little GoBack tool is useful? https://jkp-ads.com/Download.asp#goback

 

[Yongle]

@wormaji

I suggest you test what @jkpieterse has provided

 

[wormaji]

Thank you for a wonderful tool.It was very useful and very simple to use. I have also downloaded flexfind and will start using it. Also, just as the experience, I was not able to install the add-in directly from Microsoft Excel 2010. It would not bring up the dialogue box which prompts the user to allow the add-in to be installed. I had to install it manually by going to Microsoft Excel menu on developer and then clicking add in. It's a minor issue. Maybe it is a problem with my installation. But I'm just letting you know so that you can incorporate it in the readme for people who are having trouble. Once again, thank you for providing this for free. I have medical problems and right now struggling with finance but I will definitely make a donation once things get a bit better. I was a programmer once and I know how much hard work is there to make a reliable product that works well. Th