positioning message or input boxes

[TerryK]

How can I make sure the input or message box is centered over the worksheet or at least the excel window. I know it's possible to get the infor but I haven't been able to get the help system to give it to me.


Thanks in advance

Regards

Terry

 

[patrickmuldoon99]

This may be just me, but doesn't it center over the window by default?

Failing that, you can set x-pos and y-pos in your code like so:

tes = InputBox("hello world", "my title", "default text", 100, 200)

where the 100 is the x position, and the 200 is the y position.

HTH

 

[TerryK]

Perhaps I should have made the request clearer...

I know how to make the boxes appear in a specific position but I was trying to set the position relative to the Excel window. I need to know how to get the twips location of the top left of the Excel window if possible and the size so I can position the message box centrally. As I recall it is by default diaplyed in the center of the screen.

Thanks in anticipation and sorry I didn't make myself clearer earlier.

Regards

Terry

 

[TerryK]

Still trying to find method...

I'm sure it entails API calls to get the display context of the Excel window but though I used to have the code in earlier versions of VB I've not got a way to get at the old code cause I don't have the old versions of basic, and anyway the API calls may have altered with the advent of Windows XP.

If anyone can help to answer so this works the way I need it to I'd be very grateful.

Regards

Terry

 

[Tom Urtis]

Paste the following code in a standard module and run the macro named "FindMe" for an example of how to position message boxes.





Option Explicit

Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Public Declare Function UnhookWindowsHookEx Lib "user32" ( _
ByVal hHook As Long) As Long
Public Declare Function GetWindowLong Lib "user32" Alias _
"GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) _
As Long
Public Declare Function GetCurrentThreadId Lib "kernel32" () As Long
Public 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
Public 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
Public Declare Function GetWindowRect Lib "user32" (ByVal hwnd _
As Long, lpRect As RECT) As Long
Public Declare Function GetActiveWindow Lib "user32" () As Long
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Public Const GWL_HINSTANCE = (-6)
Public Const SWP_NOSIZE = &H1
Public Const SWP_NOZORDER = &H4
Public Const SWP_NOACTIVATE = &H10
Public Const HCBT_ACTIVATE = 5
Public Const WH_CBT = 5
Public hHook As Long
Public hXL As Long

Function TopRight(ByVal lMsg As Long, ByVal wParam As Long, _
ByVal lParam As Long) As Long
Dim rectXL As RECT, rectMsg As RECT
Dim x As Long, y As Long
Dim hMsgbox As Long
If lMsg = HCBT_ACTIVATE Then
hMsgbox = GetActiveWindow
GetWindowRect hXL, rectXL
GetWindowRect wParam, rectMsg
x = (rectXL.Left + (rectXL.Right - rectXL.Left) * 0.9) - _
((rectMsg.Right - rectMsg.Left) / 2)
y = (rectXL.Top + (rectXL.Bottom - rectXL.Top) * 0.1) - _
((rectMsg.Bottom - rectMsg.Top) / 2)
SetWindowPos wParam, 0, x, y, 0, 0, _
SWP_NOSIZE Or SWP_NOZORDER Or SWP_NOACTIVATE
UnhookWindowsHookEx hHook
End If
TopRight = False
End Function

Private Sub ShowMsgBoxTopRight()
Dim hInst As Long
Dim Thread As Long
hXL = FindWindow("XLMAIN", Application.Caption)
hInst = GetWindowLong(hXL, GWL_HINSTANCE)
Thread = GetCurrentThreadId()
hHook = SetWindowsHookEx(WH_CBT, AddressOf TopRight, hInst, Thread)
MsgBox "Wow, look at me up here !", , "Top right"
End Sub

