VBA Excel - Clarification on Popup method

Question

Dear All,

by the below function (found in internet) used in Excel VBA I show a popup and wait some seconds before it disappears:

Private Function Popup(ByVal Prompt As String, Optional ByVal SecondsToWait As Integer = 0, Optional ByVal Title As String = "", Optional ByVal Buttons As VbMsgBoxStyle = vbOK) As VbMsgBoxResult 

    Dim Fso As Object 

    Dim Wss As Object 

    Dim TempFile As String 

    On Error GoTo ExitPoint 

    Set Fso = CreateObject("Scripting.FileSystemObject") 

    Set Wss = CreateObject("WScript.Shell") 

    With Fso 

        TempFile = .BuildPath(.GetSpecialFolder(2).Path, .GetTempName & ".vbs") 

        With .CreateTextFile(TempFile) 

            .WriteLine "Set wss = CreateObject(""WScript.Shell"")" & vbCrLf & _ 

                "i = wss.Popup(""" & Prompt & """," & SecondsToWait & ",""" & Title & _ 

                """," & Buttons & ")" & "" & vbCrLf & _ 

                "WScript.Quit i" 

            .Close 

        End With 

    End With 

    Popup = Wss.Run(TempFile, 1, True) 

    Fso.DeleteFile TempFile, True 

ExitPoint: 

End Function

I'm trying to show message in two lines as below:

by using the below code:

Private Sub cmdMsgBox2_Click() 

    Dim iMsgBox As Integer 

    iMsgBox = Popup("There is already a file named Test.txt" & vbCrLf & _ 

            "Do you want to overwrite it?", 10, "MessageBox Test", vbQuestion + vbYesNo + vbDefaultButton2) 

    If iMsgBox = vbNo Then 
       'Do something

    End If 

End Sub

I get this error:

I think the issue is on how concatenates the two string "There is already a file named Test.txt" & vbCrLf & "Do you want to overwrite it?"

Any suggestion to fix it?

Thanks

Answer 1

You could add:

Prompt = Replace$(Prompt, vbCrLf, """ & vbCrLf & """)

near the start of your function.

Answer 2

You could add:

Prompt = Replace$(Prompt, vbCrLf, """ & vbCrLf & """)

near the start of your function.

Great!

It works like a charm :-)