PDA

View Full Version : How show msgbox in created Word Document



stranno
06-26-2013, 12:29 PM
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?


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



Regards and thanks in advance,
Stranno

snb
06-26-2013, 01:24 PM
This suffices:

Sub M_snb()
with Getobject("C:\Test\TestDocument.doc")
.Visible = True
end with
End Sub



If the Word document contains:


Private Sub Document_Open()
userform1.show
End Sub

It will show you the userform in the Word document.

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


application.visible=false

stranno
06-26-2013, 02:37 PM
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
06-26-2013, 02:59 PM
Show the MsgBox first. When they close the MB, create wdApp.

stranno
06-26-2013, 11:46 PM
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



Getobject("C:\Test\TestDocument.doc")


You create a new document via:


Set wdApp = CreateObject("Word.Application")
wdApp.Visible
Set wdDoc = wdApp.Documents.Add


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

regards,
stranno

stranno
06-27-2013, 12:20 AM
Snb,


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


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

Other suggestions maybe?

Regards, Stranno

stranno
06-27-2013, 12:58 AM
This works fine:

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


And in TestDocument.doc:

Private Sub Document_Open()
UserForm1.Show vbModeless
End Sub


But how to achieve this when creating a new document?

snb
06-27-2013, 01:44 AM
You can't show a userform in a new document that doesn't contain a userform.

other question:

Sub M_snb()
With Getobject("C:\Test\TestDocument.doc")
.application.Visible = True
End With
End Sub


If you want to create a new Word document use simply


with createobject("Word.document")

end with


Please forget all those redundant object variables.

Jan Karel Pieterse
06-27-2013, 08:00 AM
You could use a userform which you set to "always on top" using a bit of API code. you need these declarations:

#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


This goes at the top of the form's code module
Then somewhere where the form initialises:
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

stranno
06-27-2013, 09:20 AM
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
06-28-2013, 02:46 AM
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).


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

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
06-28-2013, 03:34 AM
Oeps, did i forget to call the userform window?
How do i do that?

snb
06-28-2013, 03:58 AM
Store the attachments in the same folder.
Open the Excel file; click the button. See what happens.

snb
06-28-2013, 03:59 AM
See also previous post.
The second attachment (in this forum the max. number of attachmnts appears to be 1)

Jan Karel Pieterse
06-28-2013, 04:41 AM
I like it self-contained, rather than having a separate word file. See attached.

Kenneth Hobs
06-28-2013, 07:14 AM
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:
' 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

stranno
06-28-2013, 10:40 AM
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 :hi:.
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?:think:

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

Gentlemen, many thanks for your contributions.

Regards,
Stranno

stranno
06-28-2013, 11:27 AM
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
06-28-2013, 01:33 PM
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:


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

Kenneth Hobs
06-28-2013, 01:50 PM
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.
Sub M_snb()
scherm.Show vbModeless
End Sub

Kenneth Hobs
06-28-2013, 02:07 PM
We can always count on snb to post short code. Short code is not bad but then I don't think that longer code versions are always bad either. Learning more than one way to solve a problem can be a good thing.

As a last contribution to help you with other Exel<->MSWord projects:
'TypeText method
' http://www.excelforum.com/excel-programming/650672-populate-word-document-from-excel.html#post1946784
' http://www.excelforum.com/showthread.php?p=1946784
' http://vbaexpress.com/forum/showthread.php?p=169877
' http://vbaexpress.com/forum/showthread.php?t=24693
' http://www.excelforum.com/excel-programming/791302-excel-to-word-paragraph-and-page-setup.html

'Copy from Excel, paste to Word
'Lucas, http://vbaexpress.com/forum/showthread.php?p=178364

'FormFields
' http://www.mrexcel.com/forum/showthread.php?p=1639696
' http://www.mrexcel.com/forum/showthread.php?t=333200
' http://www.excelforum.com/excel-programming/799070-import-text-fields-from-word.html
' Content Controls
' http://www.vbaexpress.com/forum/showthread.php?t=39654

'Add Hyperlink to Bookmark
' http://www.excelforum.com/excel-programming/664078-use-excel-vba-to-add-a-hyperlink-to-a-word-document.html#post2006430
'Steiner, http://www.vbaexpress.com/kb/getarticle.php?kb_id=126
'Colin_L, http://www.mrexcel.com/forum/showthread.php?t=358054

'Save OLEObject as MSWord Document
' http://vbaexpress.com/forum/showthread.php?t=21619

'Add Table to MSWord
' http://vbaexpress.com/forum/showthread.php?t=23975
' http://vbaexpress.com/forum/showthread.php?p=168731

'Import Word Tables
'vog, http://www.mrexcel.com/forum/showthread.php?t=382541
'Ruddles, http://www.mrexcel.com/forum/showthread.php?t=524091

'snb, Word Tables
' http://www.vbaexpress.com/forum/showthread.php?t=45520
' http://www.vbaexpress.com/forum/showthread.php?t=46472

'Save OLEObject as MSWord DOC
' http://vbaexpress.com/forum/showthread.php?t=21619

'Get Optionbutton info from MSWord DOC
' http://vbaexpress.com/forum/showthread.php?t=22454

'FindReplace Text
' http://www.excelforum.com/excel-programming/682014-replace-word-in-ms-word-with-varable-from-ms-excel.html
' http://www.vbaexpress.com/forum/showthread.php?t=38958
' http://www.vbaexpress.com/forum/showthread.php?p=250215
' http://www.vbaexpress.com/forum/showthread.php?t=42833
' http://support.microsoft.com/kb/240157
' http://word.tips.net/T001833_Generating_a_Count_of_Word_Occurrences.html

' http://www.excelforum.com/excel-programming/794297-struggling-with-a-find-replace-macro-to-word.html

'Bookmarks
' http://vbaexpress.com/forum/showthread.php?p=185718
'Colin_L, http://www.mrexcel.com/forum/showthread.php?t=358054
' http://www.vbaexpress.com/forum/showthread.php?p=253277

'Mail Merge
' http://www.excelforum.com/excel-programming/796614-mail-merge-from-excel.html
' http://www.excelforum.com/excel-programming/798299-print-mail-merge-document.html
'Word 's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). _
To see how to group records with any mailmerge data source supported by Word, _
check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
' http://lounge.windowssecrets.com/index.php?showtopic=731107
' or
' http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

Jan Karel Pieterse
06-29-2013, 12:54 PM
Yes, I tested my code. It opens Word and then shows the userform on top of Word.
OK, I retract that. I tested it straight from the VBA editor, if you try to call the code from Excel, for example from a button or through alt+F8, it does not work.

However, the attached seems to do the trick.