×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

TextBox type not detected by code
2

TextBox type not detected by code

TextBox type not detected by code

(OP)
I have a simple UserForm (Excel VBA) with one textbox, one combo box, and a command button.

CODE

Option Explicit

Private Sub CommandButton1_Click()

Dim c As Control

For Each c In Me.Controls
    Debug.Print c.Name & " is a " & TypeName(c)
    
    If TypeOf c Is TextBox Then
        c.Value = ""
    End If
    
    If TypeOf c Is ComboBox Then
        c.Value = ""
    End If
Next c

End Sub 

When I run this code, ComboBox Type is detected with no problem, but the TextBox Type is not working, the code never gets to the red portion of my code. Even tho TypeName(c) shows TextBox.
What is going on?


---- Andy

There is a great need for a sarcasm font.

RE: TextBox type not detected by code

2
Excel library, with higher priority, has hidden TextBox control, representing old forms control. Full reference is necessary: If TypeOf c Is MSForms.TextBox Then

combo

RE: TextBox type not detected by code

(OP)
Thanks, that worked. smile

So, to be safe, would be better to Full reference any control this way:

If TypeOf c Is MSForms.TextBox Then

If TypeOf c Is MSForms.ComboBox Then


---- Andy

There is a great need for a sarcasm font.

RE: TextBox type not detected by code

Definitely, OptionButton, CheckBox, ListBox and TextBox are in both libraries. Similar purpose, different class names in excel: Button, DropDown, GroupBox.

combo

RE: TextBox type not detected by code

Interesting - I've never had an issue with the hidden legacy controls taking priority. Guess I've just been lucky. But yes, always best to be as explicit as possible when using TypeOf

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close