How to: User Form to assign a user defined range to a macro variab
 

I want to be able to select a range of cells with a user form (using the
RefEdit control) and then pass or assign the user defined range address to a
range varaible in a seperate VB macro. I want to be able to hold the user
defined range in a range variable so I can reference multiple times within
the/a "formatting macro".

I have figured out how to display the form and get the range address
assigned inside the VB code for the user (CommandButton1 click Sub) but I can
not figure out how to assign or pass the range value out of the user form to
the range variable in the "formatting macro". I think if I put all the
"formatting macro" code in the Sub CommandButtion1_click() it would work or
this specific application or task.

Ideally I would like the UserForm to be "generic" of sorts, that way I can
call it from inside any macro I have (current or future) that performs
formating on each cell in the range or copying/moving/tranposing etc. the
range as a whole.

I think the issue I am having is a miss match of varaible types or not
setting the correct parameters when referencing variables with repsect to
their "scope". It also could be something simple that I am just over looking
- VB is a little new to me. Any assistance or light that could be shed on
this "hair wringer" is much appreciated. Thanks in advance.

How to: User Form to assign a user defined range to a macro variab
 

Rather than unload the userform, hide it where you would unload it.

then in the other code you can so

sAdd = Userform1.Regedit1.Value
set rng = Activesheet.Range(sAdd)
unload userform1

---------------------
General Module:
Sub Showform()
Dim sAdd As String
Dim rng As Range

UserForm1.Show
sAdd = UserForm1.RefEdit1.Value
Set rng = ActiveSheet.Range(sAdd)
Unload UserForm1
rng.Select
End Sub

---------------------
Userform Module:
Private Sub CommandButton1_Click()
Me.Hide
End Sub

As an example (worked for me)

--
Regards,
Tom Ogilvy

"TrevTrav" wrote in message
...
I want to be able to select a range of cells with a user form (using the
RefEdit control) and then pass or assign the user defined range address to
a
range varaible in a seperate VB macro. I want to be able to hold the user
defined range in a range variable so I can reference multiple times within
the/a "formatting macro".

I have figured out how to display the form and get the range address
assigned inside the VB code for the user (CommandButton1 click Sub) but I
can
not figure out how to assign or pass the range value out of the user form
to
the range variable in the "formatting macro". I think if I put all the
"formatting macro" code in the Sub CommandButtion1_click() it would work
or
this specific application or task.

Ideally I would like the UserForm to be "generic" of sorts, that way I can
call it from inside any macro I have (current or future) that performs
formating on each cell in the range or copying/moving/tranposing etc. the
range as a whole.

I think the issue I am having is a miss match of varaible types or not
setting the correct parameters when referencing variables with repsect to
their "scope". It also could be something simple that I am just over
looking
- VB is a little new to me. Any assistance or light that could be shed on
this "hair wringer" is much appreciated. Thanks in advance.