Excel: Fixed Button Position

Question

Needing some help attaching an Excel/VBA button on an Excel sheet. I need it to stay in the same position on the screen regardless of how I scroll or zoom. Preferably, I need this on the bottom left or right of the screen.

I have tried adding a button. Then, I right clicked on the button. Clicked on Format Controls -> Properties -> selected Don't Move or Size With Cells. Am I missing something that's making this not work?

Thanks!

Answer 1

Right click → properties → placement → change to 3.

Answer 2

Add new Row on the beginning of your WorkSheet and set your button on it, then: Freeze Top Row

Answer 3

I know this post is old, but here's to anyone it could be useful. The VisibleRange property of ActiveWindow can solve this problem. Use something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With ActiveSheet.OLEObjects("MY_BUTTON'S_NAME")
        .Top = ActiveWindow.VisibleRange.Top + ActiveWindow.VisibleRange.Height - 5
        .Left = ActiveWindow.VisibleRange.Left + ActiveWindow.VisibleRange.Width - .Width - 5
    End With
End Sub

Answer 4

Here is the idea that I put across the comment earlier today :) Typically we can get a Floating User Form by setting the Modal property of the form to be 0 which is indeed a Modeless state.

Basic Points to consider:

• Look & Feel of the form to make it look like a Button (Not show title bar/Not Resizable/ Hidden Close Button etc)
• Setting the position of the Button
• Which Event should trigger the form-button (WorkBook Open)
• What would you do with Form Initialize Event
• Whcih Events should keep it stick to the same position alive

Further Points to consider:

• You might only want to keep this button vissible for the workbook you are working, and if you open another instance of a workbook, do you still want to keep the button

• If you minimize the Excel Window instance, how do you plan to manage the state of the button and keep it visible

• Post about keep displaying a form even the workbook is minimized.

• One other great reference I happend to see, (little bit technical) but worth the shot - at least to get to know the certain properties/methods that you could make use: Extending VBA User Form Control.

The article include the following info, and please note the last line as well :)

They give you access to capabilities that are not available from VBA or from the objects (UserForms, Workbooks, etc.,) that make up a VBA Project. When you call an API, you are bypassing VBA and calling directly upon Windows. This means that you do not get the safety mechanisms such as type checking that VBA normally provides. If you pass an invalid value to an API or (a very common mistake) use a ByRef parameter instead of a ByVal parameter, you will most likely completely and immediately crash Excel and you will lose all your unsaved work. I recommend that until you are confident that your API calls are solid you save your work before calling an API function.