Using a dropdown for 100 + people would create a very long list to select from.

As an alternative idea, have you thought about using another inputbox to
prompt staff for their staff number? Using staff number as opposed to name
would ensure consistent input by user. You would then test staff number &
password inputs against values stored in hidden sheet say.

In your master workbook you would enter data like this:

- Col A enter staff numbers.
- Col B password
- Col C the full path & file name you want to open.


code would look something like this but may need work to suit your need:

Sub FindStaff()
Dim FoundCell As Range
Dim ws1 As Worksheet
Dim Search As Variant
Dim Passwrd As Variant
Dim MyFile As String
Dim MyTitle As String
Dim OpenWB As Workbook

Set ws1 = Worksheets("Sheet1") '<< change as required

MyTitle = "Open My WorkBook"

startsearch:
Search = Application.InputBox(prompt:="Enter Staff Number",
Title:=MyTitle, Type:=2)


If Search = False Then Exit Sub

'search for staff number
Set FoundCell = ws1.Columns("A").Find _
(Search, LookIn:=xlValues, _
LookAt:=xlWhole)

If FoundCell Is Nothing = False Then

i = 1
enterpassword:
Passwrd = Application.InputBox(prompt:="Enter Password" & Chr(10) &
"Attempt " & i, Title:=MyTitle2, Type:=2)

If Passwrd = False Then Exit Sub

'check password value in Col B
If FoundCell.Offset(0, 1).Value = CStr(Passwrd) Then

'get file name & path from Col C
MyFile = FoundCell.Offset(0, 2).Value

On Error GoTo myerror
Set OpenWB = Workbooks.Open(MyFile, Password:=Passwrd)

'do stuff here

Else

msg = MsgBox("Password Not Valid", vbInformation, MyTitle)

i = i + 1

If i 3 Then

Exit Sub

Else

GoTo enterpassword

End If

End If

Else

msg = MsgBox("Value " & Search & " Not Found", vbInformation, MyTitle)

GoTo startsearch

End If

myerror:
If Err 0 Then

MsgBox (Error(Err))
Err.Clear

End If

End Sub

of course this is not very secure but should be ok for most users.

Just an idea €“ hope helpful.

--
jb


"Monomeeth" wrote:

Hello

I have a workbook which acts as a menu by displaying multiple macro buttons
for selection by users. Basically, the user clicks on the button with their
name on it and then a macro runs asking the user to enter a password and, if
the password is correct, proceeds to open up a unique workbook for use only
by that user. The same macro also closes down the "menu" workbook so that it
is available to other users.

The problem I have is that we now have to set this up for about a hundred
people, so it will no longer be practical to keep adding macro buttons for
each user. So what I want to do is present the user with a drop-down menu
from which they can select their name to open up that user's workbook.
Currently I have one macro per user behind the scenes, so this means I need a
way for the selection (cell value?) to activate that user's macro.

So, how do I do this? I'm a bit lost as to how to proceed, although I'm
guessing some sort of "If Then" statement is what I need.

Any suggestions? Your help would be most appreciated!

Thanks,

Joe.

--
If you can measure it, you can improve it!