Excel Application.InputBox 位置

Question

对于这个函数的 Top 和 Left 参数，是否有一个中心屏幕选项，或者它总是一个数字？

我使用它而不是常规输入框，因为它可以更好地处理取消事件，但它总是出现在屏幕的右下角，这不太有用:/

Answer 1

没有中央屏幕选项。你得计算一下。但是，假设您使用的是 Excel 2007 或更高版本，还有另一个问题......

这对我来说是新闻，但在谷歌搜索和测试中，我看到在 Excel 2007 和 2010 Application.Inputbox 恢复到它的最后一个位置，而忽略了顶部和左侧设置。即使从新工作表调用新输入框，此问题似乎仍然存在。当我在 XL 2003 中尝试它时，它可以正常工作，并且输入框放置在正确的左右坐标处。

您可以将输入框拖动到您想要的位置，然后保存。除非有人稍后拖动它，否则它将在同一个地方重新打开。

这是 link to a solution这有助于某人恢复正确的行为，并且还解决了将输入框居中的问题。它确实需要 API 调用，因此请在尝试之前保存您的工作。

编辑 - 根据 JMax 的评论，这是上面链接中的代码。它是由 vbforums.com 网站上名为 KoolSid 的用户编写的:

Private Declare Function UnhookWindowsHookEx Lib "user32" _
(ByVal hHook As Long) As Long

Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long

Private Declare Function SetWindowsHookEx Lib "user32" _
Alias "SetWindowsHookExA" (ByVal idHook As Long, _
ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long

Private Declare Function SetWindowPos Lib "user32" _
(ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
ByVal x As Long, ByVal y As Long, ByVal cx As Long, _
ByVal cy As Long, ByVal wFlags As Long) As Long

'~~> Handle to the Hook procedure
Private hHook As Long

'~~> Hook type
Private Const WH_CBT = 5
Private Const HCBT_ACTIVATE = 5

'~~> SetWindowPos Flags
Private Const SWP_NOSIZE = &H1      '<~~ Retains the current size
Private Const SWP_NOZORDER = &H4    '<~~ Retains the current Z order

Dim InputboxTop As Long, InputboxLeft As Long

Sub TestInputBox()
    Dim stringToFind As String, MiddleRow As Long, MiddleCol As Long

    hHook = SetWindowsHookEx(WH_CBT, _
    AddressOf MsgBoxHookProc, 0, GetCurrentThreadId)

    '~~> Get the center cell (keeping the excel menus in mind)
    MiddleRow = ActiveWindow.VisibleRange.Rows.Count / 1.2
    '~~> Get the center column
    MiddleCol = ActiveWindow.VisibleRange.Columns.Count / 2

    InputboxTop = Cells(MiddleRow, MiddleCol).Top
    InputboxLeft = Cells(MiddleRow, MiddleCol).Left

    '~~> Show the InputBox. I have just used "Sample" Change that...
    stringToFind = Application.InputBox("Sample", _
    "Sample", "Sample", InputboxLeft, InputboxTop, , , 2)
End Sub

Private Function MsgBoxHookProc(ByVal lMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long

    If lMsg = HCBT_ACTIVATE Then
        '~~> Change position
        SetWindowPos wParam, 0, InputboxLeft, InputboxTop, _
        0, 0, SWP_NOSIZE + SWP_NOZORDER

        '~~> Release the Hook
        UnhookWindowsHookEx hHook
    End If

    MsgBoxHookProc = False
End Function