Excel 2007 : Excel Shortcut- switch to previous sheet

[fua]

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

[teylyn]

Ctrl-PgDwn for sheet forward
Ctrl-PgUp for sheet back

Ctrl-tab for next workbook
Ctrl-Shift-tab for previous workbook


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

[fua]

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?

[DonkeyOte]

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

Dim Prior As Worksheet
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set Prior = Sh
End Sub

Public Sub PriorSheet()
If ActiveWorkbook.FullName = ThisWorkbook.FullName Then Prior.Select
End Sub

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

[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.

Private Sub UserForm_Initialize()
  Dim Sht As Worksheet
  Dim PictArray() As Control, LabelArray() As Control
  Dim x As Byte
  
  ReDim PictArray(1 To 30)
  ReDim LabelArray(1 To 30)
  x = 1

  For Each Sht In Worksheets
    Set PictArray(x) = Me.Controls.Add("Forms.Image.1", Sht.Name)
    Set LabelArray(x) = Me.Controls.Add("Forms.Label.1", Sht.Name)
    LabelArray(x).Caption = Sht.Name
    LabelArray(x).TextAlign = 2
    LabelArray(x).BackStyle = 0
    LabelArray(x).BorderStyle = 0
    LabelArray(x).AutoSize = True
    PictArray(x).Picture = LoadPicture("C:\...\Sht.bmp")
    PictArray(x).Width = 66
    PictArray(x).Height = 24
    PictArray(x).Left = 5 + 71 * ((x + 2) Mod 3)
    LabelArray(x).Left = PictArray(x).Left + 19
    PictArray(x).Top = 5 + CByte((x - 2) / 3) * 30
    LabelArray(x).Top = PictArray(x).Top + 11
    Me.Caption = "Switch to Sheet ..."
    Me.Height = PictArray(x).Top + 54
    Me.Width = PictArray(x).Width * 3 + 30
    x = x + 1
  Next
End Sub

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

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

[fua]

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.

Private Sub UserForm_Initialize()
  Dim Sht As Worksheet
  Dim PictArray() As Control, LabelArray() As Control
  Dim x As Byte
  
  ReDim PictArray(1 To 30)
  ReDim LabelArray(1 To 30)
  x = 1

  For Each Sht In Worksheets
    Set PictArray(x) = Me.Controls.Add("Forms.Image.1", Sht.Name)
    Set LabelArray(x) = Me.Controls.Add("Forms.Label.1", Sht.Name)
    LabelArray(x).Caption = Sht.Name
    LabelArray(x).TextAlign = 2
    LabelArray(x).BackStyle = 0
    LabelArray(x).BorderStyle = 0
    LabelArray(x).AutoSize = True
    PictArray(x).Picture = LoadPicture("C:\...\Sht.bmp")
    PictArray(x).Width = 66
    PictArray(x).Height = 24
    PictArray(x).Left = 5 + 71 * ((x + 2) Mod 3)
    LabelArray(x).Left = PictArray(x).Left + 19
    PictArray(x).Top = 5 + CByte((x - 2) / 3) * 30
    LabelArray(x).Top = PictArray(x).Top + 11
    Me.Caption = "Switch to Sheet ..."
    Me.Height = PictArray(x).Top + 54
    Me.Width = PictArray(x).Width * 3 + 30
    x = x + 1
  Next
End Sub

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?

[fua]

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

Dim Prior As Worksheet
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set Prior = Sh
End Sub

Public Sub PriorSheet()
If ActiveWorkbook.FullName = ThisWorkbook.FullName Then Prior.Select
End Sub

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...

[shg]

You have to make sure there was a prior sheet:

Dim wksPrior           As Worksheet

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

Public Sub PriorSheet()
    If ActiveWorkbook Is ThisWorkbook Then IIf(wksPrior Is Nothing, ActiveSheet, wksPrior).Select
End Sub

[teylyn]

not in native Excel. But maybe possible with some VBA tricks (out of my league, I'm afraid)

[rwgrietveld]

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

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

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?

[fua]

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.

[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!

[DonkeyOte]

DonkeyOte's suggestion and shg's version work

Being honest my code would have debugged should no prior sheet have existed at run time whereas shg's modified version would not - so in short use that rather than mine.

[fua]

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

[shg]

Put the code in your Personal.xls file (http://office.microsoft.com/en-us/ex...872961033.aspx) or an add-in (http://www.fontstuff.com/vba/vbatut03.htm)

[fua]

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?