MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Getting a range from a userform to use in VBA code


Posted by Neil Spencer on January 04, 2002 5:36 AM

I require a user to input a range of cells into a UserForm by either typing in the reference or (more likely) selecting with the mouse. I have set up the UserForm with a RefEdit where the cell reference will appear and all appears well - the user can select the range of cells in the RefEdit box without difficulty.

Where I am struggling is when I come to use this info that the user has input. I cannot work out how to tell the rest of my VBA program what range of cells was selected in the UserForm.

Many thanks for any help.

Neil Spencer


Posted by Dank on January 04, 2002 7:59 AM

Hello Neil,

Try this...

Sub GetRange()
Dim rnge As Range
Set rnge = Range(UserForm1.RefEdit1.Text)

'rnge now represents a Range object.
'E.g.
rnge.Interior.ColorIndex = 3
End Sub


Regards,
Daniel.