View/Hide sheets based on sheets' name with InputBox

[Daniel2022]

Hi there. I'm a fairly new in VBA but things used to go well until this moment.
My co-workers use Excel that consist 50 different sheets while this amount rises every month. I managed to create module that creates new sheet with desirable template but I also want to create button that opens and hides required sheet. My idea is to insert InputBox where a person puts sheet' name.
However, I right now I faced an issue that I cannot create similar macros to view and hide sheet in accordance to its name.
I also attach the screenshot how "create" button looks and what it shows and code:

Sub NewSheet()
    Dim s As String
    s = InputBox("Please write month and year e.g September 2021")
    If s = "" Then Exit Sub
    
    Sheets("Macro").Copy , Sheets(Sheets.Count)
    With ActiveSheet
        .Name = s
    End With
    
End Sub

Any help/ ideas how to do it will be valuable.

Thank you,
Kind Regards,
Daniel

 

[dmt32]

Hi,
you should be able to do what you want in same code

Sub NewSheet()
    Dim s As Variant
    Do
        s = InputBox("Please write month And year e.g September 2021", "Enter Sheet Name")
        If StrPtr(s) = 0 Then Exit Sub
    Loop Until IsDate(s)
   
    s = Format(DateValue(s), "mmmm yyyy")
   
    If Not Evaluate("ISREF('" & s & "'!A1)") Then
        'create new sheet
        Worksheets("Macro").Copy , Sheets(Sheets.Count)
        With ActiveSheet
            .Name = s
        End With
       
    Else
        'sheet name exists
        With Worksheets(s)
            .Visible = xlSheetVisible
            .Activate
        End With
       
    End If
   
End Sub

Code checks to see if sheet name exists & will either create new sheet or unhide & activate it.

Based on your post, I have assumed all sheets are named by month / year? Updated code should allow any valid date entry like Feb 2023 to be entered & will change entry to required format

Hope Helpful

Dave

 

[mumps]

Try:

Sub View_HideSheet()
    Dim s As String
    s = InputBox("Please enter the name of the sheet to view or hide.")
    If s = "" Then Exit Sub
    If MsgBox("Click 'Yes' to view the sheet or 'No' to hide the sheet.", vbYesNo) = vbYes Then
        Sheets(s).Visible = True
    Else
        Sheets(s).Visible = False
    End If
End Sub

 

[Daniel2022]

Try:

Sub View_HideSheet()
    Dim s As String
    s = InputBox("Please enter the name of the sheet to view or hide.")
    If s = "" Then Exit Sub
    If MsgBox("Click 'Yes' to view the sheet or 'No' to hide the sheet.", vbYesNo) = vbYes Then
        Sheets(s).Visible = True
    Else
        Sheets(s).Visible = False
    End If
End Sub

Thanks a lot!
It does work. I'll try to split it by 2 buttons- view/hide.

 

[mumps]

You are very welcome.

 

[RobP]

Hi Daniel,

one idea might be to draw an activeX box on your (main?) sheet ie. one that is not getting hidden. Then use the button as a "Toggle" ie. if a sheet is hidden, it will become visible, and vice versa.

Try the below code (which is inserted into the actual button itself - right click on the button you've drawn when in "Design Mode" on the developer tab, and select "view code".

It will check if the sheet exists first, then check its status, and toggle it on or off depending on its status.

Cheers
Rob

Private Sub CommandButton1_Click()

Dim s As String
s = InputBox("Please write month and year e.g September 2021")
    If s = "" Then Exit Sub
    
        If SheetExists(s) Then
            If (Sheets(s).Visible = False) Then
                Sheets(s).Visible = True
            Else
                Sheets(s).Visible = False
            End If
        Else
            MsgBox ("Worksheet Does not Exist")
        End If
    
End Sub

Private Function SheetExists(Tabname) As Boolean
'   Returns logical TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(Tabname)
    If Err = 0 Then SheetExists = True _
        Else SheetExists = False
End Function