How show msgbox in created Word Document

[stranno]

Hi,

In an Excel module i create a Word Document. When the document opens, there should simultaniously appear a MsgBox (or UserForm) with instructions about de Word document, however the MsgBox or Userform is blocked by the Word document and shows up in the inactive Excel sheet. Does someone know a solution for this problem?

[vba]
Sub MsgBoxInDocu()
Dim wdApp As Object
Dim wdDoc As Object

On Error Resume Next
'Get existing instance of Word if it exists.
Set wdApp = GetObject(, "Word.Application")
If Err <> 0 Then
'If GetObject fails, then use CreateObject instead.
Set wdApp = CreateObject("Word.Application")
Err.Clear
End If

Set wdDoc = wdApp.Documents.Open("C:\Test\TestDocument.doc")
wdApp.Visible = True

Application.ActivateMicrosoftApp xlMicrosoftWord
wdApp.ActiveWindow.WindowState = wdWindowStateMaximize
End Sub

[/vba]

Regards and thanks in advance,
Stranno

[snb]

This suffices:
[vba]
Sub M_snb()
with Getobject("C:\Test\TestDocument.doc")
.Visible = True
end with
End Sub

[/vba]

If the Word document contains:

[vba]
Private Sub Document_Open()
userform1.show
End Sub
[/vba]
It will show you the userform in the Word document.

If you want to suppress the visibility of Excel you can use:

[vba]
application.visible=false
[/vba]

[stranno]

Hello Snb,

Thanks for answering my question. But I think I have tried this before. Unfortenately without result. Have you tried this? Tomorrow I Will check it again. I'll let you know if this is what i'am looking for. Thanks again.

Regards,
Stranno

[SamT]

Show the MsgBox first. When they close the MB, create wdApp.

[stranno]

SamT, That's what i do now. But I prefer to show Document and MsgBox (or UserForm) simultaniously (people have have a short memory).

Snb, suppose the document doesn't exist yet. So instead of


[vba]
Getobject("C:\Test\TestDocument.doc")
[/vba]

You create a new document via:

[vba]
Set wdApp = CreateObject("Word.Application")
wdApp.Visible
Set wdDoc = wdApp.Documents.Add
[/vba]

What is in that case the solution?
Because actually that's the case in my macro.

regards,
stranno

[stranno]

Snb,

[VBA]
Sub M_snb()
With Getobject("C:\Test\TestDocument.doc")
.Visible = True
End With
End Sub
[/VBA]

generates an error (438). ".visible = true" seems not to be a valid method.

Other suggestions maybe?

Regards, Stranno

[stranno]

This works fine:
[VBA]
Sub n()
Dim wdApp As Object
Dim wdDoc As Object
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open("C:\Test\TestDocument.doc")
wdApp.Visible = True
End Sub
[/VBA]

And in TestDocument.doc:
[VBA]
Private Sub Document_Open()
UserForm1.Show vbModeless
End Sub
[/VBA]

But how to achieve this when creating a new document?

[snb]

You can't show a userform in a new document that doesn't contain a userform.

other question:
[vba]
Sub M_snb()
With Getobject("C:\Test\TestDocument.doc")
.application.Visible = True
End With
End Sub
[/vba]

If you want to create a new Word document use simply

[vba]
with createobject("Word.document")

end with
[/vba]

Please forget all those redundant object variables.

[Jan Karel Pieterse]

You could use a userform which you set to "always on top" using a bit of API code. you need these declarations:

[VBA]#If VBA7 Then
Dim mhwndForm As LongPtr 'The userform's window handle
Private Declare PtrSafe Function FindWindow32 Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As LongPtr
Private Declare PtrSafe Sub SetWindowPos Lib "USER32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, _
ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
ByVal cy As Long, ByVal wFlags As Long)
Private Const HWND_TOPMOST As LongPtr = -1
Private Const HWND_NOTOPMOST As LongPtr = -2
#Else
Dim mhwndForm As Long 'The userform's window handle
Private Declare Function FindWindow32 Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Sub 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)
Private Const HWND_TOPMOST As Long = -1
Private Const HWND_NOTOPMOST As Long = -2
#End If
Private Const SWP_NOSIZE As Long = &H1
Private Const SWP_NOMOVE As Long = &H2
Private Const SWP_NOACTIVATE As Long = &H10
Private Const SWP_SHOWWINDOW As Long = &H40
[/VBA]

This goes at the top of the form's code module
Then somewhere where the form initialises:
[VBA]Public Sub MakeTopMost()
#If VBA7 Then
Dim lngParm As LongPtr
#Else
Dim lngParm As Long
#End If
mhwndForm = FindWindow32("ThunderDFrame", Me.Caption)
lngParm = IIf(mhwndForm, HWND_TOPMOST, HWND_NOTOPMOST)
SetWindowPos mhwndForm, lngParm, 0, 0, 0, 0, (SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE)
End Sub[/VBA]

[stranno]

Hello Jan Karel,

