2

I am trying to return a value from a userform to another macro.

Here is an example of a piece of code that I want to return the value intMonth:

sub comparison()
    UserForm1.Show
end sub

then I have the userform code:

Private Sub initialize()
    OptionButton1 = False
End Sub

Private Sub OptionButton1_Click()
    intMonth = 1
    Me.Hide
End Sub

How do I get the intMonth value of 1 back to my original comparison() function?

Community
  • 1
  • 1
Mike Hill
  • 113
  • 1
  • 2
  • 13

2 Answers2

4

This is a minimal example, but should help.

screenshot of UserForm

In the UserForm:

Option Explicit
Option Base 0

Public intMonth As Long    ' <-- the variable that will hold your output

Private Sub initialize()
    OptionButton1 = False
    intMonth = 0
End Sub

Private Sub CommandButton1_Click()  ' OK button
    Me.Hide
End Sub

Private Sub OptionButton1_Click()
    intMonth = 1    '<-- set the value corresponding to the selected radio button
End Sub

Private Sub OptionButton2_Click()
    intMonth = 2
End Sub

In a module or ThisWorkbook:

Option Explicit
Option Base 0

Sub comparison()
    UserForm1.Show
    MsgBox CStr(UserForm1.intMonth)   ' <-- retrieve the value
End Sub
cxw
  • 16,685
  • 2
  • 45
  • 81
  • 2
    @MathieuGuindon Yep - the code as is also doesn't reset the radio buttons if `comparison` is called repeatedly without an editor reset between them :) . I was trying to keep the answer focused on the OP's stated issue, but agree with your point. My personal style is `Dim f as UserForm: set f = New UserForm` &c. – cxw Aug 21 '18 at 16:05
  • Thank you, i see what i was doing wrong now, i didnt have a variable declared in the userform code for it to return. Can i just confirm, you are saying rather than using UserForm1.Show, i should create an instance of the UserForm in my comparison () sub? – Mike Hill Aug 22 '18 at 08:55
  • @MikeHill good news! Yes, `UserForm1. Show` can cause you problems later, so `comparison` should create an instance. The second code sample in the article linked above by @MathieuGuindon shows an example. Happy hacking! – cxw Aug 22 '18 at 09:54
  • Yep, i read through it, and i think i actually understand it. Thanks for your help cxw – Mike Hill Aug 22 '18 at 10:23
0

Another useful way to achieve what you need is to wrap the code in a public function in the userform.

In the UserForm:

Option Explicit
Option Base 0

Private intMonth As Long

Public Function Choose_Option()
    OptionButton1 = False
    intMonth = 0
    Me.show()

    Choose_Option = intMonth 
End sub

Private Sub CommandButton1_Click()  ' OK button
    Me.Hide
End Sub

Private Sub OptionButton1_Click()
    intMonth = 1 
End Sub

Private Sub OptionButton2_Click()
    intMonth = 2
End Sub

Then in module, it is simple as this:

Option Explicit
Option Base 0

Sub comparison()
    MsgBox Userform1.Choose_Option() 
End Sub

This way, the function is in charge of showing the userform, prompting the user and returning the value.

If you debug this function, you will see that after Me.Show() is called, the function halts and continue only when the userform is hidden, which is done in the OK button.

Pedrusco
  • 31
  • 1
  • 6