Make String of Identifier, et al

[fumei]

Hi folks. Two questions, rather related. I have a userform with a textbox, a checkbox, a combobox, and two commandbuttons.

One commandbutton just unloads the form, this can be ignored.

The other commandbutton clears the other controls.

The textbox goes to "".
The checkbox becomes unchecked.
The combobox goes back to ListIndex = 0. here is the code:

Sub CommandButton2_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
            Debug.Print ctl.Name
    [COLOR=red]'  This will print "Checkbox1"
               '  when it gets to the checkbox[/color red]
        If TypeOf ctl Is TextBox Then
    [COLOR=red]'  This will works fine[/color red]
            Debug.Print ctl.Name
            Me.Controls(ctl.Name).Text = ""
        ElseIf TypeOf ctl Is CheckBox Then
            [COLOR=red]Debug.Print ctl.Name  ' this does NOT
            Me.Controls(ctl.Name).Value = False[/color red]
        ElseIf TypeOf ctl Is ComboBox Then
    [COLOR=red]'  This will works fine[/color red]
            Debug.Print ctl.Name
            Me.Controls(ctl.Name).ListIndex = 0
        End If
    Next
End Sub

Nothing unusual about it. Except for this.

With IntelliSense when I type "TypeOf ctl Is" I get lots of choices, including Textbox, CheckBox, and ComboBox. Fine, this is as it should be. But the instructions for the checkbox NEVER fire. I am sure it something stupid, but what?

Second question. How do you get a string of an identifier? I thought I would get a Debug.Print of TypeOf itself. I can not seem to do so.

Thanks.

Gerry
My paintings and sculpture

 

[PHV]

What about TypeName ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

First of all, why would

TypeOf ctl Is TextBox Then

work;

TypeOf ctl Is ComboBox Then

work; BUT

TypeOf ctl Is CheckBox Then

NOT work, when all three were put in the procedure via the IntelliSense dropdown?

Ok, thanks PH. I have to say though that:

If TypeOf ctl Is TextBox Then
   Me.Controls(ctl.Name).Text = ""

ElseIf TypeName(ctl) = "CheckBox" Then
   Me.Controls(ctl.Name).Value = False

ElseIf TypeOf ctl Is ComboBox Then
   Me.Controls(ctl.Name).ListIndex = 0
End If

does in fact work. But why would a checkbox be different???




Gerry
My paintings and sculpture

 

[TonyJollans]

Hi Gerry,

Your problem is caused by not fully qualifying the reference.

What VBA does when faced with an incomplete reference such as Checkbox is to look through its (referenced) libraries to find out what precise object you are referring to; as soon as it finds a match it uses it. A checkbox on a UserForm is of type MSForms.Checkbox but the first reference found is for type Word.Checkbox and so you get a mismatch. This will work every time ...

[blue]If TypeOf ctl Is MSForms.Checkbox Then[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[fumei]

Ah, thank you. And THAT is why there are TWO Checkbox items in the the IntelliSense dropdown.

HOWEVER......

I tried with both. Neither worked, but OK, I understand that it is not fully qualified. I will start doing so out of principle. But I do find it odd that there is only ONE "textbox", ONE "combobox", but TWO "checkbox".

Is there not a Word.Textbox and a MSForms.Textbox?

Is there not a Word.Combobox and a MSForms.Combobox?

Why is a checkbox different?

Gerry
My paintings and sculpture

 

[TonyJollans]

Yes, it does indeed explain why there are two in the IntelliSense dropdown but whichever you choose only the text is copied into your code and it is resolved at compile time.

I have to agree that Word's object model is odd The Word objects are FormFields and there exist only types TextInput, Checkbox, and Dropdown so it is only with Checkbox that there is a clash with the name of the MSForms control.

More generally, of course, you may have any number of such clashes with all sorts of possible combinations of referenced libraries and it is always good practice to fully qualify all references. That one gets away with not doing so most of the time encourages one to think it should always be OK but each object model is developed in isolation and it is only at compile time that they meet each other.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[fumei]

And that is why I shall now fully qualify at all times.

Thanks Tony. And yes...Word is odd at times.

Gerry
My paintings and sculpture