how to change the location of Msg box

[aravindhan_31]

Hello guys,

I have created a vba code in excel. which shows a msgbox a particular sheets is active ( that is when u click sheet2 a message box appears) with some message.That message box appears on the middle of the sheet i wanted that to be appeared on top the the page or somewhere left top, or right top but not in the center.

kindly help me out to do this.


Arvind

 

[Tom Urtis]

Depending on where you want it (top right, top left, bottom right, bottom left) this code will give you an example for each of those choices.

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

 

[aravindhan_31]

Thanks a lot tom,

as im a beginner i couldnot edit anything in this. everything works fine, but each time i run this macro, all the four sides the message box poping up. i just want one side that is right middle of the.

can u please edit n send it to me .

regards
arvind

 

[Tom Urtis]

All 4 sides popped up because that was the nature of my example. Just toss out what you do not need and modify what you do need.

Example, this you wrote:

i just want one side that is right middle of the.

is not comprehendable but if by chance you want the message box to be on the right side of the screen, and centered vertically, this might be what you want:





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 CenterRight(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 = ActiveWindow.Height / 2
SetWindowPos wParam, 0, x, y, 0, 0, _
SWP_NOSIZE Or SWP_NOZORDER Or SWP_NOACTIVATE
UnhookWindowsHookEx hHook
End If
CenterRight = False
End Function

Sub ShowMsgBoxCenterRight()
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 CenterRight, hInst, Thread)
MsgBox "Hello from the right center of your screen !", , "Message Box position example"
End Sub