Dim sheet objects

[Mats Samson]

Hello,
I'm trying to Declare worksheet Comboboxes (from Control Toolbox) as object
variables, but I don't know how to refer to them!
Dim CoBo as MSForms.Combobox
doesn't work on a worksheet, CoBo turns to Nothing and and I get a Type
Mismatch Error. I found that Controls are referred to as OLEObjects but I
can't find information how to declare the variable.
For Each CoBo In Worksheets("Calculation").OLEObjects
CoBo.Clear
If Left(CoBo.Name, 4) = "Curr" Then
CoBo.List = CurrVar
End If
Next

Thanks for help!
Mats

 

[Robert ap Rhys]

Hello,
I'm trying to Declare worksheet Comboboxes (from Control Toolbox) as object
variables, but I don't know how to refer to them!
Dim CoBo as MSForms.Combobox
doesn't work on a worksheet, CoBo turns to Nothing and and I get a Type
Mismatch Error. I found that Controls are referred to as OLEObjects but I
can't find information how to declare the variable.
For Each CoBo In Worksheets("Calculation").OLEObjects
CoBo.Clear
If Left(CoBo.Name, 4) = "Curr" Then
CoBo.List = CurrVar
End If
Next

Hi,

The Object property of an OLEObject, er, object returns the inner object:

Dim obj As OLEObject
For Each obj In Worksheets("Calculation").OLEObjects
If TypeOf obj.Object Is MSForms.ComboBox Then
'...Do your stuff...
End If
Next

HTH

Robert

 

[Tom Ogilvy]

Dim obj as OleObject
Dim CoBo as MSForms.Combobox
For Each obj In Worksheets("Calculation").OLEObjects
if typeof Obj.Object is MSforms.Combobox then
set CoBo = Obj.Object
if Left(CoBo.Name,4) = "Curr" then
CoBo.Clear
CoBo.List = CurrVar
end if
End If
Next

Or if only Comboboxes have the Name Curr

Dim obj as OleObject
For Each obj In Worksheets("Calculation").OLEObjects
if Left(Obj.Name,4) = "Curr" then
Obj.Object.Clear
Obj.Object.List = CurrVar
End If
Next

Using the Cobo variable dim'd as MSForms.Combobox could also be added.

Note in xl97, the OleObject Name and the Combobox Name might not agree.

 

[Mats Samson]

Thank you guys, it worked fine!
An extra question?! A little bit academic perhaps but I'm curious to learn.
Lets say I have 100 labels each in 3 multipage pages.
If I would like to disable 1/3 of all labels, from a performance point of
view,
what is best?
1. Use a procedure as the below one to search through all labels to find the
right label.
2. Write 100 lines with Labelx.Enable = False
In the first case, Excel has to work more, especially if it would be a
procedure that is frequently used, the performance "drops".
In the second case my file will grow and there is an overall performance drop
but as the address of each label is specifically written, Excel doesn't need
to look
in all 300 labels.
Finally is there a way of "attach" the labels to the page "container" so I may
use the procedure on the current page only? Why run a procedure on a page
that is not visible/used?
Regards
Mats