In the VBA code below, how can I add the the Target.Offset(0, -3).Value to the MsgBox so…
- User
In the VBA code below, how can I add the the Target.Offset(0, -3).Value to the MsgBox so that it reads; "PO details copied to Word Document" & vbNewLine & "Please Add PO: (Target.Offset(0, -3).Value) into Comment Cell"
If Target.Cells.CountLarge > 1 Then Exit Sub 'only one cell at a time If Not Intersect(Target, Range("J2:J2001")) Is Nothing And Target.Value = "NOTIFY CONTRACTOR" Then 'Check for J2:J2001 If Target.Row > 1 Then 'USED TO OPEN WORD DOC FOR TASK REQUEST - DO NOT DELETE 'Module9.sheetJobRequestActive = True 'THIS SET THE GLOBAL VARIABLE TO - TRUE - because the code checks for TRUE the code runs 'If Not Module9.sheetJobRequestActive Then Exit Sub ' if TRUE it allows the module to run Dim wordApp As Object Dim doc As Object Dim WshShell As Object
Set wordApp = CreateObject("Word.Application") Set doc = wordApp.Documents.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\zzzz ServProvDocs\POissued.docm")
wordApp.Visible = True doc.Activate
Set WshShell = CreateObject("WScript.Shell") WshShell.AppActivate wordApp.Caption
Application.Wait (Now + TimeValue("0:00:03"))
Dim wsjrw As Worksheet Dim wscfw As Worksheet Dim activeRow As Long
Set wsjrw = Worksheets("PORDER") activeRow = ActiveCell.Row Set wscfw = Worksheets(Cells(activeRow, 11).Value) Cells(activeRow, 12).Value = "Sent" Cells(activeRow, 10).Calculate
wscfw.Activate
MsgBox ("PO details copied to Word Documnet" & vbNewLine & "" & vbNewLine & "Please Add PO:123 number into Comment Cell" & vbNewLine & "" & vbNewLine & "Comments Can Also Be Added")
End If End If
- Assistant
To add the value of
Target.Offset(0, -3).Valueto theMsgBox, you can concatenate it using the&operator. Here's the modified code:MsgBox "PO details copied to Word Document" & vbNewLine & "Please Add PO: " & Target.Offset(0, -3).Value & " into Comment Cell" & vbNewLine & "Comments Can Also Be Added"This will display the message as:
PO details copied to Word Document Please Add PO: [value] into Comment Cell Comments Can Also Be AddedReplace
[value]with the actual value ofTarget.Offset(0, -3).Value.