Sunday, May 4, 2008

Excel Back Button

This Excel macro acts like the back button in your internet browser. This is a sample, but you should be able to implement this code into your worksheets. To do this, just copy the code from the worksheet and module into your own worksheets, and then assign the macro to a button in your toolbar.

The idea is this: any time a new sheet is activated within a workbook, a subroutine records the previous worksheet name and the new worksheet name into respecive variables. (wkshtBack and wkshtCurrent). This is done by adding code to the worksheet object in the VB Editor (tools>macro>VBEditor), under the Workbook_SheetActivate event. Once the current and previous sheets are known, the macro simply tells excel to move between the two. The code is shown below:

Workbook object:
Private Sub Workbook_Open()

wkshtCurrent = ActiveSheet.Name
wkshtBack = ActiveSheet.Name

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

wkshtBack = wkshtCurrent
wkshtCurrent = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
End Sub


Global wkshtCurrent As String, wkshtVar As String, wkshtBack As String

Sub backmacro()
wkshtVar = wkshtCurrent
wkshtBack = wkshtVar
End Sub

-ExcelBack Macro-


Anonymous Anonymous said...

Thanks Kyle,
I found this Macro to be very helpfull

July 13, 2008 7:40 PM  

Post a Comment

<< Home