Macro with List Box Selection

  • Thread starter Thread starter Karin
  • Start date Start date
K

Karin

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
 
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
 
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
 
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)
 
Worked great - thank you

Jacob Skaria said:
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
Click to expand...
 
Thank you - I really appreciate your help in trying to get me to be a better
programer :)

I will incorporate that into my code.
 
Back
Top