Selection Change Event Range Problem

[drrocket5292]

I have a selectionchange event sub that I've gotten help with in this forum before but this time I've got kind of an interesting problem that I'm not sure if its a bug with Excel or if I'm just doing something wrong. Anyways, heres the problem:

Theres certain areas on a worksheet that if the user selects that area, then my program will retrieve information from a database and display it as drilldown for the user. One area that this works is in Column A of a worksheet. The user can select as many cells with information in Column A and a for...Next loop will run SQL queries and retrieve the info theyre looking for. However if the person highlights the entirerow or selects mulitple columns then my SQL queries error out. How can I write something that would allow the user to select multiple rows in Column A but would ignore any other column thats been selected. Heres what I wrote:


Private Sub App1_SheetSelectionChange(ByVal sh As Object, ByVal target As Range)

dim ca as range
dim ce as range...

...ElseIf target.Column = 1 And target.Row >= x + 14 And target.Row <= Range("IV1001").Value Then
Set ca = target

If ca.Columns.count > 1 Then
Set ca = ca.Columns(1)
End If

For Each cel In ca
Debug.Print ca.Address, "CA"
Debug.Print cel.Address, "CEL"

If InStr(cel.Value, "January") > 0 Then
tmonth = 1
ElseIf InStr(cel.Value, "February") > 0 Then
tmonth = 2
ElseIf InStr(cel.Value, "March") > 0 Then
tmonth = 3
ElseIf InStr(cel.Value, "April") > 0 Then
tmonth = 4
ElseIf InStr(cel.Value, "May") > 0 Then
tmonth = 5
ElseIf InStr(cel.Value, "June") > 0 Then
tmonth = 6
ElseIf InStr(cel.Value, "July") > 0 Then
tmonth = 7
ElseIf InStr(cel.Value, "August") > 0 Then
tmonth = 8
ElseIf InStr(cel.Value, "September") > 0 Then
tmonth = 9
ElseIf InStr(cel.Value, "October") > 0 Then
tmonth = 10
ElseIf InStr(cel.Value, "November") > 0 Then
tmonth = 11
ElseIf InStr(cel.Value, "December") > 0 Then
tmonth = 12...


When I run a debug.print it tells me that cel is not an individual cell but is the entire range of ca. This is what I get in my immediate window:

$A$32:$A$38 CA
$A$32:$A$38 CEL

However, if i take out the if statement about ca = ca.columns(1) then I get:

$A$32:$A$38 CA
$A$32 CEL
$A$32:$A$38 CA
$A$33 CEL
$A$32:$A$38 CA
$A$34 CEL
$A$32:$A$38 CA
$A$35 CEL
$A$32:$A$38 CA
$A$36 CEL...

Why is it that when I define a range to a certain column that it changes what cel is and screws up my For...Next loop? I'm sure theres a better way to write this, I'm just so tired of writing this program at this point that I'm not thinking straight. Thanks for the help.

 

[PHV]

Have you tried to play with the Application.Intersect method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Set ca = application.intersect(target, range("A:A"))

forget the columns(1) bit.

Skip,
[sub]
[red][/red]
[/sub]

 

[drrocket5292]

Thanks guys. Worked like a charm. I appreciate the help.