Macro to unhide a sheet based on selection from drop-down menu

[JacqH]

I have a drop-down menu on the sheet called "Start" in cell D7 where a user can choose his/her name. When the user selects his/her name from that menu, I want a hidden sheet to appear after he/she types the password (the sheet holds personal information of the user). The password is checked against the value in cell D10 on the "Start" sheet.
In the dropdwon list there are blank cells.

There will essentially be approx 40 choices in that drop-down menu, and subsequently, 40 hidden sheets to accompany those choices (each sheet is exactly named as the name in the dropdon list).

When the user saves the workbook and closes it, a macro is hiding the sheet again.

Untill now I have the following code, but I do not get it to work and I do not get the password sub included in it.

Thanks in advance!

The code I have is:
XXXXX1 is the name of the person out of the dropdown list


-------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
Dim ws As Worksheet
' Set Target Range
Set rng = Target.Parent.Range("D7")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Exit if the Data Validation cell is blank
If Target.Value = "" Then Exit Sub
' Action if Condition(s) are met

Sub ViewSheet2()
pword = Application.InputBox("Type Password", "PASSWORD REQUIRED")
'If pword = "PASSWORD" Then
If pword = Range("D10").Value Then
Sheets("XXXXX1").Visible = True
End If
End Sub



For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Start" Then ws.Visible = xlSheetHidden
Next ws
' Sheets(Target.Value).Visible = True
With Sheets(Target.Value)
.Visible = True
.Activate
End With
End Sub


-------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Jan Mensen").Visible = xlVeryHidden
End Sub

 

[JacqH]

Does someone know how to replace in sheets("XXXXX1") the XXXX1 with the name out of the dropdownbox?

Regards Jacques