how to list all open workbooks within a message box except current workbook

[Pookiemeister]

As the title states, is possible to "list" all open workbooks but not include the current workbook.
After searching the web this is closest the following code is pretty close to what I am looking for, except it list each individual open workbook in a msgbox one at time. Which makes sense since the msgbox is within a For Loop but unfortunately it also includes the current workbook as well.
Thank you

Sub isAnyWorkbookOpen()
        
    Dim wb As Workbook
    Dim wbs As Workbooks
    
    Set wbs = Application.Workbooks
    
    For Each wb In wbs
        MsgBox wb.Name 
    Next wb
End Sub

 

[dmt32]

Hi,
try this update to your code

Sub isAnyWorkbookOpen()
        
    Dim wb As Workbook
    Dim wbs As Workbooks
    Dim 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
    
    MsgBox IIf(Len(msg) > 0, msg, "No workbooks Open"), 48, "Workbooks Open"
End Sub

Dave

 

[Pookiemeister]

Thank you that worked.

 

[Ahmed Hashish]

Hi,
try this update to your code

Sub isAnyWorkbookOpen()
       
    Dim wb As Workbook
    Dim wbs As Workbooks
    Dim 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
   
    MsgBox IIf(Len(msg) > 0, msg, "No workbooks Open"), 48, "Workbooks Open"
End Sub

Dave

Hello,
I appretiate if you can help on this one
I am using combobox1 instead of msg box.
I need to do exactly as the above, as in removing ThisWorkBook from the item listing

My code as as follows:

Dim wkb as Workbook
For each wkb in Application.Workbooks
.Additem wkb.name
Next wkb
End with

Thanks in advance

 

[rlv01]

appretiate if you can help on this one

Edit. I did not realize this was a 2019 thread. You should probably create your own new thread.

 

[Ahmed Hashish]

Sorry but I thought it was relevant, so I continued on here.
Sorry.
Also still appreciate your help if its possible.

 

[rlv01]

'
'To be located in worksheet code module, assumes a worksheet activeX combobox control named ComboBox1
'
Sub ListOpenWorkbooksExample()
    Dim WB As Workbook

    With Me.ComboBox1
        .Clear
        For Each WB In Application.Workbooks
            Select Case WB.Name
            Case ThisWorkbook.Name
            Case Else
                .AddItem WB.Name
            End Select
        Next WB

        If .ListCount > 0 Then
            .ListIndex = 0
            MsgBox .ListCount & " open workbooks", vbInformation, "Open Workbooks"
        Else
            MsgBox "No workbooks are open", vbExclamation, "Open Workbooks"
        End If
    End With
End Sub