Microsoft Office Tutorials and References
In Depth Information
Example 19-9. The cmdSelect_Click Event Procedure
cRows = rngSearch.Rows.Count
If rngSearch.Areas.Count > 1 Or _
(cColumns <> 1 And cRows <> 1) Then
lblSearchRange.Caption = "Requires (portion of) single
column or row."
cmdSelect.Enabled = False
Exit Sub
End If
' If single cell then expand to used portion of column
If cColumns = 1 And cRows = 1 Then
Set rngSearch = Application.Intersect( _
rngSearch.EntireColumn, ActiveSheet.UsedRange)
End If
ElseIf optEmpty Or optNotEmpty Then
' If selection is single cell then expand to used range
If rngSearch.Cells.Count = 1 Then
Set rngSearch = ActiveSheet.UsedRange
End If
End If
lblSearchRange.Caption = "Search Range: " & _
rngSearch.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End Sub
When the user hits the Select button, the action begins, based on the user's selection. Thus, we
should call a different procedure based on which option button is selected. After the new selection
is made, the Select button is disabled. Since the CompleteRows and CompleteColumns features
are still available, however, we do not want to dismiss the main dialog. The code to handle the
Select button is shown in Example 19-9 .
Example 19-9. The cmdSelect_Click Event Procedure
Private Sub cmdSelect_Click()
' Read option buttons and
' call appropriate procedure
If optDifferent Then
SelectIfDifferent
ElseIf optSame Then
SelectIfSame
ElseIf optEmpty Then
SelectIfEmpty
ElseIf optNotEmpty Then
SelectIfNotEmpty
End If
cmdSelect.Enabled = False
End Sub
The SelectIfDifferent procedure is shown in Example 19-10 . It basically searches through
the rngSearch range, looking for cells whose contents differ from the previous cell. Since we do
not know whether the range is a column or row (or portion thereof), it is easier to use a double
For loop. However, it would be a bit more efficient to split the code into two cases ( cColumns =
1 and cRows = 1). Note that the first cell needs a bit of special attention, since we want to include
it in the selection. The selection is accumulated in a Range object variable called rngMatch ,
using the Union function. However, we always need to consider the possibility that rngMatch is
 
 
Search JabSto ::




Custom Search