Excel Macro Class: Worksheet Object

The Worksheet object is part of the Worksheets collection, and represents a worksheet, which is a type of Excel sheet. It is related and works similarly to the Sheets object (or collection). Sheets include worksheets and charts (Chart sheets).

How to refer to a Worksheet object

Both Worksheets and Sheets can be used with either an index number or the sheet name. But using the index number with Sheets could give a different outcome if there are also Chart sheets in that workbook.

Worksheets(1) or Sheets(1)

Worksheets("SheetName") or Sheets("SheetName")

Sheet1 or MySheet (if object module or defined as object)

ActiveSheet

When working with worksheets, it is common to declare object variables in order to represent each worksheet in scope of the macro or VBA project. With that purpose, the object variable is declared as a Worksheet object and defined with the ‘Set’ statement as follows:

Dim ws As Worksheet 'declares the object variable ws

Set ws = Worksheets("Sheet2") 'ws is now the reference worksheet object for Sheet2

Examples:

ws.Select

ws.Delete

Activate and Select worksheets

We can select multiple sheets but only one can be activated (or active). We can activate or select a sheet by either using the index number or sheet name (see below). That can be done referring to both the Worksheets and Sheets collection.

Worksheets(2).Activate 'activates the second worksheet

Worksheets("Sheet3").Activate 'activates Sheet3

Worksheets("Sheet2").Select 'selects and activates Sheet2

Dim mySheet As String 'declares a text variable

mySheet = "Sheet3" 'defines the variable as the sheet name

Worksheets(mySheet).Select 'selects the defined sheet

Worksheets(Array("Sheet1", "Sheet2")).Select 'selects both Sheet1 and Sheet2

When manually selecting more than one sheet, only the first sheet is activated. However, if you input data with more than one sheet selected, the input will replicate in the selected sheets.

Count, Add, and Delete worksheets

The Count property shows different figures for Worksheets and Sheets if there are Chart sheets in the workbook. In the above example, there are 3 worksheets (Sheet1, Sheet2, and Sheet3), and 1 Chart sheet (Chart1), that makes a total of 4 sheets.

Worksheets.Count 'in the above example is 3 Sheets.Count 'in the above example is 4

We can add a new sheet with either the Worksheets or Sheets collections. In both cases, it will add a worksheet. To add a Chart sheet we should use Charts.Add.

Worksheets.Add 'adds the sheet before the active sheet

Worksheets.Add after:=ActiveSheet 'adds the sheet specifically after the active sheet

Worksheets.Add before:=Sheets("Sheet2") 'adds the sheet specifically before Sheet2

There is a slight difference between Worksheets and Sheets when adding a new sheet with a given name using the Name property. Note that parenthesis after Worksheets.Add().

Worksheets.Add().Name = "MySheet"

Sheets.Add.Name = "MySheet"

To delete a sheet you can use both Worksheets and Sheets too.

Worksheets(1).Delete

Worksheets("Sheet1").Delete

Sheets(1).Delete

Sheets("Sheet1").Delete

Change sheet name or color of the tab

The Name and Tab.Color properties work similarly with both Worksheet and Sheet objects.

Worksheets(1).Name = "Summary"

Worksheets("Sheet1").Name = "Summary"

Sheets(1).Name = "Summary"

Sheets("Sheet1").Name = "Summary"

Worksheets(1).Tab.Color = vbRed

Worksheets(1).Tab.Color = RGB(255, 0, 255)

Sheets(1).Tab.Color = vbRed

Sheets(1).Tab.Color = RGB(255, 0, 255)

Hide/show worksheets

An Excel sheet can either be visible (by default) or not visible (hidden or very hidden). Hidden worksheets can be manually unhidden by right-clicking any tab and selecting “Unhide…”. Very hidden worksheets can only be unhidden from the VBA project properties window or with VBA code.

Worksheets(1).Visible = xlSheetVisible

'xlSheetVisible

'xlSheetHidden

'xlSheetVeryHidden

*works similar using ‘Sheets’ instead of ‘Worksheets’

Protect/unprotect worksheets

The Protect and Unprotect methods work similarly with Worksheets and Sheets, and can be used with or without a password. When protected, all locked cells and shapes cannot be modified. To unlock or lock those particular cells or shapes, we need to refer to the corresponding Range/Cells or Shape objects (see that in the following sections).

Worksheets("Sheet1").Protect

Worksheets("Sheet1").Protect "1234"

Worksheets("Sheet1").Protect myPsw

Worksheets("Sheet1").Protect Password:="1234"

Worksheets("Sheet1").Unprotect

*works similar using ‘Sheets’ instead of ‘Worksheets’

The Protect method accepts many other attributes to specify which actions are protected or allowed in locked cells (see the full list below).

Worksheet Events

Find below some of the most common Worksheet event procedures. Note that these apply to a specific sheet in the workbook, which is clearly highlighted in the VBA project explorer window on the left.

The Activate event is triggered when moving into a worksheet and allows to run a macro upon selecting that particular worksheet.

Private Sub Worksheet_Activate()

'set of tasks responding to that event

End Sub

The SelectionChange event occurs when moving from one cell or range to another within the same sheet. Target is the variable that stores the destination range or cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'set of tasks responding to that event

End Sub

The Change event triggers when the value of a particular cell or range changes. Target is the variable that stores the range or cell which value has changed.

Private Sub Worksheet_Change(ByVal Target As Range)

'set of tasks responding to that event

End Sub