Change several Dropdown box values at once

[bradjedis]

Hey everone,

I have an interesting question... I have 20 dropdown boxes that each contain 'Yes' & 'No' we are looking to have a button assigned to a macro to toggle all of them at once. Reasoning is if it is a Yes, there is other code that acts upon the Yes, and triggers an e-mail.

If they are set to Yes, we would want to toggle all to No. and If they all contain No, we would want to toggle to Yes.

Any Ideas?


Thanks,
Brad

[HansV]

What kind of dropdown boxes are these?

1. On a worksheet, from the Forms toolbar.
2. On a worksheet, from the Control Toolbox.
3. On a userform (created in the Visual Basic Editor)

Apart from that:

• Are there other dropdown boxes than the 20 you mention?
• Have you given them consistent names, for example ComboBox1, ComboBox2, ..., ComboBox20 ?

[bradjedis]

Hans,
There are 2 columns. They are pulling the Yes/No from a different set of cells on the same tab. Looks like they are suing data validation to populate the list.

[HansV]

OK, so you need to set the values of a range of cells to 'Yes' or to 'No'. What is the range?

[bradjedis]

Range of cells is E2:24

Brad

[HansV]

You could use code like this in a macro:

Code: Select all

If Range("E2") = "No" Then
  Range("E2:E24") = "Yes"
Else
  Range("E2:E24") = "No"
End If

[bradjedis]

Coolness.

Thanks and have a great weekend!

Brad

[HansV]

You too!