Message box of all hidden workbooks

[kojak27]

I am using this code to Display a message of all open workbooks.

Sub LIST_OPEN_WORKBOOK_MESSAGE()
Dim a, i&, fn$, prefix$
ReDim a(1 To Workbooks.Count)

prefix = "report to excel."
For i = 1 To Workbooks.Count
a(i) = Workbooks(i).Name
If Left(a(i), Len(prefix)) = prefix Then fn = a(i)
Next i
MsgBox Join(a, vbLf)
If fn <> "" Then MsgBox fn
End Sub

How do I modify the code to Display a list of just the open HIDDEN workbooks?

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try:

Sub LIST_hidden_WORKBOOK_MESSAGE()
  Dim a, i&, fn$, prefix$
  ReDim a(1 To Workbooks.Count)
  
  prefix = "report to excel."
  For i = 1 To Workbooks.Count
    If Application.Windows(i).Visible = False Then
      a(i) = Workbooks(i).Name
      If Left(a(i), Len(prefix)) = prefix Then fn = a(i)
    End If
  Next i
  MsgBox Join(a, vbLf)
  If fn <> "" Then MsgBox fn
End Sub

 

[kojak27]

I tried this and it still lists ALL of the open workbooks and not just the hidden ones.

 

[JoeMo]

I tried this and it still lists ALL of the open workbooks and not just the hidden ones.

Try:

Sub ListOpenHiddenWBs()
Dim Wb As Workbook, Ct As Long, Lst As String
For Each Wb In Workbooks
    If Not Windows(Wb.Name).Visible Then
        Ct = Ct + 1
        Lst = Lst & vbNewLine & Wb.Name
    End If
Next Wb
If Ct > 0 Then
    MsgBox "There are " & Ct & " hidden workbooks open:" & vbNewLine & Lst
Else
    MsgBox "There are no hidden workbooks open"
End If
End Sub

 

[kojak27]

Try:

Sub ListOpenHiddenWBs()
Dim Wb As Workbook, Ct As Long, Lst As String
For Each Wb In Workbooks
    If Not Windows(Wb.Name).Visible Then
        Ct = Ct + 1
        Lst = Lst & vbNewLine & Wb.Name
    End If
Next Wb
If Ct > 0 Then
    MsgBox "There are " & Ct & " hidden workbooks open:" & vbNewLine & Lst
Else
    MsgBox "There are no hidden workbooks open"
End If
End Sub

Thanks, this worked perfectly.

 

[DanteAmor]

Try:

Sub LIST_hidden_WORKBOOK_MESSAGE()
  Dim a, i&, fn$, prefix$, s
  ReDim a(1 To Workbooks.Count)
  
  prefix = "report to excel."
  For i = 1 To Workbooks.Count
    s = Workbooks(i).Name
    If Application.Windows(s).Visible = False Then
      a(i) = s
      If Left(a(i), Len(prefix)) = prefix Then fn = a(i)
    End If
  Next i
  MsgBox Join(a, vbLf)
  If fn <> "" Then MsgBox fn
End Sub

 

[JoeMo]

Thanks, this worked perfectly.

You are welcome - thanks for the reply.

 

[kojak27]

Try:

Sub LIST_hidden_WORKBOOK_MESSAGE()
  Dim a, i&, fn$, prefix$, s
  ReDim a(1 To Workbooks.Count)
 
  prefix = "report to excel."
  For i = 1 To Workbooks.Count
    s = Workbooks(i).Name
    If Application.Windows(s).Visible = False Then
      a(i) = s
      If Left(a(i), Len(prefix)) = prefix Then fn = a(i)
    End If
  Next i
  MsgBox Join(a, vbLf)
  If fn <> "" Then MsgBox fn
End Sub

That did the trick. Thanks.