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?
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.