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.
- xlSheetHidden is used to normal hide and worksheet can be un-hidden from excel by using sheet unhide (Alt O+H+U).
- 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
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.
Example: Let’s say we need to put the “Hello, you changed the selection” message on the selection change.
Below is the code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) MsgBox "Hello, you changed the selection" End Sub