intersect question

[Cheetahke]

Is it possible to use more than once the intersect methode? I tried next
code, but it does not work. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set LastCellChanged = Target
If not intersect(LastCellChanged, Range("f6:f25") is nothing then
'Do something
else
If not intersect(LastCellChanged, Range("h2") is nothing then
'Do something else
else
'Do 3rd thing
end if
end if

End Sub

 

[Don Guillett]

Just add an additional ) in BOTH places
Range("f6:f25")
Range("f6:f25") )

 

[Tom Hutchins]

You have clearly retyped some of your code in your post. The code you
submitted is missing a closing right parentheses each time after the
Intersect statement. This works for me:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set LastCellChanged = Target
If Not Intersect(LastCellChanged, Range("f6:f25")) Is Nothing Then
MsgBox "A"
Else
If Not Intersect(LastCellChanged, Range("h2")) Is Nothing Then
MsgBox "B"
Else
MsgBox "C"
End If
End If
End Sub

I assume you have some reason to assign Target to a range variable
LastCellChanged each time the event fires, instead of just using Target.

Since this is a worksheet event, it needs to be placed on the code module
for the particular worksheet where you want this to work, not in ThisWorkbook
or a general VBA module.

Hope this helps,

Hutch

:

 

[Cheetahke]

Hi Tom,

Thanks for the remarks. I had the right code in my worksheet, but mistyped
it here.
I've tried again, and it still doesn't work. I don't know what's wrong. Is
it possible that it's because I work with Excel 2003?

 

[Rick Rothstein]

Describe "doesn't work" for us.

 

[Don Guillett]

Tested in xl2003

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set LastCellChanged = Target

If Not Intersect(LastCellChanged, Range("f6:f25")) Is Nothing Then
MsgBox "f6"
'Do something
Else
If Not Intersect(LastCellChanged, Range("h2")) Is Nothing Then
MsgBox "h2"
'Do something else
Else
MsgBox "none of the above"
'Do 3rd thing
End If
End If

End Sub

 

[Cheetahke]

When I change the value in cell "f6" on the specific worksheet (first
worksheet with the code behind), normaly that value must change in several
cells in other worksheets (worksheets 2, 3 and 4), but when I check the other
worksheets, nothing has changed.

 

[Rick Rothstein]

Almost sounds like Calculations are set to Manual. From any worksheet, click
Tools/Options and select the Calculation tab on the dialog box that comes
up... if Automatic is not selected in the Calculation section at the top
then select it and hit the OK button.

 

[Cheetahke]

Sorry Rick, Calculations are not set to Manual but Automatic.

 

[Tom Hutchins]

Are you typing a value into F6 or is it changing as the result of a formula?
A cell's value changing as the result of its formula recalculating won't
trigger a Worksheet_Change event.

Hutch

 

[Cheetahke]

I type a value into F6. I don't use formula's in the project I'm making
because I'm using a Dutch version, and the computer, where this workbook is
going to be used on, has an English version of Excel.
In the past, I've noticed that sometimes the formula's didn't work when I
copied the workbook between the two computers.
I've noticed something else... when I use only the messagebox after the
intersect methode, it works, but when I remove the messagebox, and I put some
code after it, that what needs to be done, does not happen.
I'll give an example here:

In range H2 comes a date, which is generated in vbe. In range F6, the user
can type a value. This value needs to be copied to another sheet, depending
on the year of the date in H2.
Code behind:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Set LastCellChanged = Target
dDateChanged = cdate(range("h2").value
iMonthChanged = month(dDateChanged)
iDayChanged = day(dDateChanged)
If Not Intersect(LastCellChanged, Range("f6:f25")) Is Nothing Then
If year(dDateChanged) >= year(now()) then
For year(dDateChanged) = year(now()) to year(now()) + 1
Select case iMonthChanged
Case 1
(Here comes the code for putting the value,
that can be typed in range F6 to F25, on the specific worksheet, in the
specified range)

Case 2
(Here comes the code for putting the value,
that can be typed in range F6 to F25, on the specific worksheet, in the
specified range)

... (until case 12)
end select
next
end if
Else
If Not Intersect(LastCellChanged, Range("h28:al30")) Is Nothing Then
MsgBox "B"
Else
MsgBox "C"
End If
End If
End Sub

I have different ranges that needs to be controled, and ranges that changes
by selecting a value in a combobox. Those ranges don't need to be controled
when they changes, because that's done in the code behind those comboboxes.

 

[Cheetahke]

Thank you all for your replies. It works fine now. I had some code behind one
of the intersect methods which was not necessary. Since I've removed that
code, it works fine.

 

[raeganhill]

Wish you had uploaded your resolved code