This is just where I was looking for. But I didn't know how to obtain the window handle of an userform. I will test it and let you know if it works for me.

Regards,
Stranno

[stranno]

Hi JKP,

I have tested your proposal. Unfortunately i expected something else. The Userform becomes not visible after i created a Word document and/or it blocks the activation of the document (depending on the order i want them to appear). I hoped that the document would appear and immediately after that the userform (both visible at the same time).

[vba]
Sub Create_Word_Applic()
Dim wdApp As Object
Dim wdDoc As Object

Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.documents.Add
wdApp.Visible = True
Application.ActivateMicrosoftApp xlMicrosoftWord
wdDoc.ActiveWindow.WindowState = wdWindowStateMaximize
UserForm1.Show
End Sub
[/vba]
Obviously the only way to achieve this:
1 - Prepair an 'empty' Document (which contains the userform) and store it in a folder
2 - Open an exact copy (copyfile) via: set wdDoc = dApp.open(Documents Fullpathname)

thanks and regards,
Stranno

[stranno]

Oeps, did i forget to call the userform window?
How do i do that?

[snb]

Store the attachments in the same folder.
Open the Excel file; click the button. See what happens.

[snb]

See also previous post.
The second attachment (in this forum the max. number of attachmnts appears to be 1)

[Jan Karel Pieterse]

I like it self-contained, rather than having a separate word file. See attached.

[Kenneth Hobs]

The topmost API method is a good approach.

I see this problem as two issues mainly.
1. MsgBox is always Modal so use a modeless method.
2. Focus and timing can play a factor. Try closing Excel after running the routines a few times. An initial run may not always run as the next run does.

Here is an API method for a modeless MessageBox. Notice that I used the system modal parameter so that clicking outside the MessageBox did not remove it from foreground focus.

In a Module:
[VBA]' http://www.tek-tips.com/faqs.cfm?fid=4699
Private Declare Function MessageBox _
Lib "User32" Alias "MessageBoxA" _
(ByVal hWnd As Long, _
ByVal lpText As String, _
ByVal lpCaption As String, _
ByVal wType As Long) _
As Long


Sub MsgBoxInDocu2()
Dim wdApp As Object
Dim wdDoc As Object
Dim wdWindowStateMaximize As Integer

wdWindowStateMaximize = 1

On Error Resume Next
'Get existing instance of Word if it exists.
Set wdApp = GetObject(, "Word.Application")
If Err <> 0 Then
'If GetObject fails, then use CreateObject instead.
Set wdApp = CreateObject("Word.Application")
Err.Clear
End If

Set wdDoc = wdApp.Documents.Open("C:\myfiles\excel\msword\a.doc")
wdApp.Visible = True

Application.ActivateMicrosoftApp xlMicrosoftWord
wdApp.ActiveWindow.WindowState = wdWindowStateMaximize

MessageBox &H0, "This is a native Message Box", "My Box", vbSystemModal
End Sub
[/VBA]

[stranno]

Snb
Where is "M_snb"? in application.run "M_snb"
You ask me: "Why so complicated". I think the answer is: because people are demanding creatures .
Anyway, your code sometimes worked and sometimes not. The disadvantage is that you need to prepair and store a document in advance.

JKP
I can't get it working. The document and msgbox are not visible at the same time in the same screen. Have you tested it youself?

Kenneth,
Your solution works fine. I 'am delighted .

Gentlemen, many thanks for your contributions.

Regards,
Stranno

[stranno]

Yet one thing Kenneth.

I noticed that the sequence is decisive!
This is the right order:

1) Open (new) Document
2) Make Word Application visible
3) Application.ActivateMicrosoftApp xlMicrosoftWord
4) wdApp.ActiveWindow.WindowState = wdWindowStateMaximize
5) MessageBox &H0, " ........ "

If you change the order, it won't work. Maybe a piece of cake for you, but handy to know for lesser VBA god's (like myself).

stranno

[snb]

M_snb is in the document's macromodule.
The macro will only be run if you open the Word Document. If it's open already nothing will happen.

I think KH's suggestion can also be achieved using:

[vba]
Sub snb()
Application.ActivateMicrosoftApp 1
GetObject("", "Word.document").Application.Visible = True
CreateObject("WScript.Shell").Popup "De gegevens worden ingelezen", , "Snb", 4096
End Sub
[/vba]

[Kenneth Hobs]

When we read someone's request for help, we have to makes some assumptions unless we want to wait for clarification. SNB assumed that you wanted to run MSWord macro code for the Userform. JKP read it another way. I of course tried to offer a 3rd solution for giggles.

SNB's code should work fine if you open his DOCM file and Enable Editing to enable the macros, save the file, and close it.

You may or may not want to add the vbModeless option to snb's DOCM file's ThisDocument. As you can see in that file, he put the userform in that file rather than Excel.

DOCM's ThisDocument code modification.
[VBA]Sub M_snb()
scherm.Show vbModeless
End Sub
[/VBA]