Opening a user form from within a macro...

[Trevor]

Hello All,

I have a form which asks the user to select from three
filenames to use in the macro. I want to have the first
thing the macro does be to open the form, get the user's
selection, close the form, and continue running the rest
of the macro using that filename. My question is how to
open the form and then, once the user selects the option,
close the form and continue to the next command. Any help
would be much appreciated.

My basic code is as follows:


Sub RemarksReport()

UserForm1.Show

Workbooks.OpenText FileName:="G:\Collections\" &
ComboBox1.List(ComboBox1.ListIndex) & ".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



Thanks,

Trevor

 

[Nikos Yannacopoulos]

Trevor,

I have dealt with a similar situation in a slightly different way; I have
broken down the code to two macros, the first of which just opens the form.
Now on the form I have added an OK button, so the user makes their selection
and clicks on the OK button. The code behind the OK button assigns the
selection to a variable, hides the form and calls the second macro which
does all the rest, passing the selection (stored in that variable before the
form is closed) as a parameter to the second macro.

HTH,
Nikos