#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

One thought 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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s