Message box to show in Word and not in Excel

[MattSonnier]

All:

I am running Windows XP with Word and Excel 2003. I am running Excel
VBA to open a word document and do some procedures. Basically, my code
gets some values in excel, then opens up Word and inserts the data into
Word. At the end of my code, I have a message box that says "the CD
labels are ready to print". When my code is finished, it ends with
word showing and activated on my screen. But the message box is shown
on Excel and the only way to see it is to activate Excel . Is there
any way to have this message box show up on top of Word being activated
or do I need to reactivate Excel to show this message box? If I can
only do one or the other, can you let me know and show me some code
that can do this? I am new to this VBA code stuff but am trying to
eagerly learn. Thanks in advance.

~Matt


Private Sub CommandButton2_Click()
Dim sCustomer As String

Sheets("SC Database").Activate
sCustomer = ActiveSheet.Range("Customer")

Dim appWD As Object
Dim docWD As Object
Set appWD = CreateObject("Word.Application")

Set docWD = appWD.documents.Open("C:\CD Jewel Case Label.doc")
appWD.Visible = True

'Replace Customer with correct job information
With docWD.Content.Find
.Text = "Customer"
.Replacement.Text = sCustomer
.Wrap = 1
.Execute Replace:=2
End With

MsgBox "Your CD labels for " & sCustomer & _
" are ready to print!", vbOKOnly, "CD Labels are done!"

End Sub

 

[Helmut Weber]

Hi Matt,

a somehat simplified and abbreviated sample:

In Excel:

Sub MsgInWOrd()
Dim oWrd As Word.Application
Set oWrd = GetObject(, "Word.application")
With oWrd
.Run MacroName:="WordMessage", varg1:="Test"
End With
End Sub

In Word:

Sub WordMessage(sText As String)
MsgBox sText
End Sub

Could be I've overlooked a simpler solution.

HTH
--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"