VBA Blogs: Unhiding and Unprotecting Sheets

Today, we’re going to look at a common application of VBA involving a range of skills that we’ve gone through.  We are going to create a VBA script that will look through all the sheets of a workbook and unhide the worksheets, unprotect them if necessary, and unhide the columns and rows.  This is useful to help identify the entirety of a file.

The first step involves declaring a Worksheet variable.  We can do this with the code:

We can then loop through all of the worksheets using the following loop:

Inside the loop, each worksheet will be defined by the variable “ws”.  With this, we can perform additional commands, such as:

Hopefully all of these lines of code make logical sense!  If not, they perform the following actions:

• Unhide the relevant sheet
• Select and activate the sheet
• Unprotect the sheet if it is protected
• Take all of the rows and unhide them
• Take all of the columns and unhide them

To put the whole piece of code together, it would look like the following:

Next week is the Final Friday Fix for April, but the following week, we will look at what happens if we run into an error when the code is being run, and how we can modify this routine to handle those errors.  Happy Friday!