Newbie question: How to iterate over combo boxes on a worksheet

[Mark Shirley]

In MS Excel 2003
I am trying to write VBA code to programmatically
analyze a large spreadsheet I didn't create.

One of the worksheets has 24 combo boxes on it.
One of the first steps I need to accomplish is iteration over
those 24 combo boxes to pull out which cells their values are linked to.

The root of my problem seems to be that I don't understand what
kinds of objects the combo boxes are.

I've found the Worksheet.OLEObjects collection but it's count is only 1,
so they don't seem to be OLEObjects. (When I found the LinkedCell property
of an OLEObject, I thought I'd solved it, but there aren't enough
objects in that collection.)

I've found the worksheet.Shapes collection. It's count is 218, so maybe
they're shapes. But I don't see how to distinguish further. Their
TypeName is always "Shape". Their .Type property varies, but there
aren't 24 objects in the collection with any one Type. (By printing
the .Type properties out, I only see integer values, no symbolic information.)

When I right click on one of the Combo Boxes in design mode, I don't
see the Properties and View Code operations that are present for a
Combo Box that has been created from the Control Toolbox. There are
cut,copy,paste,grouping,order,assign macro, and format control options.

If I pick Format Control, I see
Size, Protection, Properties, Web, Control tabs
Under the Control tab, there are Input Range, Cell link, Drop down lines
pulldowns and a 3d shading checkbox.

I'm completely confused about what these Combo Boxes are. What
might they be and how can I write code that gets its 'hands' on them?
When I do, how will I be able to get an identifier for the linked cell?

Thank's in advance *very* much.
- Mark Shirley

 

[Dave Peterson]

The "assign macro" option you saw when you rightclicked makes it sound like
they're dropdowns from the Forms toolbar.

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim myDD As DropDown
Dim oRow As Long

Set curWks = ActiveSheet
Set newWks = Worksheets.Add

newWks.Range("a1").Resize(1, 3).Value _
= Array("Name", "Address", "linkedCell")

oRow = 1
With curWks
'MsgBox .DropDowns.Count & " were found!"
For Each myDD In .DropDowns
oRow = oRow + 1
With newWks.Cells(oRow, 1)
.Value = myDD.Name
.Offset(0, 1).Value = myDD.TopLeftCell.Address(0, 0)
.Offset(0, 2).Value = myDD.LinkedCell
End With
Next myDD
End With
End Sub


the Comboboxes from the control toolbox toolbar were added in xl97. These Forms
dropdowns were kept for compatibility purposes (although they do behave nicer
for some functions).

 

[Mark Shirley]

In MS Excel 2003
I am trying to write VBA code to programmatically
analyze a large spreadsheet I didn't create.

Here's some more detail. I've discovered data validation dropdowns
and am beginning to thing that's what these combo boxes are. However,
when I tried to get a handle on them like this:
set r = <worksheet>.Cells.SpecialCells(xlCellTypeAllValidation)

where r is a Range, I get an error "no cells found".

- Mark Shirley

 

[Dave Peterson]

I don't think you're correct. I think they are dropdowns from the Forms
toolbar.

 

[Mark Shirley]

I don't think you're correct. I think they are dropdowns from the Forms
toolbar.

You are correct. I didn't know about the Forms Toolbar, just
the ToolBox. Thank you for posting the code also. It was
clear and helpful.
- Mark

When I posted my second message, I looked for but didn't see a
response to the original message. (I probably used google to find
my original and forgot to look at the whole thread.) Anyway, I hadn't
seen your reply when I added more detail. Sorry for the confusion.