Worksheet_Activate () - Need to work when any worksheet is activated

Hi All,

I have this code sitting in Sheet2 of my workbook:

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False

Range("B:B").ClearContents

For i = 1 To Sheets.Count
        Cells(i, 2) = Sheets(i).Name
    Next i
Application.ScreenUpdating = False

End Sub

The code kicks in when I activate sheet2 (that is where I want the code to work), but what I need is for the code to update sheet2 whenever any of my worksheets are activated. Help...?

JM

Hi there,

Insert the following code into the "ThisWorkbook" VBA CodeModule:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Const sTARGET_SHEET As String = "Sheet2"

    Dim wksTarget       As Worksheet

    Set wksTarget = ThisWorkbook.Sheets(sTARGET_SHEET)

    Application.ScreenUpdating = False

        With wksTarget

            .Range("B:B").ClearContents

            For i = 1 To Sheets.Count
                .Cells(i, 2) = Sheets(i).Name
            Next i

        End With

    Application.ScreenUpdating = False

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg,

That worked perfectly, but it threw up an unexpected problem, because the code causes a small load time now every time I select a new tab, timings are building up - so could I ask you this: Instead of before when I wanted the code to kick in when any worksheet, can I have it kick in only when I select sheet3?

Regards,
JM

Hi again Jim,

You can speed up the execution of the routine a little bit by adding the highlighted statements:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Const sTARGET_SHEET As String = "Sheet2"

    Dim wksTarget       As Worksheet

    Set wksTarget = ThisWorkbook.Sheets(sTARGET_SHEET)

    Application.ScreenUpdating = False

        Application.Calculation = xlCalculationManual

            With wksTarget

                .Range("B:B").ClearContents

                For i = 1 To Sheets.Count
                    .Cells(i, 2) = Sheets(i).Name
                Next i

            End With

        Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = False

End Sub

Or you could just change the first line of the routine to:

Private Sub Worksheet_Activate()

then insert the routine into the VBA CodeModule for Sheet 3, and delete it from the CodeModule for ThisWorkbook.

And of course, you can even enter the "speeded-up" routine into just the CodeModule for Sheet 3.

Hope this helps - please let me know how you get on.

Regards,

Greg M