Mac – How do dynamically hide cells/sheets without macros

macrosmicrosoft excelvba

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).

Unhidden

Hidden

Once you have hidden what you want to hide, then add a custom view.

Starting Custom View

Adding 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.

Selecting Custom View

And you will see this:

Hidden

Related Question