Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro based on cell value
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro based on cell value
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro based on cell value
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro based on cell value
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. -- If you can measure it, you can improve it! "john" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro based on cell value
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro based on cell value
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro based on cell value
Thanks, that's great. I'll be able to use that in a few applications!
:) Joe. -- If you can measure it, you can improve it! "BSc Chem Eng Rick" wrote: 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running macro based on cell value
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. -- If you can measure it, you can improve it! "john" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running a macro based on the time | Excel Discussion (Misc queries) | |||
Running a macro based on cell value that is updated by formula | Excel Programming | |||
Running a macro based on combo box value | Excel Programming | |||
Running a macro based on a value | Excel Programming | |||
Running a macro based on a cells value | Excel Programming |