In this VBA event, I want it to detect if Word is open. If a word document is open the…
- 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
- 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 SubThis 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.