Databases Reference
In-Depth Information
Why? Probably because if only one visible sheet exists in a Workbook, it
be the active sheet.
(This really should not happen and is a poor design on the part of Microsoft. If the sheet is already
activated, and a command is issued to activate it, then nothing should happen.) Because this error
can occur, it is handy to have a function that can determine if a Workbook contains only a single
sheet. The function OneVisibleWorksheet provides this capability.
must
Function OneVisibleWorksheet(wkbook) As Boolean
'Determines if Only One Visible Worksheet in Workbook (wkbook)
'Useful when calling Sheets().Activate
Workbooks(wkbook).Activate
For Each wks In Worksheets
If wks.Visible = 1 Then
TotalVisWkshts = TotalVisWkshts + 1
If TotalVisWkshts > 1 Then
OneVisibleWorksheet = False
Exit Function
End If
End If
Next wks
OneVisibleWorksheet = True
End Function
If the possibility exists that a Workbook will only have one Worksheet, this function can be
called before the activate method is utilized on a sheet. If the function returns True, then the activate
method should not be executed. The following line of VBA code illustrates such a check:
If OneVisibleWorksheet(ActiveWorkbook.Name) = False then
Sheets(2).activate
Now suppose Sheet2 has its name changed to RawData. To activate the sheet now named
“RawData,” the following commands would have to be typed:
Sheets(2).Activate
or
Sheets(“RawData”).Activate
What has happened here is that even though Sheet2 had its string name changed from “Sheet2” to
“RawData,” its numerical reference number does not change. The numerical reference number is indic-
ative of the order in which a sheet was created, and will never change even when a sheet is renamed.
While on the subject of renaming sheets, a sheet can be renamed at will by using the (.Name)
property. Here are two examples.
ActiveSheet.Name = "pKa Data"
Sheets(1).Name = "ELogD Data"
To explicitly reference a sheet allows a user to change values on a sheet
without that sheet being
the active sheet
. To do this, both the Workbook and the Worksheet, and possibly the range, must
be referenced in the command. This form of referencing will be covered in this chapter in more
detail when Ranges and Cells are discussed. However, an example of using an explicit reference
to change the name of a Worksheet in any Workbook (not just the active Workbook) is as follows:
Workbooks("Book2.xls").Sheets("RawData").Name = "Analyzed Data"
