#Excel Short and Sweet Tip #26 (Showing an UserForm With a Worksheet UDF) by David Hager

 

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:

xlSS026_1

Have fun with this!

Advertisements

3 thoughts on “#Excel Short and Sweet Tip #26 (Showing an UserForm With a Worksheet UDF) by David Hager

  1. Pingback: #Excel Super Links #97 – shared by David Hager | Excel For You

  2. Pingback: #Excel: Generate a List of Antonyms Using an User-Defined Function (UDF) | Excel For You

  3. Jim Cone

    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.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s