Excel VBA Floating object

Question

Been all over the forums and not much luck with my problem. I want to create a custom button bar using shapes and then dock it at the bottom of Excel. This gets tricky because each user has a different monitor and screen resolution. After doing some research it seems I can use application.height and object.top to locate the button bar. The problem is that as I scroll up and down the page, I need the macro run on scroll. Is this even possible?

Answer 1

I can't think of any way to do it with shapes, but you could add a UserForm to your project, then add the buttons/labels/pictures you want to the form. By default, a form runs separately from the Excel sheets, so it's not impacted by scrolling, yet it stays on top of the Excel window.

To add a form, open the code window, right-click on your VBAProject and select Insert... => UserForm. You can use Application.Width, Application.Height, Application.Top and Application.Left to determine where on the screen the user has their Excel window and then use UserForm1.Width etc. to reposition the form as necessary to keep it where you want it (replace UserForm1 with whatever you name the form object).

You can have the form open to a specific location on the screen, but the users can move it around (I haven't tried locking a form's position before). You'll have to determine which events to monitor to reposition the form, if you so choose. Just remember that monitoring too many or too often will cause an overall performance hit for your users.