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?

No guarantee here, but have you explored creating a variable for previous sheet, then set that variable on the line prior to moving to the new tab then when you click again it returns to where you were...and sets the variable again and so on... — DCX, Jun 19 '15 at 17:27
Do you have any existing code that switches sheets? You will need to add a variable at the global scope to store which `Worksheet` to revert back to. This post should get you started on declaring global variables: http://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba — Byron Wall, Jun 19 '15 at 17:34

score 1 · Accepted Answer · answered Jun 19 '15 at 17:40

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.

VBA Macro Coding

1 Answers1