continuing code on another line?

[neowok]

i have the following code

With Target
If Not Intersect(.Cells
Range("$C$7:$E$7,$G$7:$I$7,$K$7:$M$7,$O$7:$Q$7,$S$7:$AC$7, _
$AE$7:$AG$7,$AI$7:$AK$7,$AM$7:$AO$7,$AQ$7:$AS$7,$AU$6:$AW$7
$AY$7:$BA$7, $BC$7:$BE$7, _
$BG$6:$BI$7, $BK$6:$BM$7, $BO$6:$BQ$7, $BS$6:$BU$7, $BW$7:$BY$7
$CA$7:$CC$7, _
$CE$7:$CG$7, $CI$7:$CK$7, $CM$7:$CO$7, $CQ$7:$CS$7, $CU$6:$CW$7
$CV$7:$DA$7, _
$DC$7:$DE$7, $DG$7:$DI$7, $DK$7:$DM$7, $DO$7:$DQ$7, $DS$3:$DU$5")) _
Is Nothing Then
Select Case .Address

Case "$C$7:$E$7"
'stanmore
Worksheets("3.data").Select
Worksheets("3.data").ComboBox1_DropButtonClick
Worksheets("3.data").ComboBox1.ListIndex = "21"
Worksheets("3.data").ComboBox1_Click
etc etc

and excel is complaining that the line starting with $AE$7 starts wit
an invalid character (even though I have the _ on the line before t
say it continues on the next line). Am i missing something

 

[Bob Phillips]

You need to add closing quotes and re-open them, like

If Not
Intersect(.Cells,Range("$C$7:$E$7,$G$7:$I$7,$K$7:$M$7,$O$7:$Q$7,$S$7:$AC$7,"
& _
"$AE$7:$AG$7,$AI$7:$AK$7,$AM$7:$AO$7,$AQ$7:$AS$7,$AU$6:$AW$7, $AY$7:$BA$7,
$BC$7:$BE$7, " & _

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[neowok]

thought i was forgetting a symbol somewhere

ive now changed it to

With Target
If Not Intersect(.Cells
Range("$C$7:$E$7,$G$7:$I$7,$K$7:$M$7,$O$7:$Q$7,$S$7:$AC$7," & _
"$AE$7:$AG$7,$AI$7:$AK$7,$AM$7:$AO$7,$AQ$7:$AS$7,$AU$6:$AW$7
$AY$7:$BA$7, $BC$7:$BE$7," & _
"$BG$6:$BI$7, $BK$6:$BM$7, $BO$6:$BQ$7, $BS$6:$BU$7, $BW$7:$BY$7
$CA$7:$CC$7," & _
"$CE$7:$CG$7, $CI$7:$CK$7, $CM$7:$CO$7, $CQ$7:$CS$7, $CU$6:$CW$7
$CV$7:$DA$7," & _
"$DC$6:$DE$7, $DG$7:$DI$7, $DK$7:$DM$7, $DO$7:$DQ$7, $DS$3:$DU$5")) _
Is Nothing Then


however excel now complains that "method range of object _workshee
failed" and highlights that entire block in yellow, and im sure its no
the & _ causing it cos it still does it when i stick all that into
line hmmmmmm

 

[Bob Phillips]

It's the damn dollars mate.

With Target
If Not Intersect(.Cells, Range("C7:E7,G7:I7,K7:M7,O7:Q7,S7:AC7," & _
"AE7:AG7,AI7:AK7,AM7:AO7,AQ7:AS7,AU6:AW7,AY7:BA7, BC7:BE7," & _
"BG6:BI7, BK6:BM7, BO6:BQ7, BS6:BU7, BW7:BY7,CA7:CC7," & _
"CE7:CG7, CI7:CK7, CM7:CO7, CQ7:CS7, CU6:CW7,CV7A7," & _
"DC6E7, DG7I7, DK7M7, DO7Q7, DS3U5")) _
Is Nothing Then

works

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[neowok]

it removes the error yes but without the dollars, it fails to recognis
it as the correct address possibly because the 'target' parameter o
selectionchange contains the dollars, but either way it fails t
recognise when those cells are selected unless the dollars are i
there.

damni

 

[Bob Phillips]

Rubbish,<G>. Cell A1 is Cell A1. Target will only contain the dollars if you
take the default address. You can just as easily see it with a column dollar
only, row dollar only, or neither.

I tested it and I can get an intersect on each of those cells are selected.
If you have a problem, it is something else.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[neowok]

well ive tried it and it doesnt work

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
Select Case .Address
Case "C7"
Worksheets("3.data").Select
Worksheets("3.data").ComboBox1_DropButtonClick
Worksheets("3.data").ComboBox1.ListIndex = "21"
Worksheets("3.data").ComboBox1_Click
end select
end with

does not do anything at all when C7 is selected

change it to $C$7 and it then attempts to perform the case statement.

i have excel 2002 sp2 if that makes any difference

 

[Bob Phillips]

You've not only moved the golaposts, you've moved tyhe pitch. Previously you
were checking for range intersect, now you have decided to check a single
cell address. For that you need the damn dollars

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
Select Case .Address
Case "$C$7"
Worksheets("3.data").Select
Worksheets("3.data").ComboBox1_DropButtonClick
Worksheets("3.data").ComboBox1.ListIndex = "21"
Worksheets("3.data").ComboBox1_Click
end select
end with

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[neowok]

im afraid i havent moved a thing, if you look at the original post an
the code i included youll see the point of the intersect is to make th
case statements below it more efficient by only trying to do it if th
cell is in the correct range. the point of the code is to use the cas
statements to perform actions.

remove the dollars from the range intersect, but leave the dollars i
the case statements and they do not fire. remove the dollars from th
range intersect AND the case statements, again they do not fire. th
only way they work is with the dollars in both.

What exactly the dollars are meant to mean I have no idea, all I kno
is I've had to use them or it refuses to fire once the given range i
selected

 

[Bob Phillips]

You have because our previous correspondence was on the intersect code. I
said nothing about the case statement (guess why, because there was nothing
to say), but you changed it anyway.

Paragraph 2 - that must be wrong.

The dollars refer to man absolute row/column address. So if you put say
=$A17 in A1 and then copy across to H1, every cell will contain =$A17. Put
=A17 in C1, and copy across, and they shift to =B17, =C17, etc.

Intersect works on a range, and a range in VBA is defined without dollars
(although I have never tried to put dollars in, and I admit to being
surprised that you can't, wouldn't have expected any different
interpretation, but still surprised). When you try to use the .Address
property, this is a string, and this can be expressed in absolute,/relative,
or relative terms. If you do not understand these properties you should look
them up in Help, it tells you there that rowabsolute and columnabsolute
default to True.

This code works

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If Not Intersect(.Cells, Range("C7:E7,G7:I7,K7:M7,O7:Q7,S7:AC7," & _
"AE7:AG7,AI7:AK7,AM7:AO7,AQ7:AS7,AU6:AW7,AY7:BA7, BC7:BE7," & _
"BG6:BI7, BK6:BM7, BO6:BQ7, BS6:BU7, BW7:BY7,CA7:CC7," & _
"CE7:CG7, CI7:CK7, CM7:CO7, CQ7:CS7, CU6:CW7,CV7A7," & _
"DC6E7, DG7I7, DK7M7, DO7Q7, DS3U5")) _
Is Nothing Then
Select Case .Address
Case "$C$7": MsgBox "~C7"
Case "$Q$7": MsgBox "~Q7"
Case Else: MsgBox Target.Address(False, False)
End Select
End If
End With

End Sub

If I select C7, guess what I get. A MsgBox with ~C7, Q7 gives ~Q7. ANy other
cell in the range gives its relative reference.

This also works in the same way

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
If Not Intersect(.Cells, Range("C7:E7,G7:I7,K7:M7,O7:Q7,S7:AC7," & _
"AE7:AG7,AI7:AK7,AM7:AO7,AQ7:AS7,AU6:AW7,AY7:BA7, BC7:BE7," & _
"BG6:BI7, BK6:BM7, BO6:BQ7, BS6:BU7, BW7:BY7,CA7:CC7," & _
"CE7:CG7, CI7:CK7, CM7:CO7, CQ7:CS7, CU6:CW7,CV7A7," & _
"DC6E7, DG7I7, DK7M7, DO7Q7, DS3U5")) _
Is Nothing Then
Select Case .Address(False, False)
Case "C7": MsgBox "~C7"
Case "Q7": MsgBox "~Q7"
Case Else: MsgBox Target.Address(False, False)
End Select
End If
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)