Trick of the Week :: Custom Views for a Dashboard(Excel) Using VBA

While Preparing an Excel Dashboard one needs to handle a lot of hiding and unhiding of Sheets for various users. For Devloper all sheets should be unhidden and for user some of them should visible. We need different views of one workbook for different people developer, user, admin, etc.
We can handle this with using a Custom View property of the workbook with VBA

ActiveWorkbook.CustomViews.Add Viewname, PrintSettings(value True/False), RowColumnSettings(value True/False)

Let’s say for a developer all sheets and all row and column headings should be visible. Perform this task and Name this view using code as Admin_View
Syntax.

ThisWorbook.CustomViews.Add "Admin_View", True, True

Now set the workbook as per the end user (Hide some of the sheets and make row column header invisible) and give name to this view End_User

ThisWorbook.CustomViews.Add "End_User", True, True

Try this Command in you Code line.

ThisWorkbook.CustomViews("Admin_View").Show

and then try

ThisWorkbook.CustomViews("End_User").Show

Were you able to see the difference in Views.

Happy Coding ..   🙂

PS: To know more about Custom Views and how they work, read this.