Dropdown Boxes again

C

Col

Still having problems with copying the dropdown boxes on my sheet.

I had the reply to my original post

"Select all the cells, and go into DV and then just change that formula
once"

Please forgive my ignorance but what does DV mean, if it refers to Data
Validation then yes it works for the cells but not the 'Control' tab within
'Format Control' on the drop down boxes themselves.

My original post problem is repeated below;

"Dear all,

I'm sure I've done this before successfully but can't remember how to do it!

I have a sheet with two tabs, one of them is just a repeated group of cells
with eight drop downs over cells F4 to F11 which are the cells into which
they link. The group is then repeated numerous times down the tab.

The second tab is the link for the contents of the drop down and covers
cells A1:A10.

What I wish to do is increase the number of options in the dropdown by four
meaning the drop down data will now be A1:A14, I've changed the first group
of drop downs on the first tab to reflect this but am having difficulty
copying them down the tab as I don't wish to manually have to change 60-70
drop downs.

I've tried copying/pasting, selecting the range and holding CTRL to copy
that way, I've also taken out the absolute ($) signs, but nothing works. I'm
using Excel 2000 but it may have been a previous version of the software
when I last did it and this may be the reason why it worked last time.

Anyone have an idea how I can do this the easy way!"

Thanks for any advice,

Colin.
 
O

Otto Moehrbach

Just copy the first cell that has the Data Validation (DV) (Edit - Copy).
Then select all the cells into which you want to paste the Data Validation.
Do Edit - Paste. Done HTH Otto
 
D

Dave Peterson

These are dropdowns from the Forms toolbar?

If yes, then you can adjust the list via code. But even better, you can use a
range name that grows contracts with what's in column A of that sheet (don't put
anything else in that column!).

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

I used Deb's technique to create a name MyList.

Then I could get all the dropdowns on sheet1 with this code:

Option Explicit
Sub testme()
Dim myDD As DropDown
For Each myDD In Worksheets("sheet1").DropDowns
myDD.ListFillRange = "MyList"
Next myDD
End Sub
 
Top