Worksheets

In this chapter you will learn about the worksheet’s properties and events.

How to add a new worksheet?

To add a new worksheet in a workbook Sheets.add is used.

Sub Add_New_Worksheet()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets.Add

End Sub

How to rename new worksheet or existing worksheet?

To rename the new worksheet below is the code. We are renaming the worksheet as “Data”.

Sub Rename_New_Worksheet()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets.Add

sh.Name = "Data"

End Sub

To Rename an existing worksheet below are the code. Here are renaming Sheet1 with Data.

Sub Rename_Existing_Worksheet()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Name = "Data"

End Sub

How to delete a worksheet?

To delete a worksheet below is the code. It will ask a confirmation before deleting the worksheet. If you don’t want to see that alert, then you need use “Application.DisplayAlerts = False”

Sub Delete_Worksheet()

    'This is being used disbable the sheet delete confirmation.

Application.DisplayAlerts = False  

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Delete

End Sub

How to hide/unhide a worksheet?

There are two types of worksheets hidden in Excel VBA.

1. xlSheetHidden  is used to normal hide and worksheet can be un-hidden from excel by using sheet unhide (Alt O+H+U).

2. xlSheetVeryHidden  is used to very hidden and worksheet cannot be un-hidden from excel by using sheet unhide. To unhide such worksheet we must use VBA or Sheet properties window.

Below is the xlSheetHidden example:

Sub Hidden_Worksheet()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Visible = xlSheetHidden      

End Sub

Below is the  xlSheetVeryHidden example:

Sub Very_Hidden_Worksheet()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Visible = xlSheetVeryHidden       

End Sub

Below is code to unhide a worksheet:

Sub Unhide_Worksheet()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Visible = xlSheetVisible       

End Sub

How to activate a worksheet?

We can active any worksheet. For example, we have 3 sheets “Sheet1”, “Sheet2” and “Sheet3” in our workbook and currently sheet3 is active. If we want to active “Sheet1” the below is the code.

Sub Activate_Worksheet()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Activate       

End Sub

What is ActiveSheet? How to get the name of ActiveSheet?

ActiveSheet is the sheet which is currently active in the workbook. To get the Activesheet Name below is the code.

Sub Get_ActiveSheet_Name()

Dim sh As Worksheet

Set sh = ActiveSheet   

MsgBox sh.Name   

End Sub

How to get the sheets count in our workbook?

We can get the total sheets count of our workbook even they are hidden.

Below is the code:

Sub Get_Sheets_Count()

  Dim sheet_count As Integer

  sheet_count = ThisWorkbook.Sheets.Count

  MsgBox sheet_count    

End Sub

Worksheet Events:

 We can run a certain code on the worksheet event like: SelectionChange, Activate, Deactivate, Calculate etc.

To use the Worksheet Events, go to the Visual Basic Editor and double click on that sheet. Select the Worksheet in left drop-down box in place of General. In the right drop-down box, you can select the event as given in below image.