Pressing space bar to navigate tabs

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
284
Is this even possible vba code :-

pressing space bar to navigate sheet tabs?

Selects and displays next sheet each press
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,037
Office Version
  1. 365
Platform
  1. Windows
This is possible. But you can't do it with the space bar. That key is not allowed.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,037
Office Version
  1. 365
Platform
  1. Windows
This code will do it with backspace. Run the sub SB, then every time you hit backspace, it will select the next sheet.

Code:
Sub SB()
Application.OnKey "{BACKSPACE}", "NEXTTAB"
End Sub

Sub NEXTTAB()
If ActiveSheet.Index < Sheets.Count Then
    Sheets(ActiveSheet.Index + 1).Select
End If
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Create a button and assign this

Code:
Sub MoveNext()
On Error Resume Next
Sheets(ActiveSheet.Index + 1).Activate
If Err.Number <> 0 Then Sheets(1).Activate
End Sub
 

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
284

ADVERTISEMENT

This code will do it with backspace. Run the sub SB, then every time you hit backspace, it will select the next sheet.

Code:
Sub SB()
Application.OnKey "{BACKSPACE}", "NEXTTAB"
End Sub

Sub NEXTTAB()
If ActiveSheet.Index < Sheets.Count Then
    Sheets(ActiveSheet.Index + 1).Select
End If
End Sub


I had to remove End If for it to work :)

What other key is allowed ?
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707

ADVERTISEMENT

Ctrl+page up/page down
 

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
284
This code will do it with backspace. Run the sub SB, then every time you hit backspace, it will select the next sheet.

Code:
Sub SB()
Application.OnKey "{BACKSPACE}", "NEXTTAB"
End Sub

Sub NEXTTAB()
If ActiveSheet.Index < Sheets.Count Then
    Sheets(ActiveSheet.Index + 1).Select
End If
End Sub


this is great Thankyou - what would be the alternative to go to the previous tab - again assigning another key for this action
 

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
284
this is great Thankyou - what would be the alternative to go to the previous tab - again assigning another key for this action

so on key “>” selects next tab
And on key “<“ selects previous tab
 

CountTepes

Active Member
Joined
Nov 8, 2010
Messages
252
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Why not just use the build in tabbing of; Ctrl-Page Up and Ctrl-Page Down ?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,311
Messages
5,600,886
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top