Accessing the values of form controls on worksheets in Excel 2000

  • Thread starter Thread starter robbinma
  • Start date Start date
R

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.:rolleyes:

Regards,

Mark



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


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
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
 
Thanks Tom.
I should have realised about the sting/range difference.
That now works great.:D

Cheers,

Mark
 
Back
Top