VBA Macro Coding

Question

I've been trying to make a Macro, but I'm having trouble figuring it out. Here's what it should do.

Macro function: When the macro is used it should switch to either a sheet called "Products" only if the current tab is NOT the "Products" tab, and if the current tab is the "Products" tab is should go to the previously visited tab.

Use: Let's say I'm on sheet index 3 and use the macro--it should activate the "Products" tab, and if I press it again it should return me to sheet index 3.

I've been trying to use ActiveSheet.Index and Sheets("Products").Index in some way, but I think I need to use something beyond my current knowledge of Visual Basics. I haven't used the Public function when declaring global variables and passing information between stuff much either.

Can someone point me in the right direction or tell me what I should use/look into? Is this even possible in VBA?

Answer 1

What you want is a global variable. Make a module and put in a global variable like such:

  Global GblPreviousSheetName As String

Then, in ALL of your sheets, put the following code:

 Private Sub Worksheet_Deactivate()

 GblPreviousSheetName = Me.Name

 End Sub

This will capture whenever a user or code changes sheets and set the Global variable to that sheet name.

Then, in your procedure, do this:

 Public Sub Test()

 If GblPreviousSheetName = "" Then
      GblPreviousSheetName = "Sheet1" 'Put default sheet here (for first time workbook opens)
 End If

 'Run whatever code you want.

      ActiveWorkbook.Sheets(GblPreviousSheetName).Activate

 End Sub

Note that if the user changes sheets before pressing your button, it will log this too. If you only wish to log the changes that are made by your code, then instead of putting in the "Worksheet_Deactivate" code, then just set the global variable in your code.