VBA: Windows vs Workbooks

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
I'm having a bit of trouble recognizing the differences between Windows and Workbooks. If I have multiple workbooks open each one will have its own window as far as I can tell, so the following two for loops are rather similar
Code:
Dim Win As Window
For Each Win In Windows
Debug.Print Win.Visible
Next Win

Dim Wkbook As Workbook
For Each Wkbook In Workbooks
Debug.Print Wkbook.Name
Next Wkbook

But how do I make these two concepts meet? Like how can I display the names of all Visible Open Workbooks, since Visible is a property of the Window and Name is a property of the Workbook.

Thanks,
~Gold Fish
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
HI goldfish

If I have multiple workbooks open each one will have its own window as far as I can tell

No. What you see are the windows. Each workbook can have several windows.

You usually use Window>New Window (in xl 2000) to create new windows for the workbook.

I use it frequently to see for ex. side by side diferent parts of a worksheet (or parts of different worksheets in the workbook). Since the windows are independent you can scroll one of them and it doesn't affect the others.

So what is visible (or not) are the windows that you create for the workbook. The workbook itself is just open (and will have at least one window).

hope this helps
PGC
 

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
Yup, tremendously, Thank you.

Code:
Sub test()
Dim wkbook As Workbook
Dim Win As Window
For Each wkbook In Workbooks
    For Each Win In wkbook.Windows
        If Win.Visible Then
            Debug.Print wkbook.Name
        End If
    Next Win
Next wkbook
End Sub

Now I can take action on all visible workbooks (for example skipping personal.xls). This code can act several times on a single workbook if it has multiple windows open, but in my case that is desired since each one can have a different activesheet. Both Window and Workbook support the activesheet property with slightly different results, which can be a very useful distinction.

Thanks!
 

Forum statistics

Threads
1,181,724
Messages
5,931,688
Members
436,798
Latest member
spprtpplcm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top