Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Assigning Macros to Drop Down Boxes

  Asked By: Dylan    Date: Oct 05    Category: MS Office    Views: 1951
  

I am having trouble trying to assign macros to drop down boxes and I
need help!
My spreadsheet has two drop down boxes in 2 different columns. For
example, the first drop down box will list all of my companies
products types. The second drop down box has a list of product
variations. I would like to set the spreadsheet up so once a product
is selected in the first drop down box the second drop down box only
displays the variations of that particular product and not variations
for all products. Basically I'd like to filter the data as each drop
down box is used. Is this possible?

When I try to assign a macro to a drop down box it only assigns it to
the entire box and not to a particular component within the box and
that's my question in a nutshell. Can you assign macros to components
of a drop down box or just to the entire box in general?

I just know the basics regarding macros and have no experience with
VBA.

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Marc Anderson     Answered On: Oct 05

If I understand properly what you're trying to do (and I think I do), then there's good news: you don't need any VBA for this!

Dave Hawley has a great example of how to do this at his Ozgrid site. You can get it from this page:

http://www.ozgrid.com/download/default.htm

Look for the doanload called ChangingCombo2.zip.

 
Answer #2    Answered By: Kiet Jainukul     Answered On: Oct 05

But the solution does not meet my requirements, as I can just see the
combos working, not how they are working.

Do you know how to fire a macro when the user clicks on one of the
options in a dropdown in, say column C?

 
Answer #3    Answered By: Mae Roberts     Answered On: Oct 05

When you say a 'dropdown in Column C', I don't think you're talking about a combo box, are you, but an auto drop  based on data validation. If that's the case, then a macro can't trigger from that drop, only from a real combobox, which has a full range of events (to do what I think you're proposing you'd probably want code in the after update event.)

But what you can do is base your data validation on the value in a field. Here's how. Let's say you have 3 options in the column C validation, which might be:

Red
Blue
Green

And then Red has suboptions you want to appear in Column D of - say -

Light red
Dark red
Metallic red
Crimson

(other colours would have different lists.)

Type the list of 4 different reds into a spare column somewhere and name the range Red. Do the same for the Blue and Green options.

You can now set the data validation for Column D on a custom basis, as something like:

= INDIRECT(C1)

This will read the value in Column C and convert it to a range reference.

I haven't had time to actually do this, so you may need to play around with it a little, but it should give you the basis of a solution.

 
Answer #4    Answered By: Freda Lane     Answered On: Oct 05

you are right in thinking that i want an autodrop based on
data->validation. The example you have taken, exactly matches my
problem. But i cud not figure out a sol. I tried indirect(C1). Does
not work. I named the range of red colours as RED, but don't know
where and how to use it.
Can u please work out a solution and send it to me?

 
Answer #5    Answered By: Hooriya Khan     Answered On: Oct 05

You need to enter =INDIRECT(C1) as a custom data validation on your cell. This probably isn't the place for a full explanation of the data validation options and dialogue, but I thing if you Google with Excel Data Validation you'll find a tutorial.

 
Didn't find what you were looking for? Find more on Assigning Macros to Drop Down Boxes Or get search suggestion and latest updates.




Tagged: