If Range().value = With a list

[dogfeeder]

Hello

I am currently trying to get a spreadsheet to perform a macro if the cell has the correct contents. I think i have managed to get the code correct, but im not sure how to get the code to execute. I originally had the code assigned to a button and it worked fine, but now I have taken the button away and replaced it with an if function im not sure how to get it to work.

the cell K1 has a list in it also, users pick a selection from the list and then the code displays the correct rows.

Sub LadiesShirts()
'
' LadiesShirts Macro
If Range("K1").Value = "Ladies" Then
    Rows("5:6").Select
    Selection.EntireRow.Hidden = True
    Rows("27:145").Select
    Selection.EntireRow.Hidden = True
    Rows("7:26").Select
    Selection.EntireRow.Hidden = False
End If
End Sub

 

[Sektor]

Sub LadiesShirts()

    ' LadiesShirts Macro
    
    Select Case Range("K1").Value
        
        Case "Ladies"
            Rows("5:6").Select
            Selection.EntireRow.Hidden = True
            Rows("27:145").Select
            Selection.EntireRow.Hidden = True
            Rows("7:26").Select
            Selection.EntireRow.Hidden = False
        
        Case "Men"
            ' Your code
        
        Case "Old men"
            ' Your code
            
    End Select

End Sub

 

[dogfeeder]

Sub LadiesShirts()
        Case "Men"
            ' Your code
        
        Case "Old men"
            ' Your code

Lol, Thankyou for your help.

 

[repairman615]

Hello,

This will need an "event" to trigger the macro.

Try this:

Right click the sheet tab.
Select veiw code.

paste in the following:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br><SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("K1"))<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <br>        <SPAN style="color:#007F00">'''if cell K1 value = nothing then exit sub</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target.Value = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>                <br>            <SPAN style="color:#00007F">If</SPAN> Target.Value = "Ladies" <SPAN style="color:#00007F">Then</SPAN><br>                Rows("5:6").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>                Rows("27:145").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>                Rows("7:26").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

now when cell K1 is changed this should trigger the macro to fire and cycle the logic.

Does that do what you need?

Jeff

 

[dogfeeder]

The code works, just the same as my other code did, but how to I get it to execute after someone has selected the item from the drop down box. At the moment i have to go Macros> Run Ladies Shirts.
EDIT: (was reply to sektor's post)

 

[Sektor]

repairman615 has answered your question. You need to respond to Worksheet_Change event. The code looks for cells changes. And if it's K1 cell that has been changed, then the event is triggered applying your code.

 

[dogfeeder]

Hello,

This will need an "event" to trigger the macro.

Try this:

Right click the sheet tab.
Select veiw code.

paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Changed As Range

Set Changed = Intersect(Target, Range("K1"))

If Not Changed Is Nothing Then

'''if cell K1 value = nothing then exit sub
If Target.Value = "" Then Exit Sub

If Target.Value = "Ladies" Then
Rows("5:6").EntireRow.Hidden = True
Rows("27:145").EntireRow.Hidden = True
Rows("7:26").EntireRow.Hidden = False
End If
End If

End Sub

now when cell K1 is changed this should trigger the macro to fire and cycle the logic.

Does that do what you need?

Jeff

Ahhhh Legend, got it to work. Just need to add the other rows and a few more ifs to it now.

 

[repairman615]

Sektor has showed the Select Case statement.... that may be of good use to help streamline a bit.

 

[Sektor]

One important thing was missed (in red):

Private Sub Worksheet_Change(ByVal Target As Range)

    [B][COLOR="Red"]Application.EnableEvents = False[/COLOR][/B]

    If Not Intersect(Target, Range("K1")) Is Nothing Then
        
        ' If cell K1 is empty, then Exit Sub
        If IsEmpty(Target) Then Exit Sub

        Select Case Target
        
            Case "Ladies"
                Rows("5:6").EntireRow.Hidden = True
                Rows("27:145").EntireRow.Hidden = True
                Rows("7:26").EntireRow.Hidden = False
            
            Case "Big women"
                ' Your code
            
            Case "Old women"
                ' Your code
            
        End If
    
    End If
    
    [COLOR="red"][B]Application.EnableEvents = True[/B][/COLOR]
    
End Sub