Hi Melody,
You are looking for the "SelectionChange" event. There are three related events for this: Worksheet.SelectionChange, Workbook.SheetSelectionChange, and Application.SheetSelectionChange.
I think that for your purposes, using Worksheet.SelectionChange is probably what you would want, since you already know which worksheet in which you are interested, but here's an example using all three as an example:
Public Class ExcelEventHandlingClass
Dim WithEvents xlApp As Excel.Application
Dim WithEvents myWorkbook As Excel.Workbook
Dim WithEvents myWorksheet As Excel.Worksheet
Sub New()
xlApp = New Excel.Application
xlApp.Visible = True
myWorkbook = xlApp.Workbooks.Add
myWorksheet = CType(myWorkbook.Worksheets.Add, Excel.Worksheet)
End Sub
Private Sub xlApp_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range) _
Handles xlApp.SheetSelectionChange
MessageBox.Show( _
"xlApp_SheetSelectionChange: " & _
Target.Address(External:=True) & " was selected")
End Sub
Private Sub myWorkbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range) _
Handles myWorkbook.SheetSelectionChange
MessageBox.Show( _
"myWorkbook_SheetSelectionChange: " & _
Target.Address(External:=True) & " was selected")
End Sub
Private Sub myWorksheet_SelectionChange( _
ByVal Target As Excel.Range) _
Handles myWorksheet.SelectionChange
MessageBox.Show( _
"myWorksheet_SelectionChange: " & _
Target.Address(External:=True) & " was selected")
End Sub
End Class
You can run the above as follows:
Dim o As ExcelEventHandlingClass
Private Sub StartExample()
o = New ExcelEventHandlingClass
End Sub
In this example, if you change the selection on the active worksheet, then all three event handlers fire and you get 3 message boxes. It's a bit annoying, lol, but it proves the point.
Of course you don't have to use WithEvents to hook up your event handlers, you could use AddHandler instead:
AddHandler xlApp.SheetSelectionChange, AddressOf xlApp_SheetSelectionChange
AddHandler myWorkbook.SheetSelectionChange, AddressOf myWorkbook_SheetSelectionChange
AddHandler myWorksheet.SelectionChange, AddressOf myWorksheet_SelectionChange
Once your handler has been called, it can extract values using automation. You could use the Range.Value property to get the values from a single cell, or return a 2 dimensional range of values from a multi-cell range. Of course you could just run SQL again, once you know which Row(s) you want, based on the selection, but I just thought I'd point out that you can extract the cell values directly.
Hope this helps!
Mike
Edit: Update to Melody's Reply
"Thank you so much for helping, Mike!"
No problem. :-)
"I need to get into the nitty-gritty
of the Target.Address(External:=True)
bit. I assume the target contains info
on what was selected? Can you provide
more info? Does it encapsulate a row
number or numbers of the rows
selected? Does it contain an index or
item property that can be used to get
at the column values? Does the
External=True argument just say this
is coming from non-managed code, or is
my assumption incorrect?"
That was just an example to show how to report the address of the Range that was selected. Let's look at the method signature for the Worksheet.SelectionChange event handler:
Private Sub myWorksheet_SelectionChange( _
ByVal Target As Excel.Range) _
Handles myWorksheet.SelectionChange
' Your code goes here...
End Sub
The event has one argument, which is the Target As Excel.Range argument. (The Application.SheetSelectionChange and Workbook.SheetSelectionChange events have a second argument stating on which worksheet the selection change occurred, but in the case of the Worksheet.Selection change event we already know on which worksheet the selection change occurred, so the parameter is omitted.)
The key is that you can make use of the Target As Excel.Range argument to determine what you want. To get the local address, which includes the Range address, but not the worksheet address (E.g. "A1:C3"):
Dim localAddress As String = Target.Address
To get the full path address (e.g. "[Book1.xls]Sheet1!A1:C3"):
Dim localAddress As String = Target.Address(External:=True)
To get the number of rows selected:
Dim numRows As Integer = Target.Rows.Count
To get the Row Index on the worksheet (remember: Excel workshets use Base 1 addressing!) for the top row of the range:
Dim topRowIndex As Integer = Target.Row
To get the row index for the last row:
Dim lastRowIndex As Integer = Target.Rows(Target.Rows.Count).Row
These are just some examples. You'll have to make use of the Excel VBA help files (or Google) to get more information on the members of the Range class.
Due to the holiday I may not be able
to respond to you right away, but I am
grateful for the help.
Slacker. Just kidding, have a great weekend. :-)