Home |
Search |
Today's Posts |
#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. |
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 |