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

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

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

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

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

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

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