I have Excel for Mac 2016, and even though the Visual Basic Editor is pretty much the worst, I've nearly completed a big data entry tool without relying on coding.
However, I have two core sheets with private data that feed into the tool but should not be accessible by the tool's users as-is. I'd like to make the sheets VeryHidden so I can pull them up as needed, but regular users cannot.
How is this possible without the Project Editor's properties feature? Can I write a couple of lines of code that creates a VeryHidden condition that I can make visible as needed?
I have a macro that hides a couple of sheets, but when I tried to modify that code into the code below, it didn't work. I'm not at all familiar with coding and having a really hard time picking it up due to the lack of a Project Editor and other seemingly basic VBA editing functions, so any help would be appreciated!
Sub VeryHidden()
'
'
'
'
Sheets("AutoPop").Select
ActiveWindow.SelectedSheets.VeryHidden = True
End Sub
Hi,
Use:
ThisWorkbook.Worksheets("Sheet1").Visible=2 ' xlSheetVeryHidden
Parameters:
xlSheetVisible=-1
xlSheetHidden=0
xlSheetVeryHidden=2
Would I need to create a module within the workbook for this? Placing the code in ThisWorkbook and one of the relevant sheets doesn't seem to create the VeryHidden feature.
Hi Alexandra,
Visible is a property of the worksheet object, not a feature. You can place the code in any sheet module you want, or in a regular module. Make sure you change the sheet name, to the name of the sheet you want to view/hide.
For example, this code will switch from view/hide, based on the current value of the visible property:
Sub ViewHide()
If ThisWorkbook.Worksheets("Sheet1").Visible = 2 Then
ThisWorkbook.Worksheets("Sheet1").Visible = -1
Else
ThisWorkbook.Worksheets("Sheet1").Visible = 2
End If
End Sub
Right, I understand it is a property, and feature was just not the right word. But without the actual ability to toggle the features through the VB editor, I still don't know where to enter the code.
Basically, I have two sheets that I want to be VeryHidden, so other users can't see them if unhiding sheets, but I can pull up the VBE to switch them to visible if I need to update them (I'll need to update them pretty frequently).
Right now, the sheets are hidden. Using your code above, changing the sheet name to the real sheet name and a Visible = 0 code, I can still see the hidden sheets when I click Unhide.
I have no idea what I'm doing wrong.
Catalin Bombea said
Parameters:
xlSheetVisible=-1
xlSheetHidden=0
xlSheetVeryHidden=2
If you're using the value 0, you're just switching from very hidden to hidden, not to visible. I already sent you all the possible values for this parameter.
Thanks a lot Mr. Catalin for useful information ..
Press Alt + F11 to open the VBE Editor >> From Insert >> Select Module >> Then paste the following code
The code will toggle between showing and hiding the sheets (Sheet1 & Sheet2) .. Change the sheet names to suit
Here's the code
Dim ws As Worksheet
'Sheet1 And Sheet2 Are The Names Of The Target Sheets That You Need To Deal With
For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2"))
If ws.Visible = xlSheetVeryHidden Then
ws.Visible = xlSheetVisible
Else
ws.Visible = xlSheetVeryHidden 'Or You Can Use 2 Instead Of xlSheetVeryHidden
End If
Next ws
End Sub
