In this article, we will create a macro for hiding the Excel sheet. Excel sheet is made hidden by changing its properties using VBA code. Hidden Excel sheet cannot be made visible by using unhide button on Excel application.
In this example, we have created a gate registration form.
When user enters the details in the form and clicks submit button, details are saved in the “Data” sheet. “Submit” button is assigned “SubmittingDetail” macro.
“Data” sheet can be made hidden or unhidden by clicking the “Data sheet” button. If “Data” sheet is hidden, it cannot be made visible by using unhide option available within Excel sheet.
Sheet hidden by using “Data sheet” button, remains visible in project explorer in the VBE(Visual Basic Editor) and Sheet properties of hidden sheet is set to xlSheetVeryHidden.
Sheet can also be made very hidden without using VBA code. For that, we have to open the VBE and set the visible property of sheet in properties window to 2 – xlSheetVeryHidden.
For hiding a sheet, so that nobody can unhide it without knowing the password for the Vb Project, simply hide the file either by using VBA code or change the visible property of sheet to 2 i.e. xlsheetveryhidden and lock the Vb Project along with the password.
For protecting Vb Project with password, right click on the Microsoft Excel Object > Select VBAProject Properties… > VBAProject Properties dialog box will appear.
In VBAProject Properties dialog box, go to Protection tab > check the box for locking the project > assign the password for the project. Password which we assign for the attached file is “register”. One can assign any password for the project.
“Data sheet” button is used for both hiding and unhiding the sheet. “Data sheet” button is assigned “ToggleHidingDataSheet” macro. When user will click the button and if the file is hidden then sheet is made visible and if the sheet is visible then sheet is hidden.
Sheet can be made visible either by clicking “Data sheet” button or by changing the visible property back to 0 i.e. xlSheetNormal using the password for the VBProject.
Please follow below for the code
Sub SubmittingDetail() Dim LastRow As Long 'Finding the Last row in the "Data" sheet 'where data needs to be inserted LastRow = Sheets("Data").Range("A1").SpecialCells(xlLastCell).Row + 1 'Inserting data in "Data" sheet With Sheets("Data") 'Assigning serial number .Range("A" & LastRow) = LastRow - 1 'Inserting data in "Data" sheet from range F15 to J15 .Range("B" & LastRow & ":F" & LastRow) = Range("F15:J15").Value End With 'Deleting the cell content in the range Range("F15:J15").Select Selection.ClearContents Range("F15").Select End Sub Sub ToggleHidingDataSheet() 'Checking whether sheet is currently hidden If Sheets("Data").Visible = xlVeryHidden Then 'Code for making the sheet visible Sheets("Data").Visible = True Else 'Code for hiding the sheet Sheets("Data").Visible = xlVeryHidden End If End Sub
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at email@example.com