Solved: How do you return / pass values?

[grichey]

What I'm trying to do: use ctrl alt pg up / dn to flip back and forth between last and current sheet. My thought was to pass the current sheet back and forth b/w subs so that "First Sheet" is always the last one you were on before hitting ctrl alt pgdown.



Private Sub Workbook_Open() 'receive and pass chseet
dim csheet as worksheet

Application.OnKey "^%{PGUP}", "FirstSheet" 'Ctrl-Alt-PageUp
Application.OnKey "^%{PGDN}", "LastSheet" 'Ctrl-Alt-PageDown
End Sub


Sub FirstSheet() 'receive csheet
dim csheet as worksheet
ActiveWorkbook.csheet.Select
End Sub

Sub LastSheet()
Dim csheet As Worksheet
csheet = ActiveSheet
'return csheet to top
ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.count).Select
End Sub

[Ago]

its not approved yet but might help you.
http://vbaexpress.com/kb/getapprovalarticle.php?kb_id=1021

i dont really understand what you are trying to do.

[CreganTur]

Deleted post- misunderstood what op was asking

[Charlize]

Paste this in the workbook coding place.Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Restore the keys to their original state
Application.OnKey "^%{PGUP}"
Application.OnKey "^%{PGDN}"
End Sub
Private Sub Workbook_Open()
Application.OnKey "^%{PGUP}", "FirstSheet" 'Ctrl-Alt-PageUp
Application.OnKey "^%{PGDN}", "LastSheet" 'Ctrl-Alt-PageDown
End Suband paste this in a module coding place.Option Explicit
Public csheet As Worksheet
Sub FirstSheet() 'receive csheet
If csheet Is Nothing Then
Set csheet = ActiveWorkbook.Worksheets(1)
End If
csheet.Select
End Sub

Sub LastSheet()
Set csheet = ActiveWorkbook.ActiveSheet
'return csheet to top
ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Select
End SubCharlize

[grichey]

Let me try to clarify.
Ctrl + pgdn / pgup goes left and right through tabs. I am trying to get ctrl + alt + pg dn to the last tab and ctrl + alt + pgup to go to the tab that I have just jumped the end from.

Ex. I have sheets 1 - 5. I am on sheet 3 and want to jump to last sheet (sheet5) and then want to jump back to sheet 3.

My thoughts are if I could pass the current sheet before jumping to the end back to the main sub, I could hold which sheet the 'current' sheet was and then jump back to that one.

[grichey]

Charlize,

Thanks. That did exactly what I was trying to do. That public variable dec was the bit I was unaware of how to do.

Thanks!

[grichey]

I'm having trouble visualizing my latest attempt at modifying this. I'm trying to figure out how to switch back and forth between current and last sheet I was on.

ex. I'm on sheet 2 and click sheet 4, have a key comb. (ctrl alt home etc) just toggle back and forth between sheets 2 and 4.
ideas?

[Paul_Hossler]

You marked it "Solved", but it sounds like it's still open:dunno

In the WB event handler"


Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Restore the keys to their original state
Application.OnKey "^%{PGUP}"
Application.OnKey "^%{PGDN}"
End Sub

Private Sub Workbook_Open()
Application.OnKey "^%{PGUP}", "PreviousSheet" 'Ctrl-Alt-PageUp
Application.OnKey "^%{PGDN}", "LastSheet" 'Ctrl-Alt-PageDown

On Error Resume Next ' in case of blank WB
Set wsPrevious = ActiveSheet
End Sub

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


and in a module


Option Explicit
Public wsPrevious As Worksheet
Sub PreviousSheet()
wsPrevious.Select
End Sub

Sub LastSheet()
'Note that this will be the far right one, NOT the last one added
ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count).Select
End Sub



Paul