Activate Event Event Handler

You can write code in the ActivateEvent handler of a worksheet to take action whenever a particular worksheet has focus. For example, you can show only one actions pane per workbook, but you can write code to display various controls on the actions pane that are specific to the visible worksheet. Add the code in Listing 7.41 to the Sheet1 class to see an example.

Listing 7.41. Showing controls on the actions pane when the ActivateEvent of Sheetl is raised

Dim Sheet1Control As New MonthCalendar

Private Sub Sheet1_ActivateEvent() Handles Me.ActivateEvent

Globals.ThisWorkbook.ActionsPane.Controls.Add(Sheet1Control)

If Globals.ThisWorkbook.ActionsPane.Controls.Count >= 1 Then

Application.CommandBars("Task Pane").Visible = True End If

End Sub

Private Sub Sheet1_Deactivate() Handles Me.Deactivate

Globals.ThisWorkbook.ActionsPane.Controls.Remove( _ Sheet1Control)

If Globals.ThisWorkbook.ActionsPane.Controls.Count < 1 Then

Application.CommandBars("Task Pane").Visible = False End If

End Sub

Next, add similar code (Listing 7.42) to the Sheet2 class. In this way, when you give focus to Sheetl, the MonthCalendar control appears on the actions pane, but when you give focus to Sheet2, MonthCalendar is removed and the DateTimePicker control is shown.

Listing 7.42. Showing particular controls on the actions pane Dim Sheet2Control As New DateTimePicker

Private Sub Sheet2_ActivateEvent() Handles Me.ActivateEvent

Globals.ThisWorkbook.ActionsPane.Controls.Add(Sheet2Control) If Globals.ThisWorkbook.ActionsPane.Controls.Count >= 1 Then

Application.CommandBars("Task Pane").Visible = True End If

End Sub

Private Sub Sheet2_Deactivate() Handles Me.Deactivate

Globals.ThisWorkbook.ActionsPane.Controls.Remove( _ Sheet2Control)

If Globals.ThisWorkbook.ActionsPane.Controls.Count < 1 Then

Application.CommandBars("Task Pane").Visible = False End If

End Sub

When you run this code, the actions pane is not visible, because even though Sheetl is visible, the ActivateEvent does not get raised. Either you must activate another sheet and reactivate Sheetl, or you can write additional code in Sheetl's Startup event handler to show the appropriate controls. This example uses a single control on the actions pane, but you can modify it to add multiple controls by designing a user control (or actions pane control) and then adding the user control to the actions pane. See Chapter 5 for more information about the actions pane.

0 0

Post a comment