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

Posted: July 31, 2013 by kulshresthazone in Excel, VBA
Tags: , , ,

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.

Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s