Hello All,
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 SubThe 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?
Thanks,
Alan.