| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Selection to fire Macro
Hi - Is it possible to have a dropdown box (listbox/combobox?) of 13 text choices, with each choice assigned to a macro? Ideally I'd like to run one of 13 macros depending upon what the user selects from this dropdown box. My present method is using 13 checkboxes with a different macro assigned to each - the user ticks it, and my macro fires. This though is occupies too much screen area. Any help would be appreciated. Regards Kaye |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ListBox Selection to fire Macro
Kaye,
You can use data validation in the cell, with a list of your 13 choices. Then in that sheet's code module insert a Worksheet_Change Event module. Assuming your data validated cell is in A1 and your choices are "a", "b" and "c" you could use code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select End If End Sub hth, Doug "Kaye" wrote in message ... Hi - Is it possible to have a dropdown box (listbox/combobox?) of 13 text choices, with each choice assigned to a macro? Ideally I'd like to run one of 13 macros depending upon what the user selects from this dropdown box. My present method is using 13 checkboxes with a different macro assigned to each - the user ticks it, and my macro fires. This though is occupies too much screen area. Any help would be appreciated. Regards Kaye |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ListBox Selection to fire Macro
Thanks for that Doug, that's what I was after!
But - another small problem has come to light .... I have TWO cells that carry Data Validation list boxes - A1, and also at A2, just beneath it. It appears I can't have TWO "Worksheet_Change" macros on the same sheet, even though my "Target, Range" is a different cell, that being A2 rather than A1. Is there any way I can have TWO "Worksheet_Change" macros on the same sheet? Kaye On Mon, 19 Feb 2007 19:10:50 -0800, "Doug Glancy" wrote: Kaye, You can use data validation in the cell, with a list of your 13 choices. Then in that sheet's code module insert a Worksheet_Change Event module. Assuming your data validated cell is in A1 and your choices are "a", "b" and "c" you could use code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select End If End Sub hth, Doug "Kaye" wrote in message .. . Hi - Is it possible to have a dropdown box (listbox/combobox?) of 13 text choices, with each choice assigned to a macro? Ideally I'd like to run one of 13 macros depending upon what the user selects from this dropdown box. My present method is using 13 checkboxes with a different macro assigned to each - the user ticks it, and my macro fires. This though is occupies too much screen area. Any help would be appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ListBox Selection to fire Macro
Kaye,
You can't have two Worksheet_Change events, but in your single event you can test for each cell separately. Duplicate the code for A2 so that it looks something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Range("A2").Value Case "1" Call Macro1 Case "2" Call Macro2 Case "3" Call Macro3 End Select End If End Sub The above assumes that the validation for A2 is different. But if it's the same, just modify it back to the "A" "B" and "C" values. hth, Doug "Kaye" wrote in message ... Thanks for that Doug, that's what I was after! But - another small problem has come to light .... I have TWO cells that carry Data Validation list boxes - A1, and also at A2, just beneath it. It appears I can't have TWO "Worksheet_Change" macros on the same sheet, even though my "Target, Range" is a different cell, that being A2 rather than A1. Is there any way I can have TWO "Worksheet_Change" macros on the same sheet? Kaye On Mon, 19 Feb 2007 19:10:50 -0800, "Doug Glancy" wrote: Kaye, You can use data validation in the cell, with a list of your 13 choices. Then in that sheet's code module insert a Worksheet_Change Event module. Assuming your data validated cell is in A1 and your choices are "a", "b" and "c" you could use code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select End If End Sub hth, Doug "Kaye" wrote in message . .. Hi - Is it possible to have a dropdown box (listbox/combobox?) of 13 text choices, with each choice assigned to a macro? Ideally I'd like to run one of 13 macros depending upon what the user selects from this dropdown box. My present method is using 13 checkboxes with a different macro assigned to each - the user ticks it, and my macro fires. This though is occupies too much screen area. Any help would be appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ListBox Selection to fire Macro
Thanks Doug, but I'm having a problem.
Yes, you are correct in your assumption that the validation for A2 is different. When I run this new code, from A1 or A2, this is returned: "Compile Error: Block IF without ENDIF" The last line of the code, END SUB, is highlighted blue, and the first line, "Private sub Worksheet_Change etc is highlighted yellow. I've transposed your code correctly. Any clues? Regards, Kaye On Tue, 20 Feb 2007 16:39:27 -0800, "Doug Glancy" wrote: Kaye, You can't have two Worksheet_Change events, but in your single event you can test for each cell separately. Duplicate the code for A2 so that it looks something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Range("A2").Value Case "1" Call Macro1 Case "2" Call Macro2 Case "3" Call Macro3 End Select End If End Sub The above assumes that the validation for A2 is different. But if it's the same, just modify it back to the "A" "B" and "C" values. hth, Doug "Kaye" wrote in message .. . Thanks for that Doug, that's what I was after! But - another small problem has come to light .... I have TWO cells that carry Data Validation list boxes - A1, and also at A2, just beneath it. It appears I can't have TWO "Worksheet_Change" macros on the same sheet, even though my "Target, Range" is a different cell, that being A2 rather than A1. Is there any way I can have TWO "Worksheet_Change" macros on the same sheet? Kaye On Mon, 19 Feb 2007 19:10:50 -0800, "Doug Glancy" wrote: Kaye, You can use data validation in the cell, with a list of your 13 choices. Then in that sheet's code module insert a Worksheet_Change Event module. Assuming your data validated cell is in A1 and your choices are "a", "b" and "c" you could use code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select End If End Sub hth, Doug "Kaye" wrote in message ... Hi - Is it possible to have a dropdown box (listbox/combobox?) of 13 text choices, with each choice assigned to a macro? Ideally I'd like to run one of 13 macros depending upon what the user selects from this dropdown box. My present method is using 13 checkboxes with a different macro assigned to each - the user ticks it, and my macro fires. This though is occupies too much screen area. Any help would be appreciated. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ListBox Selection to fire Macro
Kaye,
Woops. Here you go: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select End If If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Range("A2").Value Case "1" Call Macro1 Case "2" Call Macro2 Case "3" Call Macro3 End Select End If End Sub hth, Doug "Kaye" wrote in message ... Thanks Doug, but I'm having a problem. Yes, you are correct in your assumption that the validation for A2 is different. When I run this new code, from A1 or A2, this is returned: "Compile Error: Block IF without ENDIF" The last line of the code, END SUB, is highlighted blue, and the first line, "Private sub Worksheet_Change etc is highlighted yellow. I've transposed your code correctly. Any clues? Regards, Kaye On Tue, 20 Feb 2007 16:39:27 -0800, "Doug Glancy" wrote: Kaye, You can't have two Worksheet_Change events, but in your single event you can test for each cell separately. Duplicate the code for A2 so that it looks something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Range("A2").Value Case "1" Call Macro1 Case "2" Call Macro2 Case "3" Call Macro3 End Select End If End Sub The above assumes that the validation for A2 is different. But if it's the same, just modify it back to the "A" "B" and "C" values. hth, Doug "Kaye" wrote in message . .. Thanks for that Doug, that's what I was after! But - another small problem has come to light .... I have TWO cells that carry Data Validation list boxes - A1, and also at A2, just beneath it. It appears I can't have TWO "Worksheet_Change" macros on the same sheet, even though my "Target, Range" is a different cell, that being A2 rather than A1. Is there any way I can have TWO "Worksheet_Change" macros on the same sheet? Kaye On Mon, 19 Feb 2007 19:10:50 -0800, "Doug Glancy" wrote: Kaye, You can use data validation in the cell, with a list of your 13 choices. Then in that sheet's code module insert a Worksheet_Change Event module. Assuming your data validated cell is in A1 and your choices are "a", "b" and "c" you could use code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select End If End Sub hth, Doug "Kaye" wrote in message m... Hi - Is it possible to have a dropdown box (listbox/combobox?) of 13 text choices, with each choice assigned to a macro? Ideally I'd like to run one of 13 macros depending upon what the user selects from this dropdown box. My present method is using 13 checkboxes with a different macro assigned to each - the user ticks it, and my macro fires. This though is occupies too much screen area. Any help would be appreciated. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ListBox Selection to fire Macro
Thanks so much Doug, it's working great now!
Kaye On Tue, 20 Feb 2007 19:30:54 -0800, "Doug Glancy" wrote: Kaye, Woops. Here you go: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select End If If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Range("A2").Value Case "1" Call Macro1 Case "2" Call Macro2 Case "3" Call Macro3 End Select End If End Sub hth, Doug "Kaye" wrote in message .. . Thanks Doug, but I'm having a problem. Yes, you are correct in your assumption that the validation for A2 is different. When I run this new code, from A1 or A2, this is returned: "Compile Error: Block IF without ENDIF" The last line of the code, END SUB, is highlighted blue, and the first line, "Private sub Worksheet_Change etc is highlighted yellow. I've transposed your code correctly. Any clues? Regards, Kaye On Tue, 20 Feb 2007 16:39:27 -0800, "Doug Glancy" wrote: Kaye, You can't have two Worksheet_Change events, but in your single event you can test for each cell separately. Duplicate the code for A2 so that it looks something like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Range("A2").Value Case "1" Call Macro1 Case "2" Call Macro2 Case "3" Call Macro3 End Select End If End Sub The above assumes that the validation for A2 is different. But if it's the same, just modify it back to the "A" "B" and "C" values. hth, Doug "Kaye" wrote in message ... Thanks for that Doug, that's what I was after! But - another small problem has come to light .... I have TWO cells that carry Data Validation list boxes - A1, and also at A2, just beneath it. It appears I can't have TWO "Worksheet_Change" macros on the same sheet, even though my "Target, Range" is a different cell, that being A2 rather than A1. Is there any way I can have TWO "Worksheet_Change" macros on the same sheet? Kaye On Mon, 19 Feb 2007 19:10:50 -0800, "Doug Glancy" wrote: Kaye, You can use data validation in the cell, with a list of your 13 choices. Then in that sheet's code module insert a Worksheet_Change Event module. Assuming your data validated cell is in A1 and your choices are "a", "b" and "c" you could use code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select End If End Sub hth, Doug "Kaye" wrote in message om... Hi - Is it possible to have a dropdown box (listbox/combobox?) of 13 text choices, with each choice assigned to a macro? Ideally I'd like to run one of 13 macros depending upon what the user selects from this dropdown box. My present method is using 13 checkboxes with a different macro assigned to each - the user ticks it, and my macro fires. This though is occupies too much screen area. Any help would be appreciated. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ListBox Selection to fire Macro
Hi
I was looking for help with macros and assigning them to data validation and saw your posting reply. What im trying to do is simialr but i have 3 choices in the drop down validation, CHOICE A (macro 1) CHOICE B (macro 2) CHOICE C (macro 3) but i need to do this to 9 seperate towns. These are on seperate worksheets marked as below. the macro that drives the print is the same one but as you change the worksheet it selects that sheets info. TOWN 1 TOWN 2 TOWN 3 ETC I basically select the choice from the data validation dropdown and I want it to then print using the macro i've set up on the relevant town worksheet Is this possible to do. Any help you can offer would be appreciated! -- MDL2005 "Doug Glancy" wrote: Kaye, You can use data validation in the cell, with a list of your 13 choices. Then in that sheet's code module insert a Worksheet_Change Event module. Assuming your data validated cell is in A1 and your choices are "a", "b" and "c" you could use code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then Select Case Range("A1").Value Case "a" Call MacroA Case "b" Call MacroB Case "c" Call MacroC End Select End If End Sub hth, Doug "Kaye" wrote in message ... Hi - Is it possible to have a dropdown box (listbox/combobox?) of 13 text choices, with each choice assigned to a macro? Ideally I'd like to run one of 13 macros depending upon what the user selects from this dropdown box. My present method is using 13 checkboxes with a different macro assigned to each - the user ticks it, and my macro fires. This though is occupies too much screen area. Any help would be appreciated. Regards Kaye |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fire Macro from Cell Change | Excel Worksheet Functions | |||
| Essbase Causing Selection Change Event to Fire | Excel Programming | |||
| fire macro on AutoFilter change | Excel Programming | |||
| how to get onkey macro to fire while another macro is running | Excel Programming | |||
| Run a macro when new selection in listbox | Excel Programming | |||
Linear Mode
