Finalize Call Panel Report - Add Filtering, Remove Opt-out MD Info, Protect Sheets, and Some More

Let's add some final features to our national level call panel file and call it a day.

One is to add the function for the end-user to filter records by specialty. For example if the user chooses "Spec3", then only those MDs with a specialty of "Spec3" would show up. All others will be hidden. Not the most fancy, but many times it would prove to be useful for people not familiar with using Auto-Filter.

To do this, we create another drop-down list which allows the user to choose one of the six specialties, and add the following code. You can download the workbook here.

In the above code we use "RowHeight" property to display/hide rows. Here we set "RowHeight" to 13 (default row height on my computer) to display the rows; and to 0 to hide them. If necessary, this property can also be read first to determine what is the default row height on the end-user's machine.

Let's move on to another topic: Opt-out physicians - doctors who do not wish to share their information (can be sales data, can be demographic info) with any sales reps. Biotech managers are legally required to remove such information in any reports sent to the sales force. In our example, let's assume that we have a list of 400 physicians who are opt-out doctors. The only information we are allowed to show is their MD#, Region and Territory, Specialty and Name. All other fields need to be blocked. The VBA code is shown below:

We can see from the above code that this 400 Opt-Out physician list is on sheet "DropDownList" and occupies the range "G1:G400". If you do not wish to show this list to the sales force, you can choose to hide the sheet (see below), or clear the list by using .Range().ClearContents, or have the list resided on a different Excel file.

Finally, before we send out the file to everybody, we would like to hide the sheets "Ter List" and "DropDownList" by using:

.Sheets(1).Visible = False

If you also prefer to hide column and row headings ("A", "B", "C", etc.) you can use:

ActiveWindow.DisplayHeadings = False

Some people prefer to protect all sheets in the workbook with a password so that no (unintentional) changes can be made to any contents within the sheets. To do this, use:

.Sheets(1).Protect(Password)

Word of caution. After you protect a sheet, if a Macro needs to write to/change the sheet, an error will be generated. To avoid this, use:

.Sheets(1).UnProtect(Password)

to temporarily unprotect the sheet, do your thing, then protect it again.

Finally, to protect the VBA code itself with a password (after all, any person with some VBA knowledge can easily go through your code and retrieve the password contained in the above Protect and UnProtect methods), go to VBA Editor, right click on the VBA Project, and choose "VBAProject Properties...".