how to use VBA code to create 'Select Data Range' userform.

[John Smith]

Dear All,

I am trying to write a general code to do some data analysis.In the
first step, I want to have a 'Select Data Range' userform to allow
user select data into a summary page. Can any one help me on this?

Thanks

 

[isabelle]

hi John,

Set plage = Application.InputBox(prompt:="Do your selection in the summary page", Type:=8)

 

[isabelle]

to recover the address of selected range

Set rng = Application.InputBox(prompt:="Do your selection in the summary page", Type:=8)
Me.TextBox1 = rng.Parent.Name & "!" & rng.Address

 

[John Smith]

to recover the address of selected range

Set rng = Application.InputBox(prompt:="Do your selection in the summary page", Type:=8)
Me.TextBox1 = rng.Parent.Name & "!" & rng.Address

Hi Isabelle,

Thank you for your reply very much!

Your code works. But I am wondering whether I can have two input
fields, one is for users to type in data name, another field is used
to select data range.

Cheers

John

 

[isabelle]

here is the syntax for the InputBox Method

expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

0 formula
1 number
2 string
4 logical (True or False)
8 cell reference in the form of a Range object
16 error value, such as # N / A
64 array of values


Dim rng As Range, dataname As String

Set rng = Application.InputBox(prompt:="Do your selection in the summary page", Type:=8)
dataname = Application.InputBox(prompt:="type the name of data", Type:=2)

Me.TextBox1 = dataname
Me.TextBox2 = rng.Parent.Name& "!"& rng.Address

but i do not see why you can not used directly a texbox on userform