Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

In the VBA code below, how can I add the the Target.Offset(0, -3).Value to the MsgBox so…

  1. 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

  2. Assistant

    To add the value of Target.Offset(0, -3).Value to the MsgBox, 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 Added
    

    Replace [value] with the actual value of Target.Offset(0, -3).Value.