Recording a macro to change item selected in drop down list

[Jen]

I am trying to record a macro in Excel that will reset values that are
standard for specific customer programs we have. The worksheet has different
data validation drop down lists and the correct selection is different for
different programs. Selecting the correct value while recording the macro
doesn't seem to work. Is there a special trick to make this happen? Is this
even possible? Thanks!

 

[ryguy7272]

Can you use a ComboBox instead? It would be something like this:
ActiveSheet.Shapes("Drop Down 1").Select

If you really want to go with Data Validation, and you could have a very
legitimate reason for this, take a look at this site:
http://www.contextures.com/xlDataVal10.html

If that doesn't work for you, take a look at this:
http://www.contextures.com/tiptech.html

There are tons of Data Validation options for you; all arranged in
alpha-order.

Regards,
Ryan---

 

[Jen]

I still can't figure out how to make this work. I tried again using a combo
box instead. I really don't know anything about writing code, so the line of
code you've written is Greek to me. If you could tell me exactly where I
would need to insert the code you've written, and what the commands are
doing, that would be great.

To be even more clear, I have different macros assigned to various buttons.
Depending on the button I select, a number of values are reset in various
cells, but I also want it to change the selection in the combo box, or data
validation drop down (whichever would work better in this situation). The
macro is assigned correctly, because when I push one of the buttons, all of
the values change EXCEPT for the selection in the drop down list. I've
re-recorded the macro several times to make sure I didn't accidentally miss
recording that one action.

The links you provided were very interesting and helpful for other things,
but didn't help me figure out this issue.

Thanks!

Jen

 

[DeaJL]

Hi,

Not sure if this will help but if you are using validation you can set the cells to whatever you want as a default using the following

Range("A1").Value = ""

The above would set A1 to "empty"
If yo have a number of drop downs just set the reference to the cell you want to set to the "default" value.

Note.
Just make sure the value you set is one that is in your validation list.
if you want a 1st value to be empty then use the string above just change the cell reference.

If you want an item that's already in your drop down list (say eggs) then change the reference then "eggs" after the = sign.


This code can go behind a button by way of a macro.
It's a pretty cool way to reset a form of any kind really. I've got about 7 or 8 drop downs the results of which also affect other cells using IF's the good thing for me is that the formulas in the other cells also "reset" via the if statements which relate to the value of the drop downs.
so I can have these cells effectively "reset" as well!



Dean