Programming

[Guest]

How can I have user form return a value when the form closes

 

[Guest]

One way is with a public variable. You have to change the userform type from
private to public. Then declare a vailble in the VBA module window outside
you other functions and subroutines.

 

[Chip Pearson]

Create a Public variable in the userform's code module and set that variable
when you close the form. E.g.,

 

[Chip Pearson]

Create a Public variable in the userform's code module and set that variable
when you close the form. E.g.,

Public ReturnValue As Long

Private Sub btnClose_Click()
Me.ReturnValue = 123
Me.Hide
End Sub

Then, read the variable in the form with code right after you Show the
form:

Sub ShowForm()
Dim Res As Long
UserForm1.Show vbModal
Res = Userorm1.ReturnValue
Unload Userform1
MsgBox CStr(Res)
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)