VBA sheets object in Excel

As the name indicates, this object represents a Microsoft Excel Worksheet.

The Worksheet object is a member of Worksheets Collection.Note that "Worksheet" is an object, whereas "Worksheets" is a collection.

This collection contains all the instance of Microsoft excel Worksheets

So, when there are more than one Worksheet, a specific Worksheet can be accessed with the help of index number.

The index numbers represent the order in which the Worksheets are arranged.

A Worksheet can also be accessed with the help of its name.

  1. Worksheets(1).Activate
  2. Worksheets().Activate

There are various properties of Worksheet which are often used along with this object.

Some of them are detailed as below

Name

The Worksheet.Name returns the name of the active Worksheet. This property can also be used to assign a name to a new sheet.

Activate

The Worksheet.Activate is used to access a Worksheet and its content by activating it.

Note that, only the active Worksheet can be accessed using Worksheet object.

Conversely, when a Worksheet is accessed, it becomes the active Worksheet

Range

The Sheets.Range() is used to access and work on a specified range.The range may be a group of cells or a single cell.

This method is most often used in VBA when working with sheets.

Cells

This is similar to Range except that, it can access only a single cell.

The syntax is Cells(row,col). Though this is helpful, Range method looks more meaningful.

Protect

This is used to protect a worksheet with a password

Visible

This method is used to hide or unhide a worksheet.

PrintOut

This method is used to print a worksheet.

PageSetup

This is used to configure the orientation of the sheet, either portrait or landscape.

The following example illustrates the WorkSheets Objects

For this purpose, let us add a new worksheet named "WS" and will test various actions in it

and finally, delete the worksheet.

  1. Sub worksheet_demo()
  2. Dim sht As Worksheet
  3. Set sht = Sheets.Add
  4. sht.Name = "WS"
  5. sht.Activate
  6. MsgBox "Range demo : put value in Cell A1 using Range object "
  7. sht.Range("A1") = "Hello"
  8. MsgBox "Range demo with multiple cells"
  9. sht.Range("A1:D5") = "Hi"
  10. MsgBox "Hide Worksheet"
  11. sht.Visible = False
  12. MsgBox "Unhide "
  13. sht.Visible = True
  14. sht.Activate
  15. sht.Delete
  16. End Sub

The screenshot from editor is as shown below

excel vba sheets object

 

You can find similar Excel Questions and Answer hereunder

1) How can I prevent other users from adding or deleting Sheets in file?

2) What are the main cell objects in VBA

3) How do you close, save, open files and do other operations in the workbook in VBA

4) what are the main range object and what can the range objects do in VBA

5) How to avoid screen update in Excel VBA. Various application objects explained

6) How to add email and emailing function in Excel VBA

7) Want to use Microsoft Outlook in Excel. Here some basic explanation to get you started

8) What is a methods. Methods are action that can be performed by an object

9) What are objects and what are properties in Excel VBA

10) How to rename multiple sheets easily with VBA

 

Here the previous and next chapter