| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro with List Box Selection
Hi,
I have a list box with 4 items. I have two macros - MacA and MacB. If item Apple is selected in the box, I want to run MacA, if any of the other 3 items are selected I want to run MacB. I could use a nudge (or more) to get going in the right direction Thank you Karin R |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro with List Box Selection
I should add that it is a data validation list in merged cells D5:E5.
"Karin" wrote: Hi, I have a list box with 4 items. I have two macros - MacA and MacB. If item Apple is selected in the box, I want to run MacA, if any of the other 3 items are selected I want to run MacB. I could use a nudge (or more) to get going in the right direction Thank you Karin R |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro with List Box Selection
I'm so excited I figured it out myself by searching more on this board!
here's what worked. The macros are in Module 1 and this code is on the worksheet. I can't seem to get the Case to be an "or" (since 3 call the same macro) Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D5"), Target) Is Nothing Then Select Case Target.Value 'unlock & unshade for Audit Case "Apple": Call UnlockUnshade ' Lock & shade for Review Case "Beets": Call LockShade ' Lock & shade for Compilation Case "Carrots": Call LockShade ' Lock & shade for Other Case "Other": Call LockShade End Select End If End Sub "Karin" wrote: I should add that it is a data validation list in merged cells D5:E5. "Karin" wrote: Hi, I have a list box with 4 items. I have two macros - MacA and MacB. If item Apple is selected in the box, I want to run MacA, if any of the other 3 items are selected I want to run MacB. I could use a nudge (or more) to get going in the right direction Thank you Karin R |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro with List Box Selection
Great...For OR separate with commas as below ( "Beets", "Carrots", "Other").
Try and feedback Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D5"), Target) Is Nothing Then Select Case Target.Value 'unlock & unshade for Audit Case "Apple" Call UnlockUnshade 'Lock & shade for Review Case "Beets", "Carrots", "Other" Call LockShade 'Lock & shade for Review End Select End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Karin" wrote: I'm so excited I figured it out myself by searching more on this board! here's what worked. The macros are in Module 1 and this code is on the worksheet. I can't seem to get the Case to be an "or" (since 3 call the same macro) Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D5"), Target) Is Nothing Then Select Case Target.Value 'unlock & unshade for Audit Case "Apple": Call UnlockUnshade ' Lock & shade for Review Case "Beets": Call LockShade ' Lock & shade for Compilation Case "Carrots": Call LockShade ' Lock & shade for Other Case "Other": Call LockShade End Select End If End Sub "Karin" wrote: I should add that it is a data validation list in merged cells D5:E5. "Karin" wrote: Hi, I have a list box with 4 items. I have two macros - MacA and MacB. If item Apple is selected in the box, I want to run MacA, if any of the other 3 items are selected I want to run MacB. I could use a nudge (or more) to get going in the right direction Thank you Karin R |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro with List Box Selection
Looks good!
Be aware that those string comparisons are case-sensitive in code. And you can actually have more than one criteria in your case statement: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D5"), Target) Is Nothing Then Select Case LCase(Target.Value) 'unlock & unshade for Audit Case LCase("Apple") Call UnLockUnShade 'Lock & shade for Review Case LCase("Beets"), LCase("carrots"), LCase("other") Call LockShade End Select End If End Sub If I'm good (consistent) typist, I wouldn't need lcase("carrots"). I could just use "carrots". Or I could drop all the lcase() stuff and add: Option Compare Text at the top of the module (outside any routine) Karin wrote: I'm so excited I figured it out myself by searching more on this board! here's what worked. The macros are in Module 1 and this code is on the worksheet. I can't seem to get the Case to be an "or" (since 3 call the same macro) Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D5"), Target) Is Nothing Then Select Case Target.Value 'unlock & unshade for Audit Case "Apple": Call UnlockUnshade ' Lock & shade for Review Case "Beets": Call LockShade ' Lock & shade for Compilation Case "Carrots": Call LockShade ' Lock & shade for Other Case "Other": Call LockShade End Select End If End Sub "Karin" wrote: I should add that it is a data validation list in merged cells D5:E5. "Karin" wrote: Hi, I have a list box with 4 items. I have two macros - MacA and MacB. If item Apple is selected in the box, I want to run MacA, if any of the other 3 items are selected I want to run MacB. I could use a nudge (or more) to get going in the right direction Thank you Karin R -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro with List Box Selection
Worked great - thank you
"Jacob Skaria" wrote: Great...For OR separate with commas as below ( "Beets", "Carrots", "Other"). Try and feedback Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D5"), Target) Is Nothing Then Select Case Target.Value 'unlock & unshade for Audit Case "Apple" Call UnlockUnshade 'Lock & shade for Review Case "Beets", "Carrots", "Other" Call LockShade 'Lock & shade for Review End Select End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Karin" wrote: I'm so excited I figured it out myself by searching more on this board! here's what worked. The macros are in Module 1 and this code is on the worksheet. I can't seem to get the Case to be an "or" (since 3 call the same macro) Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D5"), Target) Is Nothing Then Select Case Target.Value 'unlock & unshade for Audit Case "Apple": Call UnlockUnshade ' Lock & shade for Review Case "Beets": Call LockShade ' Lock & shade for Compilation Case "Carrots": Call LockShade ' Lock & shade for Other Case "Other": Call LockShade End Select End If End Sub "Karin" wrote: I should add that it is a data validation list in merged cells D5:E5. "Karin" wrote: Hi, I have a list box with 4 items. I have two macros - MacA and MacB. If item Apple is selected in the box, I want to run MacA, if any of the other 3 items are selected I want to run MacB. I could use a nudge (or more) to get going in the right direction Thank you Karin R |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro with List Box Selection
Thank you - I really appreciate your help in trying to get me to be a better
programer :) I will incorporate that into my code. "Dave Peterson" wrote: Looks good! Be aware that those string comparisons are case-sensitive in code. And you can actually have more than one criteria in your case statement: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D5"), Target) Is Nothing Then Select Case LCase(Target.Value) 'unlock & unshade for Audit Case LCase("Apple") Call UnLockUnShade 'Lock & shade for Review Case LCase("Beets"), LCase("carrots"), LCase("other") Call LockShade End Select End If End Sub If I'm good (consistent) typist, I wouldn't need lcase("carrots"). I could just use "carrots". Or I could drop all the lcase() stuff and add: Option Compare Text at the top of the module (outside any routine) Karin wrote: I'm so excited I figured it out myself by searching more on this board! here's what worked. The macros are in Module 1 and this code is on the worksheet. I can't seem to get the Case to be an "or" (since 3 call the same macro) Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("D5"), Target) Is Nothing Then Select Case Target.Value 'unlock & unshade for Audit Case "Apple": Call UnlockUnshade ' Lock & shade for Review Case "Beets": Call LockShade ' Lock & shade for Compilation Case "Carrots": Call LockShade ' Lock & shade for Other Case "Other": Call LockShade End Select End If End Sub "Karin" wrote: I should add that it is a data validation list in merged cells D5:E5. "Karin" wrote: Hi, I have a list box with 4 items. I have two macros - MacA and MacB. If item Apple is selected in the box, I want to run MacA, if any of the other 3 items are selected I want to run MacB. I could use a nudge (or more) to get going in the right direction Thank you Karin R -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Limiting selection in a cell AND linking that selection to a list | Excel Discussion (Misc queries) | |||
| Starting a macro from a list selection | Excel Worksheet Functions | |||
| Macro Makes List Box Selection | Excel Discussion (Misc queries) | |||
| validation list--list depends on the selection of first list | New Users to Excel | |||
| limit cell list selection based on the selection of another list | Excel Worksheet Functions | |||
Linear Mode
