Accessing the values of form controls on worksheets in Excel 2000

[robbinma]

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



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[Tom Ogilvy]

They are in the dropdowns collection

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

 

[robbinma]

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

Cheers,

Mark