Mapping a variable from an Activex control - [VBA]

0
votes

Hi I am trying to use a publicly declared variable that has a value assigned to it upon clicking one of two optionbuttons inside a userform.

The userform does show and I can click either of the two buttons i have in the frame I created, but the variable does not pick up the click or actually I don't know what I'm doing with the userform controls. And i don't think the form hides either, clicking the optionbutton seems to be a dummy click, it highlights but does nothing after that.

Plus i'm not sure if the code itself needs to be placed on the userform' code section or the module code section, does it make a difference? Currently running it on the module code section.

Option Explicit

Dim C As String
Public Sub OptionButton1_Click()
C = "Delivery"
End Sub

Public Sub OptionButton2_Click()
C = "Holiday"
End Sub


Public Sub Optionbutton()
Userform1.Show
Sheet1.Cells(1, 1).Value = C
Userform1.Hide

End Sub

********--Edit-********* I guess Zack's method gets the job done, printing to Cell A1, but what I was really after is although the event handler needs to be private, running debug points out that the execution stops when the private sub ends, which could be optionbutton1 or optionbutton2's click event, which is what led me to believe the click was a dummy click.

But what I was really after is for the C variable to be available in the public sub that I execute from, is there a way for the execution to divert to the public sub after the C is assigned a value in the optionbutton private sub?

For example if the C was assigned in the private sub but my success criteria were defined by the successful execution of the public Sub defined above, because i have a larger macro where that variable will be used inside the public sub, and this is only part of the problem, or the problem I can't solve.

2
excelvba
C isn't publicly declared, it's local to whatever module this Dim C As String is written in. Use Public to make a variable public. Dim should be used for locals, and Private for module-scope variables. – Mathieu Guindon
The buttons' event handlers should be Private, in the form's code-behind. – Mathieu Guindon

2 Answers

0
votes

If I understand correctly, your form has two option buttons. Thus, your form's code-behind should look like this:

Option Explicit

Private Sub OptionButton1_Click()
    C = "Delivery"
End Sub

Private Sub OptionButton2_Click()
    C = "Holiday"
End Sub

This assumes there's a global variable C declared somewhere, in a standard module:

Option Explicit
Public C As String

Public Sub Optionbutton()
    Userform1.Show
    Sheet1.Cells(1, 1).Value = C
End Sub

Note that this displays the form modally, which means the next instruction only runs after the form is closed/destroyed. For that reason, there's no need for the subsequent Userform1.Hide call: the form is already gone by then.

Once you're comfortable with manipulating global variables from a form's code-behind, read UserForm1.Show (I wrote that article) for insight on a vastly more robust way to go about this.

-1
votes

Try this: You will need to tell the control where to place the value after the button is clicked.

Option Explicit
Dim C As String
Private Sub OptionButton1_Click()
    C = "Delivery"
    Sheet1.Cells(1, 1) = C
    Userform1.Hide
End Sub
Private Sub OptionButton2_Click()
    C = "Holiday"
    Sheet1.Cells(1, 1) = C
    Userform1.Hide
End Sub
Public Sub Optionbutton()
Userform1.Show vbModeless