VBA for multiple Worksheets

[hezcal11]

Hello, I have a workbook that allows 50 people to access their own page to input their monthly shift availability. The main page will have 50 boxes with names and a button next to each asking for a password to allow them to unhide their own page. The pages are numbered 1 to 50. Is there any way to do this without copying a macro 50 times changing the worksheet number for each. I was hoping there would be a 'lighter' way to achieve this.

No	NAME	CONTACT NO	PASSWORD
1			1 *

<COLGROUP><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><TBODY>
</TBODY>

* The macro button is here​

Thanks

Colin

 

[MickG]

You could perhaps do it with a "Change_Event" code
You would first need to uses a "Workbook_Event" to hide all sheets except "Master" (For input of password)
Using Sheet "Master" "D2" for input of "Password_to _Open", and "E2" for "Password_for Closing"
This coding means you only have to input any Password into "E2" for the reltive sheet to open.
NB:- You will perhaps also need to protect the code from other users. :- (VbWindow "Tools", VbProject Properties, Protection)

This is the first code that goes in the "ThisWorkbook" code module.

Option Explicit
Private Sub Workbook_Open()
Dim Sht As Worksheet
For Each Sht In Worksheets
    If Not Sht.Name = "Master" Then
        Sht.Visible = xlVeryHidden
    End If
Next Sht
End Sub

Then this is the code for "Worksheet_Change" event in sheet "Master.
You will need to change the "s" numbers for your passwords and add further sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
Application.EnableEvents = False
If Target = Range("D2") Then
Select Case Target.Value
Case "s2": Sheets("Sheet2").Visible = True
Case "s3": Sheets("Sheet3").Visible = True
Case "s4": Sheets("Sheet4").Visible = True
Case "s5": Sheets("Sheet5").Visible = True
End Select
End If
If Target = Range("E2") Then
Select Case Target.Value
Case "s2": Sheets("Sheet2").Visible = xlVeryHidden
Case "s3": Sheets("Sheet3").Visible = xlVeryHidden
Case "s4": Sheets("Sheet4").Visible = xlVeryHidden
Case "s5": Sheets("Sheet5").Visible = xlVeryHidden
End Select
End If
Target.Value = "####"
Application.EnableEvents = True
End If
End Sub

 

[spacebouncer]

Yo.

Adapt accordingly.

Dim unhidesheet as integer
unhidesheet = ActiveSheet.Shapes(Application.Caller).topleftcell.offset(0, -3).value

 

[hezcal11]

You could perhaps do it with a "Change_Event" code
You would first need to uses a "Workbook_Event" to hide all sheets except "Master" (For input of password)
Using Sheet "Master" "D2" for input of "Password_to _Open", and "E2" for "Password_for Closing"
This coding means you only have to input any Password into "E2" for the reltive sheet to open.
NB:- You will perhaps also need to protect the code from other users. :- (VbWindow "Tools", VbProject Properties, Protection)

This is the first code that goes in the "ThisWorkbook" code module.

Option Explicit
Private Sub Workbook_Open()
Dim Sht As Worksheet
For Each Sht In Worksheets
    If Not Sht.Name = "Master" Then
        Sht.Visible = xlVeryHidden
    End If
Next Sht
End Sub

Then this is the code for "Worksheet_Change" event in sheet "Master.
You will need to change the "s" numbers for your passwords and add further sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
Application.EnableEvents = False
If Target = Range("D2") Then
Select Case Target.Value
Case "s2": Sheets("Sheet2").Visible = True
Case "s3": Sheets("Sheet3").Visible = True
Case "s4": Sheets("Sheet4").Visible = True
Case "s5": Sheets("Sheet5").Visible = True
End Select
End If
If Target = Range("E2") Then
Select Case Target.Value
Case "s2": Sheets("Sheet2").Visible = xlVeryHidden
Case "s3": Sheets("Sheet3").Visible = xlVeryHidden
Case "s4": Sheets("Sheet4").Visible = xlVeryHidden
Case "s5": Sheets("Sheet5").Visible = xlVeryHidden
End Select
End If
Target.Value = "####"
Application.EnableEvents = True
End If
End Sub

Thanks for that. Will try it out

 

[hezcal11]

Thank you for a quick response.