What Collection are ComboBoxes objects of? So that they can be accessed to perfom a modification on each ComboBox in the application via a For Each Next loop?

Depends on the toolbar that you used. But, assuming that you're using the
Control Toolbox, then its usually something like

Dim Ctl As MSForms.Control
For Each Ctl In Me.Controls
If TypeName(Ctl) = "ComboBox" Then
'Stuff here
End If
Next Ctl

Regards,

Juan Pablo González

"George" wrote in message
...
What Collection are ComboBoxes objects of? So that they can be accessed to
perfom a modification on each ComboBox in the application via a For Each
Next loop?

Juan gave you the code for comboboxes on a Userform.

for comboboxes from the control toolbox toolbar on a worksheet

Dim cbox as MSForms.Combobox
for each oleobj in Activesheet.OleObjects
if typeof oleObj.Object is MSForms.Combobox then
set cbox = oleObj.Object
cbox.Value = cbox.list(cbox.listcount-1)
end if
Next


for dropdowns from the Forms toolbar

Dim ddown as DropDown
for each dDown in ActiveSheet.DropDowns
dDown.Value = dDown.list(dDown.Listcount)
Next

Then there are dropdowns associated with data validation.

--
Regards,
Tom Ogilvy





"George" wrote in message
...
What Collection are ComboBoxes objects of? So that they can be accessed to
perfom a modification on each ComboBox in the application via a For Each
Next loop?

Tom, Juan,
Thank you for your help. I have tried both methods, but as I am using it with an Excel Worksheet, the OLE method would seem to be the most likely to succeed, though I have tried variations. The below For loop runs through and I watch the value of the oleObj from a Watch Window. After going through some other objects in the application, each of the ComboBoxes is accessed, but none trigger a True response from the TypeOf function. The MessageBox inside the For loop is never reached, though as I mentioned, each of the ComboBoxes is accessed, but just doesn't produce a True to get into the If block. I tried leaving off the MSForms and the access member operator and just using "ComboBox" alone in both places, but it was unsuccessful. I tried declaring oleObj as a variant, but the ComboBoxes are never reset. I tried using for the If Conditional statement:
If TypeName(oleObj) = "ComboBox" Then
But it likewise ran through, but did not trigger a true response.

Dim oleObj As OLEObject, cboItem As MSForms.ComboBox

With Application.Workbooks(1).Worksheets(1)
For Each oleObj In .OLEObjects
If TypeOf oleObj Is MSForms.ComboBox Then
Set cboItem = oleObj
cboItem.ListIndex = -1
MsgBox "Arrived inside If block"
End If
Next oleObj
End With

Apparently, each ComboBox is not being recognized as a ComboBox, though in Design mode, when I selected a ComboBox, the formula bar read: =EMBED("Forms.ComboBox.1","").
Any idea why it is not being recognized?

Thank you much.

You missed a little, but quite important !, piece in the code that Tom gave
you...

This

Set cboItem = oleObj

should be

Set cboItem = oleObj.Object

--
Regards,

Juan Pablo González

"George" wrote in message
...
Tom, Juan,
Thank you for your help. I have tried both methods, but as I am using it
with an Excel Worksheet, the OLE method would seem to be the most likely to
succeed, though I have tried variations. The below For loop runs through and
I watch the value of the oleObj from a Watch Window. After going through
some other objects in the application, each of the ComboBoxes is accessed,
but none trigger a True response from the TypeOf function. The MessageBox
inside the For loop is never reached, though as I mentioned, each of the
ComboBoxes is accessed, but just doesn't produce a True to get into the If
block. I tried leaving off the MSForms and the access member operator and
just using "ComboBox" alone in both places, but it was unsuccessful. I tried
declaring oleObj as a variant, but the ComboBoxes are never reset. I tried
using for the If Conditional statement:
If TypeName(oleObj) = "ComboBox" Then
But it likewise ran through, but did not trigger a true response.

Dim oleObj As OLEObject, cboItem As MSForms.ComboBox

