Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
What version of Excel? If 2007 or higher, have you considered making a custom bar on the ribbon instead of a floating toolbar? –  techturtle Apr 25 '12 at 21:49
    
Not a bad idea. –  user130275 Apr 26 '12 at 11:17
add comment

1 Answer

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.

share|improve this answer
    
Thanks for the input –  user130275 Apr 26 '12 at 11:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.