Return to Original Sheet VBA

[Rudy]

I have a workbook that has a series of sheets. Each sheet has a button that when pressed, moves the user to the last sheet and opens a User Form. Upon pressing the "Close" button the user form unloads and the user is stuck on the last sheet.

I am trying to figure out I can gather the sheet name and then upon closeing the User Form also move back to the original sheet.

I currently have the following:

Sub ActivateSumForm()
    Dim strSheetName As String
    strSheetName = ActiveSheet.Name
    
    Sheets("Sum").Select
    frmSumForm.Show
End Sub

Upon pressing the close button the code looks like:

Sub cmdClose_Click()
'Close form
        
    Application.DisplayAlerts = False
    Unload Me
    Sheets(strSheetName).Select
    Application.DisplayAlerts = True
    
End Sub

Everytime I press the close button I get an error.

Anyone have any tips or ideas for me to try?

Thanks in advance

 

[VoG]

You need to declare strSheetName outside of a sub like this:

Public strSheetName As String

Sub ActivateSumForm()
    
    strSheetName = ActiveSheet.Name
    
    Sheets("Sum").Select
    frmSumForm.Show
End Sub

Sub cmdClose_Click()
'Close form
        
    Application.DisplayAlerts = False
    Unload Me
    Sheets(strSheetName).Select
    Application.DisplayAlerts = True
    
End Sub

 

[Rudy]

Great!! Thanks that worked just like I needed it too.