Position of MsgBox

[Otto Moehrbach]

Excel XP & 2007
An OP has asked me if there is a way to control the position of a MsgBox on
the screen. I know of no way to do that. Is that possible? Thanks for
your time. Otto

 

[Ken]

Otto

Someone may know how to control the position of a msgbox, but, it
would be pretty easy to control the position of a userform that looks
and acts like a msgbox; in case that will work.

Ken

 

[Gary''s Student]

See:

http://groups.google.com/group/microsoft.public.excel.programming/msg/67c6d98b98fb7aca

 

[Peter T]

I think Stratos posted that more as an academic exercise rather than as his
suggested way to position a msgbox, and very clever it is too. He also said
"Therefore my suggestion would be if you
really need to position a message box use a userform or a baloon instead."

He did explain the code was for use in XL97 and as written it will only work
in XL97, later versions will not find vba332.dll. That library is only
required as part of the workaround for AddressOf which was n/a in xl97. In
later versions, in the function fncMsgBox_Pos97 include the following.

#If VBA6 Then
TempHook = SetWindowsHookEx _
( _
idHook:=WH_CBT, _
lpfn:=AddressOf cbkPositionMsgBox, _
hmod:=GetWindowLong(0, GWL_HINSTANCE), _
dwThreadId:=GetCurrentThreadId() _
)

#Else
TempHook = SetWindowsHookEx _
( _
idHook:=WH_CBT, _
lpfn:=AddrOf("cbkPositionMsgBox"), _
hmod:=GetWindowLong(0, GWL_HINSTANCE), _
dwThreadId:=GetCurrentThreadId() _
)
#End If

If no need to cater for xl97 remove the conditional #If, the AddrOf
function, and Declare Function GetCurrentVbaProject from the top of the
module.

Follow instructions carefully about where to put code and how to run (not in
the IDE, at least don't try to step through). Don't forget to add the
FindWindow API that was initially overlooked.

Regards,
Peter T

 

[Gary''s Student]

Peter:

Might it be possible to first create the MsgBox (where ever Excel want to
put it) and then move it to some location with VBA??

 

[Otto Moehrbach]

Yes. How is that done? Otto

Peter:

Might it be possible to first create the MsgBox (where ever Excel want to
put it) and then move it to some location with VBA??

 

[Gary''s Student]

When I thought about the problem with more coffee in me, I realized that
there is a sort of Catch-22. Once the VBA has popped up the MsgBox, it has
lost focus ("sleeping"), waiting for the user to respond. Anyway here is
another reference:

http://groups.google.com/group/micr...a/0526b8c4f39aa019?lnk=st&q=#0526b8c4f39aa019

 

[Peter T]

As you say, code is suspended until the msgbox is dismissed. Did either of
you actually try the demo by Stratos. I only ask because your similar
questions implied either you didn't or you couldn't get it to work. It
worked fine for me after adapting for post xl97 versions along the lines I
suggested.

Although the demo in the link below (by Jim Rech) looks different, in
essence the hook method is pretty much the same. Jim's includes an
extra"feature" in that it returns the size of the msgbox, so with a bit of
math's would allow the msgbox to be centred over a userform, for example.

Regards,
Peter T