Drop down lists and macros

[lost and confused in excel-land]

I want to set a choices in several cells that have drop down lists to one
value each time I open the worksheet (or if I have been doing "what if"
scenarios).

Lets say I have 3 cells that have 5 choices each A1, A2, and A3

In B1, B2, B3 have values inserted based on the drop down choices in column A

One of those choices in A for all 3 cells is "No choice", with a 0 value in
column B if that choice is taken.

I want to perform a macro that goes in and chooses "No choice" automatically
when the macro is run. I have been unsuccessful, it doesn't do anything with
the drop down lists (I have other cells that the macro does change correctly,
just not the choices in the ddl)

How can I get around this?

Many many thanks!

 

[JP]

Does this help?

http://www.bettersolutions.com/excel/EGH188/QE229212022.htm


--JP

 

[Gary Brown]

This macro will put the value "No Choice" in all cells with Data Validation
when the macro is run.

'/=================================/
' Sub Purpose: Put the value "No Choice"
' in all Data Validation cells
'/=================================/
Sub Macro1()
Dim objCell As Object
Dim rngValidation As Range

'find all cells with data validation
Set rngValidation = _
Cells.SpecialCells(xlCellTypeAllValidation)

'put 'No Choice' in all cells that
' have data validation
For Each objCell In rngValidation
objCell.Value = "No Choice"
Next objCell

'free up memory
Set rngValidation = Nothing
End Sub
'/=================================/

 

[lost and confused in excel-land]

Thanks for the assistance, but I don't think I made myself clear. In the
DDL itself, there is a "No choice taken", or "No Option" or some other choice
that has a 0 value associated with it. I have the choices and corresponding
values populating the cells and have everything working correctly, except,
when I want to start with a blank sheet.

I need to be able to have the user click a control button which would run a
macro that would go into EACH drop down list (there are MANY) and choose the
"No Option" choice, which, then would bring up the 0 value (which is working
correctly, but manually so.

When I have quantities associated with the drop down lists, the macro runs
great. I just won't go in and select the correct option in each dropdown
list.

I have been using the macro recorder and performing the task manually then
stop recording, but doesn't take.

HELP!! Many thanks in advance - you guys are smart and awesome!!