Function BottomRight(ByVal lMsg As Long, ByVal wParam As Long, _
ByVal lParam As Long) As Long
Dim rectXL As RECT, rectMsg As RECT
Dim x As Long, y As Long
Dim hMsgbox As Long
If lMsg = HCBT_ACTIVATE Then
hMsgbox = GetActiveWindow
GetWindowRect hXL, rectXL
GetWindowRect wParam, rectMsg
x = (rectXL.Left + (rectXL.Right - rectXL.Left) * 0.9) - _
((rectMsg.Right - rectMsg.Left) / 2)
y = (rectXL.Top + (rectXL.Bottom - rectXL.Top) * 0.9) - _
((rectMsg.Bottom - rectMsg.Top) / 2)
SetWindowPos wParam, 0, x, y, 0, 0, _
SWP_NOSIZE Or SWP_NOZORDER Or SWP_NOACTIVATE
UnhookWindowsHookEx hHook
End If
BottomRight = False
End Function

Private Sub ShowMsgBoxBottomRight()
Dim hInst As Long
Dim Thread As Long
hXL = FindWindow("XLMAIN", Application.Caption)
hInst = GetWindowLong(hXL, GWL_HINSTANCE)
Thread = GetCurrentThreadId()
hHook = SetWindowsHookEx(WH_CBT, AddressOf BottomRight, hInst, Thread)
MsgBox "Hey, look down here !", , "Bottom right"
End Sub


Function TopLeft(ByVal lMsg As Long, ByVal wParam As Long, _
ByVal lParam As Long) As Long
Dim rectXL As RECT, rectMsg As RECT
Dim x As Long, y As Long
Dim hMsgbox As Long
If lMsg = HCBT_ACTIVATE Then
hMsgbox = GetActiveWindow
GetWindowRect hXL, rectXL
GetWindowRect wParam, rectMsg
x = (rectXL.Left + (rectXL.Right - rectXL.Left) * 0.1) - _
((rectMsg.Right - rectMsg.Left) / 2)
y = (rectXL.Top + (rectXL.Bottom - rectXL.Top) * 0.1) - _
((rectMsg.Bottom - rectMsg.Top) / 2)
SetWindowPos wParam, 0, x, y, 0, 0, _
SWP_NOSIZE Or SWP_NOZORDER Or SWP_NOACTIVATE
UnhookWindowsHookEx hHook
End If
TopLeft = False
End Function

Private Sub ShowMsgBoxTopLeft()
Dim hInst As Long
Dim Thread As Long
hXL = FindWindow("XLMAIN", Application.Caption)
hInst = GetWindowLong(hXL, GWL_HINSTANCE)
Thread = GetCurrentThreadId()
hHook = SetWindowsHookEx(WH_CBT, AddressOf TopLeft, hInst, Thread)
MsgBox "Now look up here !", , "Top left"
End Sub

Function BottomLeft(ByVal lMsg As Long, ByVal wParam As Long, _
ByVal lParam As Long) As Long
Dim rectXL As RECT, rectMsg As RECT
Dim x As Long, y As Long
Dim hMsgbox As Long
If lMsg = HCBT_ACTIVATE Then
hMsgbox = GetActiveWindow
GetWindowRect hXL, rectXL
GetWindowRect wParam, rectMsg
x = (rectXL.Left + (rectXL.Right - rectXL.Left) * 0.1) - _
((rectMsg.Right - rectMsg.Left) / 2)
y = (rectXL.Top + (rectXL.Bottom - rectXL.Top) * 0.9) - _
((rectMsg.Bottom - rectMsg.Top) / 2)
SetWindowPos wParam, 0, x, y, 0, 0, _
SWP_NOSIZE Or SWP_NOZORDER Or SWP_NOACTIVATE
UnhookWindowsHookEx hHook
End If
BottomLeft = False
End Function

Private Sub ShowMsgBoxBottomLeft()
Dim hInst As Long
Dim Thread As Long
hXL = FindWindow("XLMAIN", Application.Caption)
hInst = GetWindowLong(hXL, GWL_HINSTANCE)
Thread = GetCurrentThreadId()
hHook = SetWindowsHookEx(WH_CBT, AddressOf BottomLeft, hInst, Thread)
MsgBox "Look down to the left !", , "Bottom left"
End Sub

Sub FindMe()
Run "ShowMsgBoxTopRight"
Run "ShowMsgBoxBottomRight"
Run "ShowMsgBoxTopLeft"
Run "ShowMsgBoxBottomLeft"
MsgBox "Back in the center where I belong.", , "Home at last."
End Sub