MsgBox in another Instance of Excel

B

Benjamin

I want to open another instance of excel and show a message box in the new
Excel app. Any idea how?


Sub PrintForm()
Dim OrigFile As String

Dim filestring As String
Dim OpenThisFile As String
Dim CurVesl As String
Dim xlApp As Excel.Application

Set xlApp = CreateObject("Excel.Application")


OrigFile = ThisWorkbook.Name

CurVesl = ThisWorkbook.Sheets("Vessel").Range("EA2").Value
filestring = "Dock Log_Fillout.xls"
'File to Open in Another Instance of Excel
OpenThisFile = ThisWorkbook.Path & "\" & filestring


xlApp.WindowState = xlMaximized
xlApp.Workbooks.Open Filename:=OpenThisFile


xlApp.Worksheets(1).Range("C5").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 3).Value ' SHIP/TUG
NAME:
xlApp.Worksheets(1).Range("C6").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 4).Value ' BARGE NAME:
xlApp.Worksheets(1).Range("C7").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 5).Value ' INSPECTION
COMPANY:
xlApp.Worksheets(1).Range("C10").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 11).Value ' NOR
TENDERED
xlApp.Worksheets(1).Range("C11").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 13).Value '
ANCHORED/LAY BERTH
xlApp.Worksheets(1).Range("C12").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 15).Value ' START
INBOUND
xlApp.Worksheets(1).Range("C13").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 17).Value ' INSPECTION
CO. NOTIFIED
xlApp.Worksheets(1).Range("C14").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 19).Value ' FIRST LINE
xlApp.Worksheets(1).Range("C15").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 21).Value ' ALL FAST
xlApp.Worksheets(1).Range("C16").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 23).Value ' GANGWAY
SECURE
xlApp.Worksheets(1).Range("c18").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 37).Value ' SHORE TANK
# :
xlApp.Worksheets(1).Range("c19").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 38).Value ' CARGO:
xlApp.Worksheets(1).Range("c21").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 39).Value ' TRANSFER
START
xlApp.Worksheets(1).Range("c22").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 41).Value ' TRANSFER
STOP/FINISH
xlApp.Worksheets(1).Range("c24").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 49).Value ' SHORE TANK
# :
xlApp.Worksheets(1).Range("c25").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 50).Value ' CARGO:
xlApp.Worksheets(1).Range("c27").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 51).Value ' TRANSFER
START
xlApp.Worksheets(1).Range("c28").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 53).Value ' TRANSFER
STOP/FINISH
xlApp.Worksheets(1).Range("c30").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 67).Value ' INSPECTOR
NOTIFIED
xlApp.Worksheets(1).Range("c31").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 69).Value ' INSPECTOR
ABOARD
xlApp.Worksheets(1).Range("c32").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 71).Value ' VESSEL
INSPECTION COMP
xlApp.Worksheets(1).Range("c33").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 73).Value ' LAST
ARM/HOSE DISCONNECTED
xlApp.Worksheets(1).Range("c30").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 68).Value ' INSPECTOR
NOTIFIED
xlApp.Worksheets(1).Range("c31").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 70).Value ' INSPECTOR
ABOARD
xlApp.Worksheets(1).Range("c32").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 72).Value ' VESSEL
INSPECTION COMP
xlApp.Worksheets(1).Range("c33").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 74).Value ' LAST
ARM/HOSE DISCONNECTED
xlApp.Worksheets(1).Range("d10").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 12).Value ' NOR
TENDERED
xlApp.Worksheets(1).Range("d11").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 14).Value '
ANCHORED/LAY BERTH
xlApp.Worksheets(1).Range("d12").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 16).Value ' START
INBOUND
xlApp.Worksheets(1).Range("d13").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 18).Value ' INSPECTION
CO. NOTIFIED
xlApp.Worksheets(1).Range("d14").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 20).Value ' FIRST LINE
xlApp.Worksheets(1).Range("d15").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 22).Value ' ALL FAST
xlApp.Worksheets(1).Range("d16").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 24).Value ' GANGWAY
SECURE
xlApp.Worksheets(1).Range("d21").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 40).Value ' TIME
xlApp.Worksheets(1).Range("d22").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 42).Value ' TIME
xlApp.Worksheets(1).Range("d27").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 52).Value ' TIME
xlApp.Worksheets(1).Range("d28").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 54).Value ' TIME
xlApp.Worksheets(1).Range("d30").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 42).Value ' INSPECTOR
NOTIFIED
xlApp.Worksheets(1).Range("d31").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 44).Value ' INSPECTOR
ABOARD
xlApp.Worksheets(1).Range("d32").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 46).Value ' VESSEL
INSPECTION COMP
xlApp.Worksheets(1).Range("d33").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 48).Value ' LAST
ARM/HOSE DISCONNECTED
xlApp.Worksheets(1).Range("b46").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 84).Value ' Comments1
xlApp.Worksheets(1).Range("b47").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 91).Value ' Comments2
xlApp.Worksheets(1).Range("b48").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 98).Value ' Comments3
xlApp.Worksheets(1).Range("b49").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 105).Value ' Comments4
xlApp.Worksheets(1).Range("b50").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 112).Value ' Comments5
xlApp.Worksheets(1).Range("f46").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 83).Value ' codedelay1
xlApp.Worksheets(1).Range("f47").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 90).Value ' codedelay2
xlApp.Worksheets(1).Range("f48").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 97).Value ' codedelay3
xlApp.Worksheets(1).Range("f49").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 104).Value ' codedelay4
xlApp.Worksheets(1).Range("f50").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 111).Value ' codedelay5
xlApp.Worksheets(1).Range("h11").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 25).Value ' VESSEL
CLEARED
xlApp.Worksheets(1).Range("h12").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 27).Value ' INSPECTOR
ABOARD
xlApp.Worksheets(1).Range("h13").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 29).Value ' LAST
ARM/HOSE CONNECTED
xlApp.Worksheets(1).Range("h14").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 31).Value ' COMPLETE
GAUGING/CALCULATIONS
xlApp.Worksheets(1).Range("h15").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 33).Value ' VSL/SHORE
CONFERENCE FIN
xlApp.Worksheets(1).Range("h16").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 35).Value ' PRE
TRANSFER LAB ANALYSIS OK
xlApp.Worksheets(1).Range("j11").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 26).Value ' VESSEL
CLEARED
xlApp.Worksheets(1).Range("j12").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 28).Value ' INSPECTOR
ABOARD
xlApp.Worksheets(1).Range("j13").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 30).Value ' LAST
ARM/HOSE CONNECTED
xlApp.Worksheets(1).Range("j14").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 32).Value ' COMPLETE
GAUGING/CALCULATIONS
xlApp.Worksheets(1).Range("j15").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 34).Value ' VSL/SHORE
CONFERENCE FIN
xlApp.Worksheets(1).Range("j16").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 36).Value ' PRE
TRANSFER LAB ANALYSIS OK
xlApp.Worksheets(1).Range("f18").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 43).Value ' SHORE TANK
# :
xlApp.Worksheets(1).Range("f19").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 44).Value ' CARGO:
xlApp.Worksheets(1).Range("h21").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 45).Value ' TRANSFER
RESUME
xlApp.Worksheets(1).Range("h22").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 47).Value ' TRANSFER
STOP/FINISH
xlApp.Worksheets(1).Range("f24").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 55).Value ' SHORE TANK
#:
xlApp.Worksheets(1).Range("f25").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 56).Value ' CARGO:
xlApp.Worksheets(1).Range("h27").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 57).Value ' TRANSFER
RESUME
xlApp.Worksheets(1).Range("h28").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 59).Value ' TRANSFER
STOP/FINISH
xlApp.Worksheets(1).Range("j21").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 46).Value ' TRANSFER
RESUME
xlApp.Worksheets(1).Range("j22").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 48).Value ' TRANSFER
STOP/FINISH
xlApp.Worksheets(1).Range("j27").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 58).Value ' TRANSFER
RESUME
xlApp.Worksheets(1).Range("j28").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 60).Value ' TRANSFER
STOP/FINISH
xlApp.Worksheets(1).Range("j30").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 76).Value ' BOOM
REMOVED
xlApp.Worksheets(1).Range("j31").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 78).Value ' VESSEL
RELEASED
xlApp.Worksheets(1).Range("j32").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 80).Value ' GANGWAY UP
xlApp.Worksheets(1).Range("j33").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 82).Value ' VESSEL
SAILED
xlApp.Worksheets(1).Range("j30").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 77).Value ' BOOM
REMOVED
xlApp.Worksheets(1).Range("j31").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 79).Value ' VESSEL
RELEASED
xlApp.Worksheets(1).Range("j32").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 81).Value ' GANGWAY UP
xlApp.Worksheets(1).Range("j33").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 82).Value ' VESSEL
SAILED Date
xlApp.Worksheets(1).Range("g46").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 85).Value ' Start Date
Comment1
xlApp.Worksheets(1).Range("g47").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 92).Value ' Start Date
Comment2
xlApp.Worksheets(1).Range("g48").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 99).Value ' Start Date
Comment3
xlApp.Worksheets(1).Range("g49").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 106).Value ' Start
Date Comment4
xlApp.Worksheets(1).Range("g50").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 113).Value ' Start
Date Comment5
xlApp.Worksheets(1).Range("h46").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 86).Value ' Start Time
Comment1
xlApp.Worksheets(1).Range("h47").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 93).Value ' Start Time
Comment2
xlApp.Worksheets(1).Range("h48").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 100).Value ' Start
Time Comment3
xlApp.Worksheets(1).Range("h49").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 107).Value ' Start
Time Comment4
xlApp.Worksheets(1).Range("h50").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 114).Value ' Start
Time Comment5
xlApp.Worksheets(1).Range("i46").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 87).Value ' End Date
Comment1
xlApp.Worksheets(1).Range("i47").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 94).Value ' End Date
Comment2
xlApp.Worksheets(1).Range("i48").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 101).Value ' End Date
Comment3
xlApp.Worksheets(1).Range("i49").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 108).Value ' End Date
Comment4
xlApp.Worksheets(1).Range("i50").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 115).Value ' End Date
Comment5
xlApp.Worksheets(1).Range("j46").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 88).Value ' End Time
Comment1
xlApp.Worksheets(1).Range("j47").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 95).Value ' End Time
Comment2
xlApp.Worksheets(1).Range("j48").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 102).Value ' End Time
Comment3
xlApp.Worksheets(1).Range("j49").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 109).Value ' End Time
Comment4
xlApp.Worksheets(1).Range("j50").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 116).Value ' End Time
Comment5
xlApp.Worksheets(1).Range("k46").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 89).Value ' Duration
Comment1
xlApp.Worksheets(1).Range("k47").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 96).Value ' Duration
Comment2
xlApp.Worksheets(1).Range("k48").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 103).Value ' Duration
Comment3
xlApp.Worksheets(1).Range("k49").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 110).Value ' Duration
Comment4
xlApp.Worksheets(1).Range("k50").Value =
Workbooks(OrigFile).Sheets("Vessel").Cells(CurVesl, 117).Value ' Duration
Comment5


xlApp.Visible = True
xlApp.ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1

MsgBox "Dock Log Sent to Printer: " & xlApp.ActivePrinter
 
J

JLGWhiz

The Message Box is an interaction within VBA and should open in the active
window,. regardless of the calling instance of Excel However, I have never
tried it with two instances of Excel, it does work that way across
applications.
 
P

Peter T

No you can't do that, at least not in VBA. What you could do is make a
Userform look and act like a message box and attach the userform to the
newly created Excel app. Post back if interested to do that, and state the
oldest version of Excel you'll be working with.

Regards,
Peter T
 
B

Benjamin

Well it doesn't work for my case...
It opens the Msgbox in the excel instance that was first opened and not the
new one. What am I doing wrong?

So I don't see the messagebox unless I tab back to the original spreadsheet.
Do you understand what I'm getting at?
 
T

Tim Williams

Why are you opening a new instance of excel ?
Why not just open the other workbook in the same instance your code is
in ?

Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicate Count 1
In need of more guidance please.... 10
Select entire rows if between date/time range 3
Compating all rows in two sheets 2
first sip of working with range in vba excel 6
another code not working 4
Problems with repeated call of userform 4
Excel VBA won't copy Worksheets after sheet Deletion 5

Top