There’s a cool feature in PowerPoint that allows you to see all of your slides in sequence, move them around, and double-click to edit them. I don’t know what it’s called, but it usually appears at the left side of your screen in PowerPoint, like this:
On a recent Excel project, my client had an idea to implement a similar feature in their Excel workbook. In this case, the user is sometimes working with dozens, or even hundreds, of Excel worksheets in a single workbook. To make it easier for them to move from one to another, and to resequence them, they asked me to implement a “Control Panel” feature.
Here's their vision:
The sample shown above contains 14 worksheets, including the table of contents (shown), cover sheet, cover letter, and several relevant forms. This sample is the tip of the iceberg – they often have dozens of forms for each project.
That’s where the Control Panel, shown at the right side of the screen shot, comes in handy. It stays on top, no matter which worksheet is active. The user can click on any form name to activate that worksheet. They can drag and drop a form name to move it before or after another sheet.
From a technical standpoint, some of the most interesting features of this Control Panel are:
Ability to have a panel stay on top of all worksheets
Dynamically populate the Control Panel with all of the worksheet names
Drag and drop to resequence the worksheets
Automatically assign page numbers to worksheets
I’ll take each of these interesting ideas and explain them in separate posts. Today, let’s look at the “Stay on Top” capability.
Stay on Top
You can see in the image above that the control panel is shown on top of the Contents worksheet. Below, I’ve activated the AB Form worksheet, and the Control Panel is still on top:
To make this panel stay on top, I implemented it as a User Form. If you’ve worked with User Forms before, you know that they usually pop up when they are needed, and you can’t go back to your worksheets until you close them. That’s because they are modal by default. In the Excel world, “modal” means that when the form is open, it’s the only thing that is active in Excel. Everything else is inactive (and therefore can’t be used) until you close the form.
When you create the form, though, there is a property you can change to alter that behavior:
See the property I highlighted, called “ShowModal”? If you change that to “False” the form is no longer modal. Now you can keep the form open, and still be able to work on your worksheets. So, while this user form is open I can:
Click on various worksheets to activate them
Enter text and images on those worksheets
Access the ribbons and their buttons
Save the workbook
And do everything else you can normally do in Excel
Pretty cool, right? Just add a little code to control the position of the form, and voila! An always-on-top control panel.
PRO TIP: You can also allow the user to resize the UserForm by using three APIs:
GetForegroundWindow
GetWindowLong
SetWindowLong
I can’t take credit for coming up with this one. I’ve got the “Mr. Excel” website to thank for that: https://www.mrexcel.com/board/threads/resize-a-userform.485489/. Scroll down to the post by Leith Ross to see a code sample using these APIs. Simple and elegant!
I hope you find this useful, and are able to implement it in an Excel application of your own! Don’t hesitate to reach out with questions.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© 2021 North Port Solutions, LLC. All right reserved.