| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
intersect question
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
intersect question
Just add an additional ) in BOTH places
Range("f6:f25") Range("f6:f25") ) -- Don Guillett Microsoft MVP Excel SalesAid Software "Cheetahke" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
intersect question
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
intersect question
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? "Tom Hutchins" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
intersect question
Describe "doesn't work" for us.
-- Rick (MVP - Excel) "Cheetahke" wrote in message ... 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? "Tom Hutchins" wrote: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
intersect question
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" wrote: Describe "doesn't work" for us. -- Rick (MVP - Excel) "Cheetahke" wrote in message ... 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? "Tom Hutchins" wrote: 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" wrote: 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 . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
intersect question
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. -- Rick (MVP - Excel) "Cheetahke" wrote in message ... 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" wrote: Describe "doesn't work" for us. -- Rick (MVP - Excel) "Cheetahke" wrote in message ... 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? "Tom Hutchins" wrote: 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" wrote: 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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
intersect question
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" wrote: 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" wrote: Describe "doesn't work" for us. -- Rick (MVP - Excel) "Cheetahke" wrote in message ... 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? "Tom Hutchins" wrote: 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" wrote: 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 . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
intersect question
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. "Cheetahke" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| intersect | Excel Programming | |||
| Intersect | Excel Programming | |||
| Intersect | Excel Programming | |||
| Intersect Formula??? | Excel Programming | |||
| Help with If Not Intersect | Excel Programming | |||
Hybrid Mode
