CODEWITHSUNDEEP
excelvba
athoch71
athoch71

Reputation: 3

Creating multiple Macro buttons to show/hide specific worksheets

New here and I just started to teach myself coding. I have a workbook that has roughly 14 tabs/worksheets for employees to enter their hours worked per day. On a "Summary" tab and want to create a macro button for each employee to click on to view his/her tab. These employee tabs are hidden and all I want the action to do is unhide and then hide when the employee clicks their button.

Unfortunately, I receive an Ambiguous Error message and I created a module per employee. I assume I need to somehow "stack" code, but again am totally new to coding. Below is a sample of my code

Private Sub ShowHideWorksheets()
  Sheets("EMPLOYEE 1").Visible = Not Sheets("EMPLOYEE 1").Visible
End Sub

Upvotes: 0

Views: 2466

Answers (4)

Amorpheuses
Amorpheuses

Reputation: 1423

If you change Private to Public it should work. I'm presuming you're just creating macros at this point to get base functionality to work. You can hide (as the code you've posted) and unhide like this:

' This first macro actually just makes the worksheet visible and then
' invisible each time you execute it - so I'm not sure if
' that's what you're after
Public Sub ShowHideWorksheets()
   Sheets("EMPLOYEE 1").Visible = Not Sheets("EMPLOYEE 1").Visible
End Sub

' If it's invisible you can do this.
Public Sub ShowWorksheets()
   Sheets("EMPLOYEE 1").Visible = True
End Sub

' Basically that should give you an idea of how to proceed.

Upvotes: 0

yoyoyoyo123
yoyoyoyo123

Reputation: 2472

you need to correctly put it behind a button. When you insert the button into the page, right click it and assign macro. The code would look like

Sub Button1_Click()
    Sheets("EMPLOYEE 1").Visible = Not Sheets("EMPLOYEE 1").Visible
End Sub

Upvotes: 1

Doug Coats
Doug Coats

Reputation: 7117

This is also an acceptable approach? Prolly long winded though

Private Sub CommandButton1_Click()
    Dim sheet As Worksheet
    For Each sheet In ActiveWorkbook.Sheets
        If sheet.Name <> CommandButton1.Caption Then
            sheet.Visible = False
        End If

        If sheet.Name = CommandButton1.Caption Then
            sheet.Visible = True
        End If
    Next sheet
End Sub

However I like this better due to the fact you only need one button

Private Sub CommandButton1_Click()
    Dim sheet As Worksheet
    For Each sheet In ActiveWorkbook.Sheets
        If sheet.Name <> Environ("USERNAME") Then
            sheet.Visible = False
        End If

        If sheet.Name = Environ("USERNAME") Then
            sheet.Visible = True
        End If
    Next sheet
End Sub

Upvotes: 0

Rik Sportel
Rik Sportel

Reputation: 2679

Basically you wish to toggle visibility for a worksheet. Assuming that you know which Sheet is going to be triggered, it is something like this:

Public Sub TriggerSheetVisibility(worksheetname as string)

Dim ws as WorkSheet

On Error Resume Next 'To avoid subscript out of range error if a worksheetname is passed that doesn't exit
    Set ws = Worksheets(worksheetname)
On Error Goto 0

If Not ws Is Nothing Then 'Only when the worksheet exists, we can execute the rest of this sub:
    If ws.Visible = True then
        ws.Visible = False
    Else
        ws.Visible = True
    End If
End If

End Sub

Also see https://msdn.microsoft.com/en-us/library/office/ff197786.aspx

Upvotes: 0

Related Questions