Running macro based on cell value

[Monomeeth]

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.

 

[BSc Chem Eng Rick]

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

 

[john]

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.

 

[Monomeeth]

Hi John

Thanks for your reply. You're right, a 100 names on a drop-down menu would
be too many, so I went with your advice and was able to adapt your code to
work a treat!

Thanks so very much for your help - it is greatly appreciated!



Joe.

 

[Monomeeth]

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.

 

[BSc Chem Eng Rick]

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]

Thanks, that's great. I'll be able to use that in a few applications!



Joe.

 

[Monomeeth]

Hi John

I'm hoping you're going to see this.

I've started using this macro and doing as you suggested, and all seemed to
be okay when I tested it with a handful of users. However, now I've been
given a full listing of users and have started adding them in - but now I've
run into a problem I can't seem to troubleshoot!

So far I have added 63 users to my hidden worksheet, along with their
associated passwords and filepaths. I decided to do a random check to ensure
all was working okay, but found that the macro didn't seem to be working - so
decided to go through the entire list in my testing.

I found that the macro recognises 15 of the 63 users. I have checked and
rechecked (and again, and again, etc) the hidden worksheet and everything
seems okay. The users are definiteley listed, and there is no pattern between
those which seem to be identified and those which aren't.

After checking the macro code I decided to go back to the hidden worksheet
and delete all unused rows and columns, just in case Excel thought there was
data in any of them. This too had no impact.

I then decided to swap one "identified" record with that of an "unidentifed"
record (that is, the rows they appeared in) within the hidden worksheet. This
also had no impact. I then also tried changing the format of the cells, but
likewise no change to the end result.

In summary then, I have a macro (i.e. your macro) activated by a button on a
"menu" worksheet, and a hidden worksheet accessed by the macro, but for some
reason only 15 users are recognised as valid. There is no pattern (or
commonality) differentiating these users to the ones not identified by the
macro. So I'm at a total loss!

Any help would be absolutely fantastic as I am supposed to have this ready
to go live.

Thanks for your help.

Joe.