No Prob.

In brief, the worksheets_change event always returns "Target" as the cell or
range of cells that have been changed. So the first thing is of course to
make sure the cell you want e.g. $B$3 is the one that has changed by using
the Target.Addres = "$B$3" condition. Then I would use a series of single
line if statements to call the correct macro. This is a little hectic when
you have too many names but works very fast for me even at 500 names in the
list.

Below is the code that is imbedded in the Sheet NOT as a standalone module.
FredMacro, JoeMacro and PeteMacro are the macros corresponding to each name
located in a separate module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MacroName As String
If Target.Address = "$B$3" Then
MacroName = CStr(Target.Value)
If MacroName = "Fred" Then Call FredMacro
If MacroName = "Pete" Then Call PeteMacro
If MacroName = "Joe" Then Call JoeMacro
End If
End Sub

If this helps please click "Yes"
<<<<<<<<<<

"Monomeeth" wrote:

Hi,

Thanks for your reply. I decided to go with John's advice because 100 names
on a drop-down list was obviously too many for users to scroll through.

However, I am interested in your response for some other applications.

In your example where you used $B$3 to indicate the cell in which the
drop-down list is located, wouldn't I have to specify which macros are run by
what values within that cell?

For instance, let's say I have a user who selects "Fred Flintstone" from the
drop-down list, how do I get the Worksheet_Change event to know which macro
to run for that selection?

It's the end of the day here, so I'm obviously too tired as I must be
missing something very simple!

*scratching head*

Thanks for your help!

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


"BSc Chem Eng Rick" wrote:

Monomeeth,

Use the "Worksheet_Change" Event to run the specified macro as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
MyModule1
End If
End Sub

Note that "$B$3" above must be replaced by the cell in which your dropdown
list is located. Also this procedure must be located in the Code of the Sheet
which contains your list, NOT in a separate module.

If this helps please click "Yes"
<<<<<<<<<<


"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!