Referring to OLEObjects (combobox's on worksheet)

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.
 
T

Tom Ogilvy

For iX = 9 To 20
sBoxName = "ComboBox" & CStr(iX)
Me.OLEObjects(sBoxName).Object.Listindex = -1
Next
 
I

Ian Chappel

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)
 
I

Ian Chappel

Sorry Tom, Got it sorted by changing to:

Set MyBox = Me.OLEObjects(sBoxName).Object

Not exactly sure why though?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type Mismatch error when trying to generically set ComboBox to aComboBox variable 8
OLEObjects - Paste combobox contents to a sheet 4
2 Dim Array Sort, on any columns, ascend or descend. Guidance des 0
Using Object & Worksheet names as variables 1
Variable Range, Control ToolBox ComboBox 3
Dim sheet objects 3
Adding a VLookup to an AutoComplete ComboBox 0
AddItem to Worksheet ComboBox 2

Top