Problem: Excel 2003, identify the control type..

[Richard U]

..of an OLEobject.

I've got several dozen controls that I need to reset upon opening a worksheet.

Some need to be set to false, other blank, et cetera.

Is there a way to identify their type so that I can do something like...

For each thing in sheets(1).oleobjet

'if the thing is a listbox, do "a"'
'if the thing is a checkbox, do "b

Next thing




thanks folks!

 

[Case_Germany]

Hi,

the first code "Sub Main()" You can see which types of controls you have in the active worksheet. With the second code "Sub Main_1()" you can do something else, depending on the type. Instead of "ActiveSheet" you can also specify a spreadsheet:

Option Explicit
Sub Main()
    Dim objCtrl As Object
    For Each objCtrl In ActiveSheet.OLEObjects
        Debug.Print "Name: " & objCtrl.Name
        Debug.Print "Type: " & objCtrl.progID
    Next objCtrl
End Sub
Sub Main_1()
    Dim objCtrl As Object
    For Each objCtrl In ActiveSheet.OLEObjects
        Select Case objCtrl.progID
            Case "Forms.CommandButton.1"
                ' do...
            Case "Forms.ComboBox.1"
                ' do...
            Case "Forms.CheckBox.1"
                ' do...
            Case "Forms.ListBox.1"
                ' do...
            Case "Forms.TextBox.1"
                ' do...
            Case "Forms.ScrollBar.1"
                ' do...
            Case "Forms.SpinButton.1"
                ' do...
            Case "Forms.OptionButton.1"
                ' do...
            Case "Forms.Label.1"
                ' do...
            Case "Forms.Image.1"
                ' do...
            Case "Forms.ToggleButton.1"
                ' do...
        End Select
    Next objCtrl
End Sub

The issue of "Debug.Print" is in "Immediate Window" (CTRL+G VBA-Editor).

 

[Richard U]

Thank you!