ansaurus

Question

Excel - Worksheet_Activate Code for New Sheets

Answer 1

+1 A:

1) To test if a window is already open, use this function

Function IsWindowOpen(windowTitle As String) As Boolean
    Dim i As Long
    For i = 1 To Windows.Count
        If Windows(i).Caption = windowTitle Then
            IsWindowOpen = True
            Exit Function
        End If
    Next
    IsWindowOpen = False
End Function

For example:

if not IsWindowOpen("Mastersheet.xlsm:2") then
     ' code to open windows
end if

2) You can reuse the function again, same idea:

if IsWindowOpen("Mastersheet.xlsm:2") then
     ' code to close windows
end if

3) Add your code to a module, not to a sheet. Then call the routine from the macro which adds the new sheets after it has done this. If this macro is in a different module, you may have to make sure your Sub is public.

Doc Brown 2010-01-21 22:20:36

Answer 2

+5 A:

That's a lot of questions. :) For 3, you need to move your events out of where they are and into a custom class module that handles application level events. Start by inserting a new class module into your project (Insert - Class Module). Name that module CAppEvents (F4 to show the property sheet where you can change the name). Then paste this code into the class module

Option Explicit

Private WithEvents mobjWb As Workbook

Private Sub Class_Terminate()

    Set mobjWb = Nothing

End Sub

Public Property Get wb() As Workbook

    Set wb = mobjWb

End Property

Public Property Set wb(objwb As Workbook)

    Set mobjWb = objwb

End Property

Private Sub mobjWb_SheetActivate(ByVal Sh As Object)

    Dim wn As Window

    If IsSplitSheet(Sh) Then
        If Not IsSplit(Sh) Then
            CreateSplitSheets Sh
        End If
    Else
        If IsSplit(Sh) Then
            For Each wn In Me.wb.Windows
                If wn.Caption Like Me.wb.Name & ":#" Then
                    wn.Close
                End If
            Next wn
            ActiveWindow.WindowState = xlMaximized
            Sh.Activate
        End If
    End If

End Sub

Private Function IsSplitSheet(Sh As Object) As Boolean

    Dim vaNames As Variant
    Dim i As Long

    IsSplitSheet = False
    vaNames = GetSplitSheetNames

    For i = LBound(vaNames) To UBound(vaNames)
        If vaNames(i) = Sh.Name Then
            IsSplitSheet = True
            Exit For
        End If
    Next i

End Function

Private Function IsSplit(Sh As Object) As Boolean

    Dim wn As Window

    IsSplit = False

    For Each wn In Me.wb.Windows
        If wn.Caption Like Sh.Parent.Name & ":#" Then
            IsSplit = True
            Exit For
        End If
    Next wn

End Function

Private Sub CreateSplitSheets(Sh As Object)

    Dim vaNames As Variant
    Dim i As Long
    Dim wn As Window
    Dim wnActive As Window

    vaNames = GetSplitSheetNames
    Set wnActive = ActiveWindow

    For i = LBound(vaNames) To UBound(vaNames)
        If vaNames(i) <> Sh.Name Then
            Set wn = Me.wb.NewWindow
            wn.Activate
            On Error Resume Next
                wn.Parent.Sheets(vaNames(i)).Activate
            On Error GoTo 0
        End If
    Next i

    Sh.Parent.Windows.Arrange xlVertical
    wnActive.Activate
    Sh.Activate

End Sub

Private Function GetSplitSheetNames() As Variant

    GetSplitSheetNames = Array("Notes", "Work Orders", "Contact Info")

End Function

Then insert a standard module (Insert - Module) and paste this code

Option Explicit

Public gclsAppEvents As CAppEvents

Sub Auto_Open()

    Set gclsAppEvents = New CAppEvents
    Set gclsAppEvents.wb = ThisWorkbook

End Sub

Here's what's happening: When you open the workbook, Auto_Open will run and it will create a new instance of your CAppEvents object. Since gclsAppEvents is public (aka global) it won't lose scope for as long as the workbook is open. It will sit there listening for events (because we used the WithEvents keyword in the class).

In the class there's a sub called mobjWb_SheetActivate. This is what will fire whenever any sheet in this workbook is activated. First it checks if the sheet you just activated (the Sh variable) is one of the ones you want to split (using IsSplitSheet). If it is, it then checks to see if it already has been split. If not, it splits them.

If Sh (the sheet you just activated) is not one of the 'split sheets', then it checks to see if a split has been done (IsSplit). IF it has, it closes all the split windows.

If you even want to add, change, or delete sheets that cause a split, you go to the GetSplitSheetNames function and change the Array arguments.

Because we're using a custom class and sniffing for events at the workbook level, you can add and delete sheets all you want.

Dick Kusleika 2010-01-21 22:44:15

This works perfectly. I'll do my best to try and understand the code you've written. Thanks so much for your help dkusleika!

Ryan B 2010-01-22 20:32:00

ansaurus

tags:

views:

answers:

Excel - Worksheet_Activate Code for New Sheets

related questions