Referring to OLEObjects (combobox's on worksheet)

  • Thread starter Thread starter Ian Chappel
  • Start date Start date
I

Ian Chappel

How can I refer to, for instance, ComboBox9 to ComboBox20?

I've tried the following but OLEObjects doesn't seem to work , but if I try
to retrieve the object's name, no problem. I have a feeling the OLEObjects
bit is read-only, but I'm not sure how to work round it, without naming each
box.

Dim iX As Integer, sBoxName As String

For iX = 9To 20
sBoxName = "ComboBox" & CStr(iX)
Me.OLEObjects(sBoxName) = ""
Next

The code is located in a Worksheet.
 
For iX = 9 To 20
sBoxName = "ComboBox" & CStr(iX)
Me.OLEObjects(sBoxName).Object.Listindex = -1
Next
 
Thanks Tom

Actually, I've got a very similar but slightly different probelm now. What
can I change here to refer to a ComboBox from a string I have generated,
e.g.:

dim sBoxName as string, iBoxNum as integer

sBoxName = "ComboBox" & cStr(iBoxNum)
Set MyBox = Me.OLEObjects(sBoxName)
 
Sorry Tom, Got it sorted by changing to:

Set MyBox = Me.OLEObjects(sBoxName).Object

Not exactly sure why though?
 
Back
Top