|
|
|
date: Sat, 20 May 2006 20:02:53 -0400,
group: microsoft.public.word.vba.userforms
back
Re: Understanding MSForm Controls
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.
On Sat, 20 May 2006 20:02:53 -0400, "Greg Maxey"
<gmaxey@mvps.oSCARrOMEOgOLF> wrote:
>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
date: Sat, 20 May 2006 22:21:08 -0400
author: Jay Freedman
Re: Understanding MSForm Controls
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
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.
Jay Freedman wrote:
> 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.
>
>
>> 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
date: Sat, 20 May 2006 22:31:41 -0400
author: Greg Maxey RrOMEOgOLF
Re: Understanding MSForm Controls
On Sat, 20 May 2006 22:31:41 -0400, "Greg Maxey"
<gmaxey@mvps.oSCARrOMEOgOLF> wrote:
>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.
date: Sun, 21 May 2006 12:51:17 -0400
author: Jay Freedman
Re: Understanding MSForm Controls
Yes that works nicely. Thanks.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.
Jay Freedman wrote:
> On Sat, 20 May 2006 22:31:41 -0400, "Greg Maxey"
> <gmaxey@mvps.oSCARrOMEOgOLF> wrote:
>
>> 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
date: Sun, 21 May 2006 13:33:23 -0400
author: Greg Maxey RrOMEOgOLF
|
|