With Application.Workbooks(1).Worksheets(1)
For Each oleObj In .OLEObjects
If TypeOf oleObj Is MSForms.ComboBox Then
Set cboItem = oleObj
cboItem.ListIndex = -1
MsgBox "Arrived inside If block"
End If
Next oleObj
End With

Apparently, each ComboBox is not being recognized as a ComboBox, though in
Design mode, when I selected a ComboBox, the formula bar read:
=EMBED("Forms.ComboBox.1","").
Any idea why it is not being recognized?

Thank you much.

Thank you, Juan for pointing that out. I didn't notice I had that left that off as I don't really see what it is doing. However, it finally worked as follows. I thank you and Tom, much.

Could either of you tell me why the .Object is needed after oleObj as I thought that oleObj was referring to an Ole Object as it seems to be identified as such and addressed as part of the OLEObjects Collection in the For Each statement? Thus to add .Object would seem redundant and be a reference to an object of the same object? Could you please straighten out my thinking on this?

Dim oleObj As OLEObject, cboItem As MSForms.ComboBox

With Application.Workbooks(1).Worksheets(1)
For Each oleObj In .OLEObjects
If TypeOf oleObj.Object Is MSForms.ComboBox Then
Set cboItem = oleObj.Object
cboItem.ListIndex = -1
End If
Next oleObj
End With

Again, I thank you both for and appreciate your keen expertise for I had tried many scenarios, but this did not occur to me.

Hi George,

The OLEObject can contain many types of "objects", such as textboxes,
checkboxes, etc. So it exposes some common properties and methods, but it
can't contain all of them for each different type of object that it may hold
(some have properties/methods not found in other objects). The Object
property will return a reference to the actual object being held in the
OLEObject, which will give you access to the properties and methods specific
to that type of object (in this case, a ComboBox). Hopefully, that makes
sense - if you need clarification or have questions, please post back.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


George wrote:
Thank you, Juan for pointing that out. I didn't notice I had that
left that off as I don't really see what it is doing. However, it
finally worked as follows. I thank you and Tom, much.

Could either of you tell me why the .Object is needed after oleObj as
I thought that oleObj was referring to an Ole Object as it seems to
be identified as such and addressed as part of the OLEObjects
Collection in the For Each statement? Thus to add .Object would seem
redundant and be a reference to an object of the same object? Could
you please straighten out my thinking on this?

Dim oleObj As OLEObject, cboItem As MSForms.ComboBox

With Application.Workbooks(1).Worksheets(1)
For Each oleObj In .OLEObjects
If TypeOf oleObj.Object Is MSForms.ComboBox Then
Set cboItem = oleObj.Object
cboItem.ListIndex = -1
End If
Next oleObj
End With

Again, I thank you both for and appreciate your keen expertise for I
had tried many scenarios, but this did not occur to me.

Thanks, Jake
I had kind of thought it was something along those lines, but apparently still don't understand it completely enough to successfully implement it

In a related question I was thinking of posting the following as a separate question, perhaps you can help

In attempting to implement a For Each Next Loop with a custom Collection, I tried to implement Tom and Juan's insights, however I could not get it to work. Perhaps there are special nuances with custom Collections that I must learn
Can you or anyone please tell me why the below does not work

Declared in module General Declarations: Private colPicts As Collectio

Statement from initialization procedure
Set colPicts = New Collectio
Statement of many from initialization procedure
colPicts.Add Worksheets(1).Shapes("Picture 57"), "Fruit Bowl
colPicts.Add Worksheets(1).Shapes("Picture 58"), "Salad Bowl
colPicts.Add Worksheets(1).Shapes("Picture 59"), "Pasta Bowl

Below is the problem procedure that seems to have a simiar to the orginal object reference problem, as it returns an "Object required" error. Because of its being a Collection, Tom and Juan's advice that worked quite well with a Combo Box cannot be applied the same way. Would anyone have any ideas

Public Sub ShowAllPicts(
Dim pict As Objec

For Each pict In colPict
pict.Visible = Tru
Next pic
End Su