find out which control type being used

[Thymen]

Hi Folks,

been searching for quite some time, but cannot find an answer to my question, so if you know, plse help....

I want to figure out the type of control on a form. I tried the following:

Dim myControl As Control
On Error Resume Next
For Each myControl In UserForm1.Controls
If (myControl.Type = xlButton) Then MsgBox "Button" Else MsgBox "Not a button"
Next myControl

I want to use this kind of code to dump the contents of all controls that are e.g. textboxes (xlTextBox) to cells on a worksheet, while leaving all other control types be. But.... myControl.Type does not work.... Am I overlooking something?

Help greatly appreciated!


Thymen

[Jacob Hilderbrand]

Try this.


Option Explicit

Private Sub CommandButton1_Click()

Dim Ctrl As Control

For Each Ctrl In UserForm1.Controls
If TypeName(Ctrl) = "TextBox" Then
MsgBox Ctrl.Name
End If
Next

End Sub

[Bob Phillips]

Dim Ctrl As Control

For Each Ctrl In UserForm1.Controls
If TypeOf Ctrl Is MSForms.CommandButton Then
MsgBox "Button"
Else
MsgBox "Not a button"
End If
Next

[Thymen]

Works fine! Both samples! Thanks a lot!

Thymen

[Jacob Hilderbrand]

Glad to help :beerchug:

Take Care

[geekgirlau]

Thymen, don't forget to mark the thread as Solved - click on "Thread Tools" at the top of the page.

[Thymen]

Sorry folks, trying to mark this thread as solved.... but the Solved option in the thread tools do not appear to be working. Will try again later from my home system....

Thymen