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 |
#7
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 . |
#8
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
intersect question
Sorry Rick, Calculations are not set to Manual but Automatic.
"Rick Rothstein" wrote: 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 . . |
#10
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 . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
intersect question
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 he 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. "Tom Hutchins" wrote: 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 . |
#12
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 |