VBA Codes for Working with Excel Spreadsheets : A Comprehensive Guide

In this article, we will explore a variety of VBA codes for working with Excel worksheets. Including hiding, showing, adding, and protecting worksheets in Excel, along with practical examples and explanations to enhance your understanding.

1. Hide and Unhide Worksheets

1.1. Hide/Unhide All Worksheets

Introduction: In Excel workbooks with multiple worksheets, managing visibility can become a repetitive task, especially when dealing with complex datasets or sensitive information. VBA enables you to hide or unhide all worksheets in just a few lines of code, regardless of their names or positions. This is particularly useful for creating custom views or protecting specific sheets during data presentations.

VBA Code:

Sub ShowOrHideAllSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible ' Change to xlSheetHidden or xlSheetVeryHidden to hide
    Next ws
End Sub

Explanation:

• xlSheetVisible: Makes the worksheet visible.
• xlSheetHidden: Hides the worksheet (users can unhide it from the sheet tab menu).
• xlSheetVeryHidden: Hides the worksheet so it can only be unhidden via VBA.

1.2. Hide a Specific Worksheet

Introduction: There are times when you need to hide a specific worksheet based on its name or position, such as confidential data or intermediate calculations that shouldn’t be accessed by regular users. The following VBA code allows you to precisely control which sheet to hide.

VBA Code:

Sub HideSpecificSheet()
    Sheets("Data").Visible = xlSheetHidden
End Sub

Explanation:

• Replace "Data" with the name of the sheet you want to hide.
• Always ensure at least one worksheet remains visible, as Excel does not allow all sheets to be hidden simultaneously.

1.3. Unhide a Specific Worksheet

Introduction: Sometimes, you need to make a previously hidden sheet visible again for further processing or review. Using VBA, you can unhide a specific worksheet by name or code name and even activate it for immediate use.

VBA Code:

Sub UnhideSpecificSheet()
    With Sheets("Report")
        .Visible = xlSheetVisible
        .Activate
    End With
End Sub

Explanation:

• Replace "Report" with the name of the sheet you want to unhide.
• The .Activate method makes the sheet the active sheet in the workbook.

2. Add a New Worksheet

Introduction: Adding new worksheets programmatically is a common requirement in dynamic report generation or automated workbook management. This VBA code snippet ensures that new worksheets are added with unique names and can be placed in specific positions for better organization.

VBA Code:

Sub AddNewSheet()
    Dim NewSheet As Worksheet
    Dim SheetName As String
    Dim ws As Worksheet

    Set NewSheet = ThisWorkbook.Worksheets.Add
    SheetName = "Data"

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = SheetName Then
            SheetName = SheetName & " (1)"
        End If
    Next ws

    NewSheet.Name = SheetName
End Sub

Explanation:

• This code adds a new sheet named “Data.” If the name already exists, it appends “(1)” to ensure uniqueness.
• You can specify the position using Before or After parameters in the .Add method.

3. Protect and Unprotect Worksheets

3.1. Protect a Worksheet

Introduction: Protecting worksheets is crucial when you need to prevent accidental changes to important data or formatting. VBA allows you to add specific protection options, such as enabling cell selection while preventing structural modifications.

VBA Code:

Sub ProtectSheet()
    Worksheets("Sheet1").Protect _
        Password:="1234", _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        AllowFormattingCells:=False, _
        AllowFormattingColumns:=False, _
        AllowFormattingRows:=False
End Sub

Explanation:

• Password: Sets a password for the protection.
• DrawingObjects, Contents, and Scenarios: Determine what elements are protected.
• AllowFormattingCells, AllowFormattingColumns, and AllowFormattingRows: Restrict specific user actions.

3.2. Unprotect a Worksheet

Introduction: When you need to modify a protected sheet, unprotecting it programmatically is much faster than manual methods, especially in workbooks with multiple protected sheets.

VBA Code:

Sub UnprotectSheet()
    Worksheets("Sheet1").Unprotect Password:="1234"
End Sub

Explanation:

• Replace "Sheet1" with the name of the sheet to unprotect.
• Ensure the password matches the one used during protection.

Conclusion

These VBA codes offer powerful ways to automate common worksheet operations in Excel, such as hiding, unhiding, adding, protecting, and unprotecting worksheets. By incorporating these codes into your workflows, you can save time, reduce errors, and enhance productivity. Experiment with these examples to understand their potential and customize them to fit your specific needs.

See more:
– VBA code “behind” a worksheet or a workbook may not work in Excel
– Guide to Splitting Sheets in Excel into Separate Files Using VBA