Dropdown Boxes again

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.














--
Remove the 'old' to reply to me.

GT: Mr Musashi

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
"Col" <oldnewsboy64@hotmail.com> wrote in message
news:e6ppA6bLGHA.3264@TK2MSFTNGP11.phx.gbl...
> 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.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> Remove the 'old' to reply to me.
>
> GT: Mr Musashi
>
>
>

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



Col wrote:
>
> 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.
>
> --
> Remove the 'old' to reply to me.
>
> GT: Mr Musashi

--

Dave Peterson