macro executed by drop-down list. how?

[waldymar]

Hi,
I have a macro which output depence on the variable input. Input is represented by drop-down list. Before, I was choosing input from drop-down, then pressing the button my macro was running. Now, I'm thinking to if it's possible to exclude a spare operation from button pressing, just by clicking from drop-down. Any ideas?
My drop-down list is created through "Data->Validation->List box"

 

[Smitty]

So, if I clear a content in 1 cell the macro is running?

Yes, and the rng paramenter tells the code whether to take action or exit.

Basically, what does "Target" property stands for? I thought it's a cell I want to change and which will lead to the execution of my macro ... Or not?

Target is the cell that gets changed, regardless of whether or not it's in the range you defined. Any cell that gets changed is identified by VBA as the Target cell.

See if this helps you understand:

<font face=Calibri><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>    <SPAN style="color:#007F00">'   Tell me which cell has been changed</SPAN><br>    <SPAN style="color:#007F00">'   First try changing some single cells,</SPAN><br>    <SPAN style="color:#007F00">'   then copy a range and paste it somewhere else</SPAN><br>    <SPAN style="color:#007F00">'   to see how targets are identified</SPAN><br>    MsgBox Target.Address<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[William777]

Hi,
I have a macro which output depence on the variable input. Input is represented by drop-down list. Before, I was choosing input from drop-down, then pressing the button my macro was running. Now, I'm thinking to if it's possible to exclude a spare operation from button pressing, just by clicking from drop-down. Any ideas?
My drop-down list is created through "Data->Validation->List box"

I have been following this thread as I have a similar situation as waldymar.
My drop-down list is also created through "Data->Validation->List" with source as "$AQ$6:$AQ$18". This source is a list of magazine titles, for example, "New Idea", "Home Beautiful", "Girlfriend", and so on.

I have 2 macros, one called "HideRows", which as the name suggests is to hide certain rows. The second macro is called "UnhideRows" which unhides the same set of rows.

The required process is that, if I choose "New Idea" from the drop-down list, the "HideRows" macro should run; and, if I choose "Home Beautiful", the "UnhideRows" macro should run.

Using lenze's example, is this the way I should code it?

Private Sub Worksheet_Change(ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
        Set rng = Target.Parent.Range("AQ6:AQ18")
             '   Only look at single cell changes
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met
            '   Do your thing here
            Select Case Target
                 Case "New Idea" : Call HideRows
                 Case "Home Beautiful" : Call UnhideRows
                 'etc
                 Case Else:
             End Select
End Sub

Thanks for your kind assistance.

William

 

[lenze]

Yes, but you can shorten it some, and also use more than 1 title. Say your dropdown is in $A$1

Private Sub WorkSheet_Change(ByVal Target as Range)
If Target.Address <> "$A$1" Then Exit Sub
Select Case Target
    Case "New Idea" : Call macro1
    Case "Home Beautiful" : Call macro2
    Case Else:
End Select
End Sub

BUT, your Case statements can be multiple values

Case "New Idea" , "Old Idea" , "Same ol Idea" : Call macro1

HTH
lenze

 

[William777]

lenze, thanks for your quick reply. Still up at 12.30 am?
I had put the following codes in the excel module

Sub HideRows()
    Rows("17:22").EntireRow.Hidden = True
    Rows("29:32").EntireRow.Hidden = True
End Sub

Sub UnhideRows()
        Rows("17:22").EntireRow.Hidden = False
        Rows("29:35").EntireRow.Hidden = False
End Sub

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Address <> "$B$5" Then Exit Sub
Select Case Target
    Case "New Idea": Call HideRows
    Case "Home Beautiful": Call UnhideRows
    Case Else:
End Select
End Sub

But it didn't work for me. Did I do something wrong?

Regards,
William

 

[lenze]

The Change Event go MUST go in the WorkSheet module. RightClick the sheet tab and choose "View Code". Paste it there. Your other macros go in a regular module. With the VBE open (ALT+F11), choose Insert>Module

HTH
lenze

 

[William777]

The Change Event MUST go in the WorkSheet module. RightClick the sheet tab and choose "View Code". Paste it there. Your other macros go in a regular module. With the VBE open (ALT+F11), choose Insert>Module

HTH
lenze

Hurray it works!!!
Thanks lenze, you are a CHAMPION.

 

[chs4]

Hi Lenze,
I am so happy to stumble upon this thread. I have been trying to incorporate the concept into my worksheet and when I put in a value (allowed through data validation:list) it does nothing until I click on that cell again. Then the macro runs. If I click on the cell again to change the value, the macro runs again and I can't change the value in the cell normally. I have to click the cell and drag to highlight the neighboring cell. Then I can go back and pick another value from the dropdown list. Is there any way around this?
This is the code I'm using

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
        Set rng = Target.Parent.Range("M12")
            If Target.Address <> "$M$12" Then Exit Sub
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            Select Case Target
                 Case "1": Call CustomColorInput
                 Case "2": Call Macro2
                 Case "3": Call Macro3
                 Case "4": Call Macro4
                 Case Else:
             End Select
End Sub

Thanks.
chs4

 

[chs4]

Hi,
I have something to add to my last post. I actually have six different dropdown boxes, rather than one. I need this code to run different macros based on changes in each of these cells with a dropdown box. When I tried to copy and paste my code for on dropdown box six times, it fussed at me for having duplicates. Any suggestions?

Here's a detailed version of what I'm trying to do in case that didn't make sense. There is one dropdown box in each of cells A16:A21. Each has the same dropdown list where they can pick from 15 different items, though it's a shrinking list so they can pick each only once. Among those 15 items are Custom Color 1, Custom Color 2, Custom Color 3, and Custom Color 4. I want it so that if the user picks any one of those 4 choices in A16, it will run CustomColorInput1. If they pick any of those 4 choices in A17, it will run CustomColorInput2. If they pick any of those 4 choices in A18, it will run CustomColorInput3 etc. This is the code I have but it only works for A16 (the previous code I posted was just a test to see if I could make it work but I have the same problem with this code as well).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
        Set rng = Target.Parent.Range("A16")
            If Target.Address <> "$A$16" Then Exit Sub
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            Select Case Target
                 Case "Custom Color 1": Call CustomColorInput1
                 Case "Custom Color 2": Call CustomColorInput1
                 Case "Custom Color 3": Call CustomColorInput1
                 Case "Custom Color 4": Call CustomColorInput1
                 Case Else:
             End Select
End Sub

Thanks.
-Chs4

 

[lenze]

Hi chs4:
I believe your 1st problem is you need to use the WorkSheet_Change event. Your code has WorkSheet_SelectionChange. So, an outline of your code might be

Private Sub WorkSheet_Change(ByVal Target as Range)
Select Case Target.Address
      Case "$A$16"
            Select Case Target
                 Case "Something"
                      'Your code or macro call
                 Case "Something Else"
                      'Your code or macro call
                 'etc
                 Case Else
             End Select
      Case "$A$17"
             Select Case Target
                 Case "Something"
                      'Your code or macro call
                 Case "Something Else"
                      'Your code or macro call
                 'etc
                 Case Else
              End Select
        'etc
      Case Else
End Select

HTH
lenze

 

[chs4]

Lenze,
That is perfect! Thank you so much for your help.
-Chs4