How do I retrieve the option selected on a form?

[Trevor]

Hello All,

I have a form set up that is opened by a macro. Once the
user has selceted one of the option buttons, how can I
close the form, return to the original macro and use that
selection? The code snippet that I have so far is:

Sub RemarksReport()

UserForm1.Show

Workbooks.OpenText FileName:="G:\Collections\" &
UserForm1.ComboBox1.Value & ".txt", Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, 1), Array(7, _
1), Array(34, 1), Array(45, 1), Array(56, 1), Array
(68, 1), Array(73, 1), Array(85, 1), _
Array(97, 1), Array(109, 2)),
TrailingMinusNumbers:=True

.....
End Sub

Any help is much appreciated.

Trevor

 

[Dick Kusleika]

Trevor

You could use a public variable, but you shouldn't. What you should do is
put your macro behind the userform. So you'll have a macro in a standard
module that shows the form

Userform1.Show

and that's it. Then, on your form you probably have a commandbutton that
the user selects when they are done. In the click event of that
commandbutton, put the meat of your code. You can then refer to the
controls on the form like

Me.OptionButton1.Value

At the end of the click event, put this statement

Unload Me

to get rid of the form.