Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
To prevent the error message that appears when no workbooks are visible, I added some error
checking. I used On Error Resume Next to ignore the error and then checked the value of
Err . If Err is not equal to 0 , it means that an error occurred. Therefore, the procedure ends. The
error-checking code is
On Error Resume Next
SheetCount = ActiveWorkbook.Sheets.Count
If Err <> 0 Then Exit Sub ‘ No active workbook
It occurred to me that I could avoid using On Error Resume Next . The following statement is
a more direct approach to determining whether a workbook isn’t visible and doesn’t require any
error handling. This statement can go at the top of the SortSheets procedure:
If ActiveWorkbook Is Nothing Then Exit Sub
There’s usually a good reason that a workbook’s structure is protected. I decided that the best
approach was to not attempt to unprotect the workbook. Rather, the code should display a
message box warning and let the user unprotect the workbook and re-execute the macro. Testing for
a protected workbook structure was easy — the ProtectStructure property of a Workbook
object returns True if a workbook is protected. I added the following block of code:
‘ Check for protected workbook structure
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & “ is protected.”, _
vbCritical, “Cannot Sort Sheets.”
Exit Sub
End If
If the workbook’s structure is protected, the user sees a message box like the one shown in
Figure 9-11.
Figure 9-11: This message box tells the user that the sheets cannot be sorted.
 
Search JabSto ::




Custom Search