Hide sheet and go back to previous sheet button-CodePudding

I want to add a button to every sheet on my workbook which hides the active sheet and go back to previous sheet. I've tried the following code but it only goes back to previous sheet. I am not familiar to VBA.

How am I supposed to modify the code so it can hide the active sheet before going back?

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set LstSht = Sh
End Sub


Public LstSht As Worksheet
Sub GoToLast()
    LstSht.Activate
End Sub

CodePudding user response：

The .Visible property of a WorkSheet controls if it is hidden or not. Here is the Microsoft documentation on how to use it: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.visible

CodePudding user response：

For those who follows this question; I solved my problem with making a little modification on my codes like this: 1- go to previous sheet, 2- hide previous sheet (Second step was hiding the active sheet before, now it's previous sheet).

You can try this:

in ThisWorkbook;

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
        Set LstSht = Sh
    End Sub

in Modules;

Public LstSht As Worksheet
Private Sub GoToLast()
        LstSht.Activate
End Sub

Private Sub HidePrvSht()
ActiveWorkbook.Worksheets(LstSht.Name).Visible = False
End Sub

Sub HideNGo()
Call GoToLast
Call HidePrvSht
End Sub

Special thanks to TehDrunkSailor :)