Msgbox position on top of sheet

[hmk]

Hi there

is there a code to make a msgbox appear on the top of a sheet instead of the defult position ?

 

[pgc01]

Hi helmekki

I may be wrong but I think that there is no code to define the position of a mailbox.

However, if you really want it, you can create a very simple userform (maybe just a button and a label) and use it as your msgbox. In that case you can set the StartUpPosition to manual and define the initial position.

Hope this helps
PGC

 

[Derek]

Hi there

As an alternative, you might consider something like this:

Draw a rectangle on your sheet and call it "MB"
Format it to look like a message box (Mine is green with a blue border, blue size 14 font - aligned central)
Place it to fill row 1 for the width of your screen, then freeze frame (so it is always visible).

Assign this macro to the rectangle.

Sub HideMB()
ActiveSheet.Shapes("MB").Visible = False
End Sub

When you need to include a message in your macro, use code like this:

Sub SendMessage()
Dim str1 As String
str1 = "This is my message"
ActiveSheet.Shapes("MB").Visible = True
ActiveSheet.Shapes("MB").TextFrame.Characters.Text = str1
End Sub

You message box can be left on screen for changes of message, or it can be hidden by clicking it.

Hope this gives you some ideas
regards
Derek

 

[Tom Urtis]

is there a code to make a msgbox appear on the top of a sheet instead of the defult position ?

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

 

[hmk]

Thank u very much