When looking at the vagaries of a Worksheet UDF, it appeared to me that anything viewed by Excel as an object could be invoked by the UDF. So, I thought, what is a large Excel object that may not work with this methodology? After a while, I thought of an Userform. Surely, I cannot show an Userform in this way. But, I WAS WRONG!
Open a new workbook and go to the Visual Basic Editor (VBE) and add an Userform. Then, add a standard module and add this code to it.
Function UForm()
UserForm1.Show
End Function
Then enter this formula in cell A1.
=UForm()
And presto, the userform appears!
Obviously, any userform, including those of your elaborate design, can be shown in this way. To have this UDF run when a specific cell is recalculated (in this case, A2), you can use a formula like:
=A2&UForm()
as shown in the following figure:
Have fun with this!
Pingback: #Excel Super Links #97 – shared by David Hager | Excel For You
Pingback: #Excel: Generate a List of Antonyms Using an User-Defined Function (UDF) | Excel For You
Re: Showing a Userform from a worksheet udf
That is going to be very useful, as it allows some pre xl2013 add ins to be used in newer XL versions while avoiding the Ribbon and xml code. Thank you.