Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CombBox - Object of What Collection?
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?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CombBox - Object of What Collection?
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CombBox - Object of What Collection?
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CombBox - Object of What Collection?
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CombBox - Object of What Collection?
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
CombBox - Object of What Collection?
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
CombBox - Object of What Collection?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Label Options - Forms Object vs Control Box Object | Excel Discussion (Misc queries) | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |