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
If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.
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 info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"I have just found an answer so I am going to answer myself.
When XLA add-in is loaded one can go to VB editor select that add-in in the project explorer and ThisWorkbook item/propety underneath and it's ""isAddIn"" property which can then be set to True. Immediatelly this will unhide hidden workseets belonging to this XLA in the Excel."
I forgot to mention that sheets already have the Visible property set to -1 ie. xlSheetVisible
"This all seems fine but I have created an XLA add-in that contains some worksheets with data and some VBA code. Now, I believe since I moved to a newer version of Excel when I open XLA document I can see the VBA code there but no worksheets and I am not able to change any data. There is no option to ""unhide"" anything because XLA workbook itself is hidden in Excel. How can I get to my worksheets in the XLA in order to change the data there ?
Thanks"
A Small Explanation: If the workbook is not protected then a person can unhide this worksheet if he knows that the Worksheet exists as we can use the sheets collection and we can refer to the property with sheets(i).visible property. (Thanks Mandar - my brother for pointing this mistake)
"I have just found an answer so I am going to answer myself.
When XLA add-in is loaded one can go to VB editor select that add-in in the project explorer and ThisWorkbook item/propety underneath and it's ""isAddIn"" property which can then be set to True. Immediatelly this will unhide hidden workseets belonging to this XLA in the Excel."
I forgot to mention that sheets already have the Visible property set to -1 ie. xlSheetVisible
"This all seems fine but I have created an XLA add-in that contains some worksheets with data and some VBA code.
Now, I believe since I moved to a newer version of Excel when I open XLA document I can see the VBA code there but no worksheets and I am not able to change any data.
There is no option to ""unhide"" anything because XLA workbook itself is hidden in Excel.
How can I get to my worksheets in the XLA in order to change the data there ?
Thanks"
A Small Explanation: If the workbook is not protected then a person can unhide this worksheet if he knows that the Worksheet exists as we can use the sheets collection and we can refer to the property with sheets(i).visible property. (Thanks Mandar - my brother for pointing this mistake)