Change event

[Bert]

I have a UserForm with four frames; each frame groups 5 OptionButtons and a
fifth frame has 6 OptionButtons.
Everytime a user clicks on one of the OptionButtons, I'd like a value to be
updated in a TextBox (depending on which OptionButton is clicked).
OptionButton1 adds 1 to the Textbox, OptionButton2 adds 2, etc. I know I
can do this using a Sub OptionButton1_Change() procedure (creating 26
procedures) but is there a more efficient way to do it?
Thanks.
Bert

 

[Leith Ross]

I have a UserForm with four frames; each frame groups 5 OptionButtons
and a
fifth frame has 6 OptionButtons.
Everytime a user clicks on one of the OptionButtons, I'd like a value
to be
updated in a TextBox (depending on which OptionButton is clicked).
OptionButton1 adds 1 to the Textbox, OptionButton2 adds 2, etc. I know
I
can do this using a Sub OptionButton1_Change() procedure (creating 26
procedures) but is there a more efficient way to do it?
Thanks.
Bert

Hello Bert,

There would have been if Microsoft had provided Control Arrays like
Visual Basic does. There are ways to simulate this, but I think all the
extra code needed to provide this type of functionality out weighs its
benefits.

Sincerely,
Leith Ross

 

[Bert]

Thanks, Leith.
I appreciate you insights. (I guess they were figuring cut-and-paste is
just as good. I was hoping though...)
At any rate, thanks for your response.
Bert

 

[Kenneth Hobson]

As Leith said, doing it is a bit more effort than it may be worth.

Here is one way using the Click event rather than a Change event in a Class.

1. Create your UserForm1 with the option buttons and TextBox1.
2. Add a class and use this code. Name the class in the project explorer as
cOptionButtons.

Private WithEvents obttn1 As MSForms.OptionButton

Sub SendOptionButton(ByVal obttn As MSForms.OptionButton)
Set obttn1 = obttn
End Sub

Private Sub obttn1_Click()
If obttn1.Value = True Then IncrementTB1 obttn1
End Sub

3. Add a Module with this code.

Sub IncrementTB1(ob As MSForms.OptionButton)
If UserForm1.TextBox1.Value = "" Then UserForm1.TextBox1.Value = "0"
UserForm1.TextBox1.Value = CStr(CDbl(UserForm1.TextBox1.Value) +
CDbl(ob.Tag))
End Sub

4. Add this code to UserForm1. The use of the Tag property makes this a bit
easier.

Dim ob() As cOptionButtons

Private Sub UserForm_Initialize()
Dim obj As MSForms.Control, i As Long

For Each obj In Me.Controls
If TypeName(obj) = "OptionButton" Then
i = i + 1
ReDim Preserve ob(i)
Set ob(i) = New cOptionButtons
ob(i).SendOptionButton obj
obj.Tag = i
End If
Next
End Sub

Private Sub CommandButton1_Click()
MsgBox TextBox1.Value
Unload Me
End Sub

 

[Bert]

Thanks, Kenneth.
I've taken the simpler route of creating a procedure to trap changes to each
OptionButton; however, I'm going to hold onto to your code and will include
it as I refine my "solution." Thanks for your help; I appreciate it!
Bert.