passing a range to a user defined function using a form

[davek]

Is it possible to use a form created in vbe to pass a
range to a function. For example, on many Excel default
forms such as the pivot table wizard, a field will accept
a range which is ultimately passed on to a function. How
is that done? I want to use to supply the input range on
a form I define. What control and how do I do this.

Thanks in advance for any help!

Dave

 

[Rob van Gelder]

You're looking for the RefEdit control.

Beware, this control has quirks.

I've found it very difficult to set breakpoints an debug.
If you get an error in an event of the refedit, the form bombs.

Once you get used to the quirks, I've found refedit control to be very
handy.

One of the things I like to do it simplify the Range it sets though. Its
like the "external" way of referring to a range. eg. Sheet1!$A$1

I like to check if it's a valid range and if it is and it's on the
activesheet, then simply refer to it as $A$1. I do the checking in the _Exit
event of the RefEdit control.