MsgBox if multiple Workbooks with similar name are open

[288enzo]

In the code below I have a MsgBox if a particular Workbook isn't open, and I would like to add another MsgBox if more than one Workbook is open that fits the criteria.

Sub set_transcript_wbk()
    Dim MyWkb As Workbook
    Dim wkb As Workbook
    
    For Each wkb In Workbooks
        If wkb.Name Like "Transcript*.xls" Then
            Set MyWkb = Workbooks(wkb.Name)
            Exit For
        End If
    Next wkb
    
    If MyWkb Is Nothing Then
        MsgBox "Please open your Transcript file", vbExclamation
    End If
    
End Sub

My guess is adding something like

    If Not MyWkb Is Nothing Then
        MsgBox "Please close all but one Transcript file and re-run macro", vbExclamation

 

[Fluff]

How about

Sub set_transcript_wbk()
    Dim MyWkb As Workbook
    Dim wkb As Workbook
    Dim i As Long
    
    For Each wkb In Workbooks
        If wkb.Name Like "Transcript*.xls" Then
            Set MyWkb = Workbooks(wkb.Name)
            i = i + 1
        End If
    Next wkb
    
    If i > 1 Then
      Set MyWkb = Nothing
      MsgBox "Please close all but one Transcript file and re-run macro", vbExclamation
      Exit Sub
   End If
    If MyWkb Is Nothing Then
        MsgBox "Please open your Transcript file", vbExclamation
    End If
    
End Sub

 

[288enzo]

How about

Sub set_transcript_wbk()
    Dim MyWkb As Workbook
    Dim wkb As Workbook
    Dim i As Long
   
    For Each wkb In Workbooks
        If wkb.Name Like "Transcript*.xls" Then
            Set MyWkb = Workbooks(wkb.Name)
            i = i + 1
        End If
    Next wkb
   
    If i > 1 Then
      Set MyWkb = Nothing
      MsgBox "Please close all but one Transcript file and re-run macro", vbExclamation
      Exit Sub
   End If
    If MyWkb Is Nothing Then
        MsgBox "Please open your Transcript file", vbExclamation
    End If
   
End Sub

Oh, I like that. Great idea.

 

[Fluff]

Glad to help & thanks for the feedback.