Help with vbYes

[Pookiemeister]

Sub isAnyWorkbookOpen()    
    Dim wb As Workbook
    Dim wbs As Workbooks
    Dim msg As String
    Dim answer As String
    
    Set wbs = Application.Workbooks
    
    msg = "The following worksbooks must be closed before continuing." & Chr(10) & "Do you want to save and close these workbooks?" & Chr(10) & Chr(10)
    
    For Each wb In wbs
        If wb.Name <> ThisWorkbook.Name Then msg = msg & wb.Name & Chr(10)
    Next wb
    
    MsgBox (msg), 52, "Workbooks Open"
        If answer = vbYes Then Call closeOtherWorkbooks
        
End Sub

When I Dim answer as String, I receive a Runtime error 13: Type Mismatch. Which I understand. However when I change the Dim answer as Boolean or Variant, the MsgBox will be displayed as expected but skips over

If answer = vbYes Then Call closeOtherWorkbooks

When I use the Watch window it states the value of Expression answer is Empty.
What am I doing wrong?

Thank You

 

[Michael M]

Maybe this way

Sub isAnyWorkbookOpen()
Dim wb As Workbook, wbs As Workbooks, msg As String
Set wbs = Application.Workbooks
For Each wb In wbs
    If wb.Name <> ThisWorkbook.Name Then msg = msg & wb.Name & Chr(10)
Next wb
msg = "The following worksbooks must be closed before continuing." & Chr(10) & "Do you want to save and close these workbooks?" & Chr(10) & Chr(10)
MsgBox (msg), 52, "Workbooks Open"
    If vbYes = 6 Then Call closeOtherWorkbooks
End Sub

 

[Pookiemeister]

Thank You that worked but what does vbYes = 6 mean?

 

[Michael M]

6 = true
So, if VByes =true then...

 

[Norie]

The value of the constant vbYes is 6 so the expression in this If statement will always evaluate to True and closeOtherWorkbooks will always be called regardless of the response to the message box.

    If vbYes = 6 Then Call closeOtherWorkbooks

Is that what you want to happen?

If you want the code to only call closeOtherWorkbooks when the user clicks Yes on the message box try this.

Sub isAnyWorkbookOpen()
Dim wb As Workbook, wbs As Workbooks, msg As String
Dim Ans As Long

    Set wbs = Application.Workbooks
    For Each wb In wbs
        If wb.Name <> ThisWorkbook.Name Then msg = msg & wb.Name & Chr(10)
    Next wb
    msg = "The following worksbooks must be closed before continuing." & Chr(10) & "Do you want to save and close these workbooks?" & Chr(10) & Chr(10)

    Ans = MsgBox(msg, 52, "Workbooks Open")
    
    If Ans = vbYes Then
        Call closeOtherWorkbooks
    End If
    
End Sub

 

[Fluff]

Another option

If MsgBox(Msg, 52, "Workbooks Open") = vbYes Then Call closeOtherWorkbooks

 

[Pookiemeister]

Good point Norie, the vbMsgBoxStyle value = 52 which includes the Yes and No buttons.So if the user clicks the No button, I probably need to do something like exit sub or just exit. So I changed the code a bit from what I originally posted. Not sure if I need to post this in a new thread. Someone please let me know before I post new code. Don't want to get in trouble with this forum. Thank you.

 

[Norie]

What do you want to happen if the user clicks No?

In the code I posted if the user clicks No then no further action will be taken and the code will end 'naturally'.

 

[Pookiemeister]

Sub isAnyWorkbookOpen()    
    Dim wb As Workbook
    Dim wbs As Workbooks
    Dim msg As String
    Dim ans As Long
    
    Set wbs = Application.Workbooks
    
    msg = "The following worksbooks must be closed before continuing." & Chr(10) & "Do you want to save and close these workbooks?" & Chr(10) & Chr(10)
    
    For Each wb In wbs
        If wb.Name <> ThisWorkbook.Name Then 
            msg = msg & wb.Name & Chr(10)
        Else
            Exit Sub
        End If
    Next wb
    
   ans = MsgBox(msg, 52, "Workbooks Open")
        If ans = vbYes Then
            Call closeOtherWorkbooks
        Else
            End
        End If
End Sub

What I am wanting this code to do, is first check what is already open, if those workbook names doesn't equal ThisWorkbook name then run the following code

msg = msg & wb.Name & Chr(10)

but if there are no other workbooks open other than ThisWorkbook and it is equal to wb.name then Exit Sub.
However the why this code is phrased as soon as the first line of the If statement is checked it jumps to the Else statement which makes sense. So how can I rephrase this to do what I want. Thank you.

 

[Norie]

What, in words, are you trying to do?

Are you trying to check if any other workbooks, apart from the one the code is in, are open?

What do you want to happen if other workbooks are open?