Page 151 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
The Application Object

Figure 6-5. The Intersect method finds the cells representing the intersection of two ranges.
A popular use of the Intersect method is to determine if a cell selected by the user is within a
specific range. The TestPlants procedure prompts the user to select the largest monthly total
for Plants in the SalesByCategory.xls file and displays an error message if a cell is selected that
is not one of the monthly plant totals.

Sub TestPlants()
Dim strPrompt As String, rgePlants As Range, rgeIntersect As Range
strPrompt = "Please select the highest monthly total for Plants."
Set rgePlants = Application.InputBox(Prompt:=strPrompt, Title:=" Microsoft _
Office Excel 2003 Programming Inside Out", Type:=8)
Set rgeIntersect = Application.Intersect(Range("G2:G13"), rgePlants)
If rgeIntersect Is Nothing Then
strPrompt = "You did not select a Plant value".
MsgBox strPrompt, vbOKOnly + vbInformation
End If

End Sub

OnKey Method
The OnKey method allows you to create hotkeys that will run a particular routine when
pressed or block Excel from behaving normally when the key combination is pressed. The
Chapter 6
syntax for the OnKey method is
Application.OnKey(Key, Prodecure)

125