Excel VBA Macro

How To Hide an Excel Worksheet

Posted on the September 24th, 2008 under Workbook and Worksheet by Poer @ Excel VBA/Macro

Some time we want to hide a certain Excel Worksheet from view, and it’s a common practice to use select the Worksheet, go to menu Format > Sheet > Hide.

Using the method explained above is right, but unfortunately, others people can easily unhide the Worksheet using the same method, only this time, instead of selecting Hide, they simply need to choose UnHide, and all the Worksheets in hiding will be revealed.

The above method only working in Microsoft Excel before 2007, in Excel 2007, the menu to hide and unhide columns/cells/worksheets are hidden by default, but we can add this menu into Excel Quick Access Toolbar (tiny menu at top left corner of the window), by accessing menu Customize Quick Access Toolbar (tiny down arrow on the right), select More Commands… » Choose commands from Home Tab » and select menu Hide & Unhide.

The other method on how to hide our Excel Worksheet, more secured, and not really well known by people is using Worksheet xlSheetVeryHidden properties.

To perform this method, first we need to go to Microsoft Visual Basic Editor (ALT+F11), in the project explorer (if the explorer is not showing, click CTRL+R), select Worksheet that we want to hide, then go to Properties Windows (F4), and in the Visible properties, select 2 – xlSheetVeryHidden like in this picture:

If we follow all the guide above, the Worksheet will be disappear/hidden from view, even when we use menu Format > Sheet > UnHide, the Worksheet will not be displayed in the list of Worksheets in hiding.

We can also get the same result using Excel VBA macro, like this:

Private Sub Workbook_Open()
    Worksheets("Sheet1").Visible = xlSheetVeryHidden
End Sub

With a simple one line of code, Sheet1 will automatically set to VeryHidden each time the Excel Workbook was opened. Change “Sheet1″ with your Worksheet name.

FIN

Create New Excel Worksheet With VBA

Posted on the September 13th, 2008 under Workbook and Worksheet by Poer @ Excel VBA/Macro

The Excel VBA macro below will create a new Excel Worksheet called ‘RawData’ or we can use msgbox to ask for the Worksheet name if needed.

If there is already a Worksheet called RawData, user will be ask whether they want to use the old Worksheet and cancel new Worksheet creation, or delete the old Worksheet and continue creating a new blank Worksheet.

Sub CreateNewWorksheet()

    Dim oSheet As Worksheet, vRet As Variant

    On Error GoTo errHandler

    'creating a new excel worksheet called RawData
    Set oSheet = Worksheets.Add
    With oSheet
        .Name = "RawData"
        .Cells(1.1).Select
        .Activate
    End With
    Exit Sub

errHandler:

    'if error due to duplicate worksheet detected
    If Err.Number = 1004 Then
        'display an options to user
        vRet = MsgBox("Worksheet called 'RawData' is already exist, " & _
            "click yes to continue creating new Worksheet and delete the old one, " & _
            "or click no to go to the old worksheet.", _
            vbOKCancel, "Duplicate Worksheet")

        If vRet = vbOK Then
            'delete the old worksheet
            Application.DisplayAlerts = False
            Worksheets("RawData").Delete
            Application.DisplayAlerts = True

            'rename and activate the new worksheet
            With oSheet
                .Name = "RawData"
                .Cells(1.1).Select
                .Activate
            End With
        Else
            'cancel the operation, delete the new worksheet
            Application.DisplayAlerts = False
            oSheet.Delete
            Application.DisplayAlerts = True
            'activate the old worksheet
            Worksheets("RawData").Activate
        End If

    End If

End Sub