6

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!

user954086
  • 61
  • 1
  • 1
  • 3
  • 1
    Buttons don't support the type of positioning you're trying to set. – Tim Williams Jan 15 '13 at 19:42
  • 1
    You could look into creating a custom button for the Ribbon. – Joseph Jan 15 '13 at 19:47
  • 5
    Perhaps try a form as the button... meaning have a modeless form as the button, set to a position to be at when sheet open event triggers... – bonCodigo Jan 15 '13 at 20:12
  • 1
    If you wanted the button on the top of the screen you could freeze the Range where the Button is. – kb_sou Jan 15 '13 at 23:53
  • I like @joseph4tw's idea. If you want to pursure that see this link http://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba/8852767#8852767 :@bonCodigo's idea is also good. So you have two options that you can choose from :) – Siddharth Rout Jan 16 '13 at 05:06
  • You might be able to achieve it, using "faked" scroll events - you can detect scrolling, check this link: http://www.cpearson.com/excel/DetectScroll.htm - from there you need to adjust the position of the button. However, the other three options seem much less effort... – Peter Albert Jan 16 '13 at 07:39

4 Answers4

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
baz
  • 31
  • 2
1

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:

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.

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
1

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

enter image description here

Eric Aya
  • 69,473
  • 35
  • 181
  • 253
Abderrahmane BECHIKH
  • 581
  • 7
  • 17
1

Right click → properties → placement → change to 3.

double-beep
  • 5,031
  • 17
  • 33
  • 41
TechAdept
  • 11
  • 1