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

In this VBA event, I want it to detect if Word is open. If a word document is open the…

  1. User

    In this VBA event, I want it to detect if Word is open. If a word document is open the warn with message "A word document is open and needs to be closed to use the Job Request" When the word document is closed the VBA should then proceed

    Private Sub Worksheet_Activate()

    Call Module11.EnableManualMainEvents Application.Wait (Now + TimeValue("0:00:01")) 'Call Module5.ListUniqueValuesCount

    Range("G2:I2").Calculate Range("D9:D14").Value = "" Range("D8").Calculate Range("D16:D20").Value = "" Range("D15").Calculate Range("A21:I21").Calculate Range("A2").Select

    Dim message As String Dim response As VbMsgBoxResult

    If WorksheetFunction.CountA(Range("A2:F2")) > 0 Then message = "Do you want to delete the Request Entry?" response = MsgBox(message, vbYesNo + vbQuestion, "Delete contents?")

    If response = vbYes Then ThisWorkbook.Sheets("Job Request").Range("A2:F2").ClearContents ThisWorkbook.Sheets("Job Request").Range("D9:D14").ClearContents ThisWorkbook.Sheets("Job Request").Range("D16:D20").ClearContents ThisWorkbook.Sheets("Job Request").Range("E7:E20").ClearContents Range("G2:I2").Calculate Range("F3:I3").Calculate Range("E6").Calculate

    Else Exit Sub

    End If End If

    End Sub

  2. Assistant

    To detect if Word is open, you can use the GetObject function and check if an error occurs. Here's an updated version of your code that checks if Word is open before proceeding:

    Private Sub Worksheet_Activate()
        Dim wordApp As Object
        On Error Resume Next
        Set wordApp = GetObject(, "Word.Application")
        If Err.Number <> 0 Then ' Word is not open
            ' Proceed with your code
            Call Module11.EnableManualMainEvents
            Application.Wait (Now + TimeValue("0:00:01"))
            ' Rest of your code...
            Range("A2").Select
        Else ' Word is open
            MsgBox "A Word document is open and needs to be closed to use the Job Request", vbExclamation, "Word Document Open"
        End If
    End Sub
    

    This code first attempts to retrieve a reference to the Word application using the GetObject function. If an error occurs, it means that Word is not open, and the code proceeds with the rest of your logic. If the GetObject function is successful, it means that Word is open, and a message box is displayed indicating that a Word document needs to be closed before proceeding.