G
Guest
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?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Juan Pablo González
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
Next loop?
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
perfom a modification on each ComboBox in the application via a For EachGeorge said:What Collection are ComboBoxes objects of? So that they can be accessed to
Next loop?
T
Tom Ogilvy
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
Next loop?
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
perfom a modification on each ComboBox in the application via a For EachGeorge said:What Collection are ComboBoxes objects of? So that they can be accessed to
Next loop?
G
Guest
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 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.
J
Juan Pablo González
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
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:
=EMBED("Forms.ComboBox.1","").
you...
This
Set cboItem = oleObj
should be
Set cboItem = oleObj.Object
--
Regards,
Juan Pablo González
with an Excel Worksheet, the OLE method would seem to be the most likely toGeorge said:Tom, Juan,
Thank you for your help. I have tried both methods, but as I am using it
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:
Design mode, when I selected a ComboBox, the formula bar read: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
=EMBED("Forms.ComboBox.1","").
G
Guest
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.
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.
J
Jake Marx
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]
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]
G
Guest
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
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