UserForms are great tools to add some “spice” to your Excel applications; however they seem rather limited and dull when compared to other “professional” programs written in C++ and other advanced programming languages.
Guess what! Visual Basic for Applications combined with the power of standard Windows API (Application Programming Interface) calls can pretty much achieve anything that your heart desires when it comes to UserForms.
Windows API calls are simply references to standard library functions that are available to anyone programming under the Windows environment. An excellent place to look at the myriads of utilities that can be developed using Windows API calls is at http://www.xcelfiles.com/APIIndex.html developed by Ivan Moala.
There are plenty of places on the Internet that explain how to call standard Windows API from Excel; one of the best ones can be found at http://www.informit.com/articles/article.asp?p=366892&rl=1 .
Stephen Bullen developed a Form Class in Excel that makes it very easy to modify your UserForm without having to understand the intricacies of Windows API calls. His site has a lot of goodies at http://www.oaltd.co.uk/Excel/Default.htm .
To hide the Close button of your UserForm becomes as simple as:
myForm.ShowCloseBtn = False
or to hide the Caption
myForm.ShowCaption = False
I added to this class the ability to change the transparency (opacity) of the UserForm and also to make the UserForm “draggable” even if the caption of the form is not shown.
Setting the opacity of the UserForm to semi-transparent is as simple as:
myForm.Opacity = 180
Opacity can be set from 0 (invisible) to 255 (normal). However, setting it to zero might create some problems…;-) I suggest a range of 80-255 would be plenty to experiment with.
Enabling the user to drag the form using the left mouse button, even if the caption is hidden is also a simple line of
myForm.SetDraggable
Making the form draggable is actually quite ingenious (and therefore has nothing to do with me!). It provides us VBA programmers a glimpse of how Windows works and it is worth explaining BRIEFLY.
Windows works based on messages it gets from a queue. Windows looks at the message queue and processes those messages one after the other. Well, luckily for us, we can send messages to this queue using Windows own SendMessage function. In fact, we can hijack the process and inject our own messages (hooks) into the queue. Note to aspiring hackers: this is not as easy to do as it sounds, especially if you want to send hooks to the queue for a window that your original process does not “own”.
But for us, trying to manipulate an Excel window (i.e., UserForm) from within Excel VBA, this is very doable.
So when we make the UserForm draggable we essentially do the following:
When the user presses down the left mouse button:
- Release the mouse and make it available for normal mouse input (in other words, ignore the fact that the left mouse button is clicked)
This is achieved by using the ReleaseCapture function of the user32.dll available to all Windows users.
- Send Windows a message that says: “Hey, the left mouse button is down on the userform’s caption!”
This is achieved by calling the SendMessage function of the user32.dll available to all Windows users. Of course you are tricking Windows to think that it is happening, when it is not. Pretty neat, huh?
Well, this is it.
Download
Why don’t you have a look at the code and the demo UserForm here?
What's Next?
My next project will be to add the SetShape method to the class so you can create transparent UserForms that can take any shape (oval, polygon, star, etc.). Yes, even a donut with a big hole in the middle! It probably does not come as a surprise that those all use Windows API calls. as well.
