Questions about "TypeOf" command

[village_alchemist]

Ok, this may be obvious, but if it weren't for you good folks here, I never would have found this. In fact, I've been searching through the help in Word 2000 and I *still* can't find reference to the TypeOf command. Is this some "top secret" Microsoft command, or is it in there somewhere and I just can't find it?

Now for the second - more curious question. In the following code, the textbox condition works, but the checkbox condition does not.
[VBA]
Dim ProjCtrl As Control

For Each ProjCtrl In frmItems.Controls
If TypeOf ProjCtrl Is TextBox Then
MsgBox ProjCtrl.Name, vbOKOnly, "Text Control Found"
ElseIf TypeOf ProjCtrl Is CheckBox Then
MsgBox ProjCtrl.Name, vbOKCancel, "Check Control Found"
End If
Next
[/VBA]

However, when I put "MsForms." in front of the control type everthing works:
[VBA]
Dim ProjCtrl As Control

For Each ProjCtrl In frmItems.Controls
If TypeOf ProjCtrl Is MsForms.TextBox Then
MsgBox ProjCtrl.Name, vbOKOnly, "Text Control Found"
ElseIf TypeOf ProjCtrl Is MsForms.CheckBox Then
MsgBox ProjCtrl.Name, vbOKCancel, "Check Control Found"
End If
Next
[/VBA]

I understand why it works with the MsForms in front, but could someone explain why without it the textbox works and the checkbox doesn't?

[Jacob Hilderbrand]

TextBox is a member of the Shapes class

CheckBox is a member of the CheckBox class

So perhaps it is equivilent to write Shapes with MSForms in front or not. But since the Checkbox has a seperate class it must be referenced with the MSForms.

All I can say is MicroSoft is nothing if not inconsistent.

[jamescol]

TypeName is probably a more reliable function to use. It should work regardless of the library used. TypeOf has many limitations when used with Controls, and specifying the library is one of them.

In your case, Excel cannot distinguish which library to which your Checkbox control belongs. That's why it works once you qualify it with the MSForms library. I'm not sure why the code can distinguish the TextBox control. The only thing I see is that there are fewer libraries with textbox controls than checkbox controls.

James

[TonyJollans]

Jacob and James have said most of it - TypeOf has its idiosyncracies! The default checkbox type is the one belonging to the word application (the type in a word form - not a userform, I think) whilst, because there isn't a textbox type in the application, the default textbox type is the first one it finds (the msforms one used in the userform). Clear as mud, I guess.

To answer your first question, you will find a mention of it - only a brief mention, mind - in the Help for the IF statement - I'm a bit surprised you didn't look there first - LOL

[village_alchemist]

Thanks guys, guess that pretty much covers it. I tried the TypeName() function and although the help file doesn't specifically state it will work on controls, it works just fine. Even better, unlike TypeOf, it works in case statements!

LOL Tony, maybe if I thought like Microsoft! But then again, if I did that my code would never work!

[jamescol]

LOL Tony, maybe if I thought like Microsoft! But then again, if I did that my code would never work!

Good rule of thumb: If the help file doesn't provide what you need, navigate to http://support.microsoft.com and search there. 99% of the time I find what I need between it and Help.

Since TypeName is just a string, you can actually use it anywhere you find it useful.

Tony - Interestingly, when I ran this code in Off2003, the TextBox object wasn't recognized. The only control TypeOf recognized without qualifying the library was CommandButton! Go figure.

In my mind, I envision the Office developers with a big cardboard Spinner used to determine these things? Guess CommandButton won this versions spin

Cheers,
James