Understanding MSForm Controls

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg Maxey - 21 May 2006 01:02 GMT

I was helping another person today to list the name and caption of each optionbutton and checkbox in a userform. My code is shown below, but I don't understand some of the behaviour I observed while constructing it. My test userform had two optionbutton, two checkboxes and a command button:

The first problem I had was when the first option button was being processed it was added to the oButtonArray and then it unexpectedly passed the
"If TypeOf oControl is MSForms.Checkbox Then" line. I had to put in the GoTo statement to skip that conditional test if the control was an optionbutton. Why is the optionbutton considered both and optionbutton and a checkbox?

The second problem is while Dim oButtonArray() as OptionButton works perfectly fine. If I try Dim oCheckboxArray() as Checkbox then I get a Runtime Error 13 Type mismatch.

I am sure that I am missing something simple here. Can someone please help clear it up for me. Thanks.

Private Sub CommandButton1_Click()
Dim oButtonArray() As OptionButton
Dim oCheckboxArray() 'As CheckBox
Dim i As Long
Dim j As Long
Dim oControl As Control
For Each oControl In Me.Controls
If TypeOf oControl Is MSForms.OptionButton Then
ReDim Preserve oButtonArray(i)
Set oButtonArray(i) = oControl
i = i + 1
GoTo SkipRest
End If
If TypeOf oControl Is MSForms.CheckBox Then
ReDim Preserve oCheckboxArray(j)
Set oCheckboxArray(j) = oControl
j = j + 1
End If
SkipRest:
Next oControl
For i = 0 To UBound(oButtonArray)
ActiveDocument.Range.InsertAfter oButtonArray(i).Name & " " & oButtonArray(i).Caption & vbCr
Next i
For j = 0 To UBound(oCheckboxArray)
ActiveDocument.Range.InsertAfter oCheckboxArray(j).Name & " " & oCheckboxArray(j).Caption & vbCr
Next j
Me.Hide
End Sub

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Reply to this Message

Jay Freedman - 21 May 2006 03:21 GMT

Hi Greg,

This isn't something I already knew about, but your post prompted me
to look at it.

I can confirm that for an OptionButton control, both

TypeOf oControl Is MSForms.OptionButton

and

TypeOf oControl Is MSForms.CheckBox

return True. Although I think it's a bug, I will say that I've seen
someplace that I now forget (VB6? Visual C++?) where an optionbutton
is just a checkbox with a different set of properties. Somewhere under
the hood, VBA is using the same code to represent both controls and
just changing the appearance (round vs. square) and the exclusive
selection business. Somebody forgot to change the bit that returns the
TypeOf information. :-(

You can handle this without the GoTo by changing from two consecutive
If...End If blocks to one If...ElseIf...End If block:

For Each oControl In Me.Controls
If TypeOf oControl Is MSForms.OptionButton Then
MsgBox oControl.Name & " optionbutton"
ElseIf TypeOf oControl Is MSForms.CheckBox Then
MsgBox oControl.Name & " checkbox"
End If
Next

If you use this, stick in a big prominent comment saying that the test
for OptionButton must come first; if you get it the other way around,
every control of both types will say it's a checkbox and none of them
will be identified as optionbuttons.

I don't know what the problem is with the array declaration -- it
works perfectly well here.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

>I was helping another person today to list the name and caption of each optionbutton and checkbox in a userform. My code is shown below, but I don't understand some of the behaviour I observed while constructing it. My test userform had two optionbutton, two checkboxes and a command button:
>
[quoted text clipped - 33 lines]
>Me.Hide
>End Sub

Reply to this Message

Greg Maxey - 21 May 2006 03:31 GMT

Thanks Jay.

You mean if you remove the ' from this line of the code I posted you are not
getting a runtime error?

Dim oCheckboxArray() 'As CheckBox

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Hi Greg,
>
[quoted text clipped - 86 lines]
>> Me.Hide
>> End Sub

Reply to this Message

Jay Freedman - 21 May 2006 17:51 GMT

>Thanks Jay.
>
>You mean if you remove the ' from this line of the code I posted you are not
>getting a runtime error?
>
>Dim oCheckboxArray() 'As CheckBox

Hmmm -- different day, different behavior. Weird.

Pasting your code as-is into a new userform, I get a compile error
"Method or data member not found" on the line

ActiveDocument.Range.InsertAfter oCheckboxArray(j).Name & " " &
oCheckboxArray(j).Caption & vbCr

with ".Name" highlighted. Sure enough, IntelliSense doesn't list Name
as one of the properties of oCheckboxArray(j), which is strange
enough. But it doesn't list Name as a property of oButtonArray(i),
either, but the compiler doesn't complain about that.

At any rate, the userform doesn't get far enough to generate a runtime
error.

Let me suggest a different approach. Instead of storing the controls
themselves in the arrays, just store their names and captions in
arrays of strings. This works:

Private Sub CommandButton1_Click()
Dim oButtonArray() As String
Dim oCheckboxArray() As String
Dim i As Long
Dim j As Long
Dim oControl As Control
For Each oControl In Me.Controls
If TypeOf oControl Is MSForms.OptionButton Then
ReDim Preserve oButtonArray(1, i)
oButtonArray(0, i) = oControl.Name
oButtonArray(1, i) = oControl.Caption
i = i + 1
ElseIf TypeOf oControl Is MSForms.CheckBox Then
ReDim Preserve oCheckboxArray(1, j)
oCheckboxArray(0, j) = oControl.Name
oCheckboxArray(1, j) = oControl.Caption
j = j + 1
End If
SkipRest:
Next oControl
For i = 0 To UBound(oButtonArray)
ActiveDocument.Range.InsertAfter oButtonArray(0, i) _
& " " & oButtonArray(1, i) & vbCr
Next i
For j = 0 To UBound(oCheckboxArray)
ActiveDocument.Range.InsertAfter oCheckboxArray(0, j) _
& " " & oCheckboxArray(1, j) & vbCr
Next j
Me.Hide
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

Reply to this Message

Greg Maxey - 21 May 2006 18:33 GMT

Yes that works nicely. Thanks.

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

>> Thanks Jay.
>>
[quoted text clipped - 53 lines]
> Me.Hide
> End Sub

Reply to this Message

Jonathan West - 22 May 2006 12:56 GMT

> Thanks Jay.
>
> You mean if you remove the ' from this line of the code I posted you are
> not getting a runtime error?
>
> Dim oCheckboxArray() 'As CheckBox

This is a problem because the Word library has a CheckBox object as well as
the MSForms library. If you go to Tools References you will see that the
Word library is listed ahead of the Forms 2.0 library, which means that in
the event of a naming conflict, it is the Word library with takes
precedence. To get round this, you need to qualify the object type with the
library name, like this

Dim oCheckboxArray() As MSForms.CheckBox

Signature

Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Reply to this Message

Greg Maxey - 22 May 2006 13:21 GMT

Exactly. Thanks Jonathan.

Reply to this Message