Referring to OLEObjects (combobox's on worksheet)
 

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.

Referring to OLEObjects (combobox's on worksheet)
 

Apologies - I am actually trying to clear the box(s).

"Tom Ogilvy" wrote in message
...
What are you trying to do

Me.OleObjects(sBoxName) = "" is unclear. Are you trying to rename the
box?


Dim iX As Integer, sBoxName As String

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

would set the value of the combobox.

--
Regards,
Tom Ogilvy

"Ian Chappel" wrote in message
...
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.

Referring to OLEObjects (combobox's on worksheet)
 

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

--
Regards,
Tom Ogilvy

Ian Chappel wrote in message
...
Apologies - I am actually trying to clear the box(s).

"Tom Ogilvy" wrote in message
...
What are you trying to do

Me.OleObjects(sBoxName) = "" is unclear. Are you trying to rename the
box?


Dim iX As Integer, sBoxName As String

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

would set the value of the combobox.

--
Regards,
Tom Ogilvy

"Ian Chappel" wrote in message
...
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.

Referring to OLEObjects (combobox's on worksheet)
 

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)

"Tom Ogilvy" wrote in message
...


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

--
Regards,
Tom Ogilvy

Ian Chappel wrote in message
...
Apologies - I am actually trying to clear the box(s).

"Tom Ogilvy" wrote in message
...
What are you trying to do

Me.OleObjects(sBoxName) = "" is unclear. Are you trying to rename
the
box?


Dim iX As Integer, sBoxName As String

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

would set the value of the combobox.

--
Regards,
Tom Ogilvy

"Ian Chappel" wrote in message
...
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.

Referring to OLEObjects (combobox's on worksheet)
 

Sorry Tom, Got it sorted by changing to:

Set MyBox = Me.OLEObjects(sBoxName).Object

Not exactly sure why though?

"Ian Chappel" wrote in message
...
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)

"Tom Ogilvy" wrote in message
...


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

--
Regards,
Tom Ogilvy

Ian Chappel wrote in message
...
Apologies - I am actually trying to clear the box(s).

"Tom Ogilvy" wrote in message
...
What are you trying to do

Me.OleObjects(sBoxName) = "" is unclear. Are you trying to rename
the
box?


Dim iX As Integer, sBoxName As String

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

would set the value of the combobox.

--
Regards,
Tom Ogilvy

"Ian Chappel" wrote in message
...
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.