Hello,

I have inherited a set of worksheets with about 40 drop down form
controls on each worksheet.
I need to programatically extract the values from the drop down boxes
so that they can be loaded into a database.

Unfortunately the creater didn't setup the cell link (Format
Control-Control-Cell link) and it doesn't look like it will be
practical to update all 150 instances of the spreadsheet with the
correct cell links..... :(

I have tried to access the drop down boxes through the OLEObjects
collection but a count says that there are no OLEobjects on the
worksheet.
As far as I can tell this is because they are form controls and not
controls selected from the control toolbox.
'Debug.Print Worksheets("General details").OLEObjects.Count

The drop down boxes are in the shapes collection but I can't do any
kind of casting so that I can get them into a control object (or
something similar) so that I can get the value.

I am using Excel 2000 SR-1.

Any help would be appreciated as my manager is keen to find a solution
quickly.

Regards,

Mark



------------------------------------------------
Message posted from http://www.ExcelTip.com/

-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
-- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/
------------------------------------------------

They are in the dropdowns collection

dim drp as DropDown
for each drp in activesheet.DropDowns
drp.LinkedCell = drp.TopLeftCell.Address(External:=True)
Next


--
Regards,
Tom Ogilvy


"robbinma" wrote in message
...
Hello,

I have inherited a set of worksheets with about 40 drop down form
controls on each worksheet.
I need to programatically extract the values from the drop down boxes
so that they can be loaded into a database.

Unfortunately the creater didn't setup the cell link (Format
Control-Control-Cell link) and it doesn't look like it will be
practical to update all 150 instances of the spreadsheet with the
correct cell links..... :(

I have tried to access the drop down boxes through the OLEObjects
collection but a count says that there are no OLEobjects on the
worksheet.
As far as I can tell this is because they are form controls and not
controls selected from the control toolbox.
'Debug.Print Worksheets("General details").OLEObjects.Count

The drop down boxes are in the shapes collection but I can't do any
kind of casting so that I can get them into a control object (or
something similar) so that I can get the value.

I am using Excel 2000 SR-1.

Any help would be appreciated as my manager is keen to find a solution
quickly.

Regards,

Mark



------------------------------------------------
Message posted from http://www.ExcelTip.com/

-- View and post Excel related usenet messages directly from
http://www.ExcelTip.com/forum
-- Hundreds of free MS Excel tips, tricks and solutions at
http://www.ExcelTip.com/
------------------------------------------------

Thanks Tom.
I should have realised about the sting/range difference.
That now works great.:D

Cheers,

Mark

Tom Ogilvy wrote:
[b]drp.ListfillRange is a string, not a range. You need to do

Range(drp.ListFillRange)

From the immediate window:

? drp.Value
7
? drp.ListfillRange
$A$1:$A$25
? application.Index(Range(drp.ListfillRange),drp.val ue,1,1)
Item7

--
Regards,
Tom Ogilvy