Page 142 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
Microsoft Office Excel 2003 Programming Inside Out

Selection.Copy
Workbooks(szName).Activate
Sheets(intCount).Select
ActiveSheet.Paste
ActiveSheet.Name = szSheetName
Next intCount
End Sub

DisplayAlerts Property

The DisplayAlerts property determines if Microsoft Excel will display alerts while a macro is
running. By changing the value to False, Excel will choose the default response for all alerts
that would have been displayed; the one exception is when using the SaveAs method for
workbooks. When DisplayAlerts is True, the OverWrite alert has a default response of Yes, but
Excel will use No as a response when DisplayAlerts is False.
Excel will reset the DisplayAlerts property to the default value of True when the macro completes.
The following macro, which removes all unused worksheets from a workbook, sets the
DisplayAlerts property to False so that the user isn’t prompted every time the macro attempts
to delete a worksheet:

Warning You must run this workbook on a workbook that contains some data. If you run
this on a new workbook with nothing but empty sheets, you get VB runtime error 1004:
"A workbook must contain at least one visible sheet."

Sub RemoveEmptySheets()
Dim intCount As Integer

Application.DisplayAlerts = False
For intCount = Sheets.Count To 1 Step -1
Sheets(intCount).Select
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
If Selection.Address() = "$A$1" And ActiveCell = "" Then
Sheets(intCount).Delete
intCount = Sheets.Count + 1
End If
Next intCount
End Sub

RangeSelection Property

The RangeSelection property returns a Range object that represents the selected cells on the
selected workbook. Even when a graphic object is selected, the RangeSelection property will
return a range of cells, returning the last cells that were selected.
When cells are selected, the RangeSelection property and the Selection object both represent
the same cells. When a graphic object is selected, the RangeSelection property will still return
Chapter 6
the last cells that were selected.
116