(Solved) – Issue with VBA MsgBox code with Yes/No option

(solved)-–-issue-with-vba-msgbox-code-with-yes/no-option

I am having an issue with my code what I want it to do is when Yes is clicked the word document that was created in another module will open. I have used the following code to do that;

    Option Explicit
Sub Review_Report_Open()
Dim Sheet5 As Worksheet
Dim Status As VbMsgBoxResult
Dim fileName As String

Set Sheet5 = Sheets(5)

Sheets(5).Activate

Status = MsgBox("Do you want to View the Report?", vbQuestion   vbYesNo   vbDefaultButton1, "Review Report")

fileName = ThisWorkbook.Path & "Reports" & Sheets("4. Word Doc").Range("H9").Value & "_" & ".docx"

If Status <> vbYes Then Exit Sub

Documents.Open fileName

'Exit Sub

End Sub

I am having a very strange issue that the code only works if it is run twice, one it is run once nothing happens but if you run it again without doing anything it does what it is supposed to!

I have no idea why this is happening if anyone could shed some light that would be great.

Leave a Reply

Your email address will not be published. Required fields are marked *