This is driving me nuts! The code below should reformat a workbook within the application window such that there are three windows. Each window will show a different worksheet. The Instructions window will appear over the other two (Diagram and Data) which are tiled. The user will then be able to see the data input sheets in one window and the diagram in another window. If he wishes to see the instructions then the window is open and can be moved or resized as he wishes.
Option Explicit Public wdwDiagram As Window Public wdwData As Window Public wdwInstructions As Window Public Sub FormatWorkBookInWindow() Const lngMargin As Long = 20 'Close all but one window Do While ThisWorkbook.Windows.Count > 1 ThisWorkbook.Windows(1).Close Loop 'Rename that window to be instructions and format it Set wdwInstructions = ThisWorkbook.Windows(1) With wdwInstructions .WindowState = xlNormal .DisplayWorkbookTabs = True .Caption = ThisWorkbook.Name & ": Instructions" .Top = 0 + lngMargin .Left = 0 + lngMargin .Height = Application.UsableHeight - 2 * lngMargin .Width = Application.UsableWidth - 2 * lngMargin Worksheets("Instructions").Select .DisplayGridlines = False End With Set wdwDiagram = ThisWorkbook.NewWindow With wdwDiagram .WindowState = xlNormal .DisplayWorkbookTabs = True .Top = 0 .Left = 0 .Width = Application.UsableWidth .Height = 0.6 * Application.UsableHeight .Caption = ThisWorkbook.Name & ": Diagram" Worksheets("Diagram").Select .DisplayGridlines = False End With [B][U] Set wdwData = ThisWorkbook.NewWindow[/U][/B] With wdwData .WindowState = xlNormal .DisplayWorkbookTabs = True .Top = wdwDiagram.Height + 1 .Width = Application.UsableWidth .Height = Application.UsableHeight - wdwDiagram.Height .Left = 0 .Caption = ThisWorkbook.Name & ": Data" Worksheets("Block Wall").Activate .DisplayGridlines = True End With wdwData.Activate wdwDiagram.Activate wdwInstructions.Activate Dim wdw As Window For Each wdw In Windows Debug.Print wdw.WindowNumber, wdw.Caption Next wdw End Sub
The problem is that when the bold underlined line is executed the window containing the instructions also changes to display the diagram worksheet.
Is this in some way linked to Windows(1) always being the active window? Can I (should I) use Window objects in this way?
I have tried to close all but one window, rename that window, create the others and rename them and then allocate the window objects to variables using the names of the windows rather than the window number and this also fails. I have also tried to rename each window as I create it and then not use the wdw variables at all but change the with statement to work on Windows([Window name]).
Still the same/similar problems.
Any ideas please?