Reading Userform Object Values

Question

I created a Userform (manually in the VBA Projectbrowser). I have written VBA code, which fills this Userform with different Objects in runtime (Labels, Optionbuttons etc.). So far everything worked fine

The Userform is filled with data read from my Excel sheets and correctly displayed. However I'm not able to read the inputs from the objects on it (for example Optionbutton - TRUE or FALSE). These objects do not appear anywhere (except on the userform) so that I can link them and use them in another Module.

I guess they are only displayed and not really read into the memory or whatever (initialized !?).

score 1 · Answer 1 · edited May 23 '17 at 11:46

1

There are two ways to go about it.

WAY 1

Declare your option button object as Public.

Module Code

Public theOpBut As Object

Sub Fill()
    If theOpBut.Value = True Then
        ActiveSheet.Cells(1, 5) = 1
    Else
        ActiveSheet.Cells(1, 5) = "NO"
    End If
End Sub

Userform Code

Private Sub UserForm_Initialize()
    Set theOpBut = UserForm1.Controls.Add("Forms.optionbutton.1", "OptionButton", True)
        With theOpBut
        .Caption = "Test Button"
        '.GroupName = OpButGroupCounter
        .Top = 10
        .Left = 20
        .Height = 16
        .Width = 50
        .Font.Size = 12
        .Font.Name = "Ariel"
    End With
End Sub

Private Sub CommandButton1_Click()
    Call Fill
End Sub

WAY 2

Declare a Boolean Variable and create a click event of the Option button and then set the value of the Boolean Variable in that click event. To create the click event of the Option button at Run Time, see THIS EXAMPLE

You can then check the value of Boolean Variable in Sub Fill() and act accordingly.

edited May 23 '17 at 11:46

Community

1
1

answered Nov 10 '13 at 08:59

Siddharth Rout

147,039
17
206
250

Hi thanks for your response. I tried the first option you suggested and its seems to have just one problem. It always returns a FALSE from the option button. Shouldn't i somehow specify in the line "If theOpBut.Value = True Then" exactly the name of the respective optionbutton, since i may have more than one object of that type? – Dimitar Todorov Nov 11 '13 at 16:53
The above code is tried and tested :) I get `TRUE` when opt button is selected and `False` when it is not. – Siddharth Rout Nov 11 '13 at 16:57
Its strange why I always get a "NO"??! – Dimitar Todorov Nov 13 '13 at 06:25
May I see your file? If yes, then please upload it in www.wikisend.com and then share the link here. – Siddharth Rout Nov 13 '13 at 07:19

Reading Userform Object Values

1 Answers1