I recently created a company excel workbook that used to have an a lot of rarely used columns/cells and was tasked with making it simpler but still universal.
I was able to create a checkbox with VBA code and when the box is checked it will show Sheet X. Unchecking it hides Sheet X. I made a similar checkbox inside a sheet to hide extra columns. Below is the VBA routine for hiding worksheets.
Private Sub CheckBox1_Click()
On Error Resume Next
ThisWorkbook.Sheets("Email Settings").Visible = CheckBox1.Value
End Sub
However the hangup now is that I can't email it to customers because our company Exchange server blocks Excel files with macros. I have sent it with Dropbox shared link however does not allow the customers to upload without an account and some customers may straight up block Dropbox.
Is there a non-VBA solution alternative to use an Active X checkbox to dynamically pick columns or sheets to hide?
Best Answer
Using Custom Views:
This example hides a column (
B
), a row (2
), and a sheet (HIDE THIS SHEET
).Once you have hidden what you want to hide, then add a custom view.
Unhide all of your data (you might want to add another custom view to make all of your data unhidden) then select your custom view and click show.
And you will see this: