Tiled Windows Of Same Workbook Code Affecting Wrong Window

  • 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.



    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?


    Thanks,


    Alan.

  • Re: Tiled Windows Of Same Workbook Code Effecting Wrong Window


    Agreed, it's not friendly, and I can't explain it, but changing your

    Code
    wdwData.Activate
    wdwDiagram.Activate
    wdwInstructions.Activate


    to

    Code
    wdwData.Activate
    Worksheets("Block Wall").Select
    wdwDiagram.Activate
    Worksheets("Diagram").Select
    wdwInstructions.Activate
    Worksheets("Instructions").Select

    and removing the Select statements earlier in the macro should give you what you want.


    p45cal

  • Re: Tiled Windows Of Same Workbook Code Effecting Wrong Window


    This revision worked for me.
    [vba]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)
    Set wdwDiagram = ThisWorkbook.NewWindow
    Set wdwData = ThisWorkbook.NewWindow

    With wdwInstructions
    .Activate
    .Parent.Worksheets("Instructions").Select
    .WindowState = xlNormal
    .DisplayWorkbookTabs = True
    .Caption = ThisWorkbook.Name & ": Instructions"
    .Top = 0 + lngMargin
    .Left = 0 + lngMargin
    .Height = Application.UsableHeight - 2 * lngMargin
    .Width = Application.UsableWidth - 2 * lngMargin
    .DisplayGridlines = False
    End With

    With wdwDiagram
    .Activate
    .Parent.Worksheets("Diagram").Select
    .WindowState = xlNormal
    .DisplayWorkbookTabs = True
    .Top = 0
    .Left = 0
    .Width = Application.UsableWidth
    .Height = 0.6 * Application.UsableHeight
    .Caption = ThisWorkbook.Name & ": Diagram"
    .DisplayGridlines = False
    End With

    With wdwData
    .Activate
    .Parent.Worksheets("Block Wall").Activate
    .WindowState = xlNormal
    .DisplayWorkbookTabs = True
    .Top = wdwDiagram.Height + 1
    .Width = Application.UsableWidth
    .Height = Application.UsableHeight - wdwDiagram.Height
    .Left = 0
    .Caption = ThisWorkbook.Name & ": Data"
    .DisplayGridlines = True
    End With

    wdwInstructions.Activate

    Dim wdw As Window
    For Each wdw In Windows
    Debug.Print wdw.WindowNumber, wdw.Caption
    Next wdw

    End Sub
    [/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!