vba Excel：固定按钮位置

Excel: Fixed Button Position

提问by user954086

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.

需要一些帮助在 Excel 工作表上附加 Excel/VBA 按钮。无论我如何滚动或缩放，我都需要它保持在屏幕上的相同位置。最好，我需要在屏幕的左下角或右下角。

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!

谢谢！

回答by baz

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:

我知道这篇文章很旧，但这里对任何人都可能有用。ActiveWindow 的 VisibleRange 属性可以解决这个问题。使用这样的东西：

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

回答by bonCodigo

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

这是我今天早些时候在评论中提出的想法:) 通常我们可以Floating User Form通过将表单的 Modal 属性设置为 0来获得 a ，这确实是一个Modeless状态。

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 InitializeEvent
• Whcih Events should keep it stick to the same position alive

• 表单的外观使其看起来像一个按钮（不显示标题栏/不可调整大小/隐藏关闭按钮等）
• 设置按钮的位置
• 哪个事件应该触发表单按钮（WorkBook Open）
• 你会用Form InitializeEvent做什么
• Whcih Events 应该让它保持在相同的位置活着

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.

• 您可能只想保持此按钮对您正在工作的工作簿可见，如果您打开工作簿的另一个实例，是否仍要保留该按钮

• 如果最小化 Excel Window 实例，您打算如何管理按钮的状态并使其保持可见

• 发布关于即使工作簿最小化也保持显示表单的帖子。

• 我碰巧看到的另一个很好的参考，（有点技术性）但值得一试 - 至少要了解您可以使用的某些属性/方法：扩展 VBA 用户表单控件。

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.

它们使您可以访问 VBA 或构成 VBA 项目的对象（用户窗体、工作簿等）不可用的功能。当您调用 API 时，您将绕过 VBA 并直接在 Windows 上调用。这意味着您无法获得 VBA 通常提供的安全机制，例如类型检查。如果您将无效值传递给 API 或（一个非常常见的错误）使用 ByRef 参数而不是 ByVal 参数，您很可能会立即完全崩溃 Excel，并且您将丢失所有未保存的工作。我建议在您确信 API 调用可靠之前，在调用 API 函数之前保存您的工作。

回答by Abderrahmane

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

在工作表的开头添加新行并在其上设置按钮，然后：冻结顶行