How to create a hidden sheet when adding it with VBA?

In Excel, you can create a hidden sheet using VBA by setting the sheet’s `Visible` property to `xlSheetVeryHidden` or `xlSheetHidden` when adding it. Here’s how you can accomplish this:

   Sub AddHiddenSheet()
       Dim ws As Worksheet
       ' Add a new worksheet
       Set ws = ThisWorkbook.Worksheets.Add
       
       ' Name the new worksheet (optional)
       ws.Name = "HiddenSheet"
       
       ' Hide the new worksheet
       ' Using xlSheetHidden means it can be unhidden from the Excel interface
       ' xlSheetVeryHidden means it can only be unhidden through VBA
       ws.Visible = xlSheetVeryHidden
   End Sub

Open Excel and Press Alt + F11 to open the VBA editor.
Insert a Module:

In the VBA editor, go to `Insert` > `Module` to add a new module.

Write the VBA Code:

You can use the following code to add a new hidden sheet:

Run the Code:

You can run this code by pressing `F5` while in the VBA editor or by closing the editor and running the macro from Excel.

Explanation:

xlSheetVeryHidden: The sheet is not visible to the user and cannot be made visible from the Excel UI (`Format` > `Hide & Unhide`). This means you must use VBA to make it visible again.
xlSheetHidden: The sheet is hidden, similar to hiding a sheet manually in Excel. Users can unhide it via the Excel interface.

Unhiding the Sheet:

If you use `xlSheetVeryHidden` and want to make the sheet visible again, you need to change its `Visible` property back, like this:

Sub UnhideVeryHiddenSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("HiddenSheet")
    ws.Visible = xlSheetVisible
End Sub

Remember that the `Visible` property has three states:

`xlSheetVisible`: The sheet is visible.
`xlSheetHidden`: The sheet is hidden but can be unhidden from the UI.
`xlSheetVeryHidden`: The sheet is very hidden and can only be unhidden through VBA.

Feel free to adapt the code with the sheet name and visibility mode as needed for your specific project.

facebooklinkedin