UtterAccess Discussion Forums > Using Intersect In Workbook Sheet Change Event

Full Version: Using Intersect In Workbook Sheet Change Event

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

ipisors

May 25 2012, 01:24 PM

I need to use workbook event Sheet Change. I want to determine if the cell being changed is in M:M. I can't use code behind a worksheet, it needs to be in the workbook object.

what am I doing wrong - i keep getting method "Intersect" failed.

in plain english, what I want to do is, if the target is in column M and the value is going to Completed or Needs Follow Up, I want to do some things. 1) remove matching items from rest of workbook, (offset negative 12 horizontal), and 2) color font, depending on which value.

CODE

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ws As Worksheet
Dim myVin As Range
If Not Intersect(Target, Range("m:m")) Is Nothing Then

     If Target.Value = "Completed" Then

        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> ActiveSheet.Name Then
                Set myVin = ws.Cells.Find(what:=Target.Offset(0, -12).Value)
                If Not myVin Is Nothing Then 'then we found it
                    myVin.EntireRow.ClearContents 'remove it
                    Target.EntireRow.Font.Color = RGB(0, 100, 0)
                    Target.EntireRow.Font.Bold = True
                End If
            End If

        Next ws


     ElseIf Target.Value = "Needs Follow Up" Then
            For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> ActiveSheet.Name Then
                Set myVin = ws.Cells.Find(what:=Target.Offset(0, -12).Value)
                If Not myVin Is Nothing Then 'then we found it
                    myVin.EntireRow.ClearContents 'remove it
                    Target.EntireRow.Font.Color = RGB(184, 134, 11)
                    Target.EntireRow.Font.Bold = True
                End If
            End If

        Next ws

     End If

End If

End Sub

norie

May 25 2012, 02:54 PM

Try Sh.Range("M:M").

ipisors

May 25 2012, 03:04 PM

Thank you. I was wondering if that was it, but when no intellisense popped up when I typed Sh., I felt unsure if it was proper. I guess it makes sense that no intellisense happens with just an Object.

however the code just seems plain angry at me at this point.

It now says, Type Mismatch

on the line:
If Target.Value = "Completed" Then

Now how can that POSSIBLE be a type mismatch ?????

ipisors

May 25 2012, 03:05 PM

And in immediate window, this:
?target.Address

produces this:
$29:$29

which I'm unfamiliar with.

ipisors

May 25 2012, 04:03 PM

I believe I got it working now. Eventually (though i had hoped to avoid this) , I changed the whole scheme to place this code in the worksheet_change events in worksheet class modules, not workbook. That, as well as some improvements in the code:

A slight change to Exit Sub if intersect is nothing
turning off application events once the code needs to start doing things

Seems to have completely fixed things. It was my fault to be leaving events on anyway. No wonder debugging was confusing me. I'd start at one Target and end up with another Target, with code firing back and forth all over the place. I am grateful for a 3 day weekend. Although by the time Tuesday comes, I will be missing my Excel work. Is that weird?

My final code was:

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim myVin As Range
If Intersect(Target, Range("m:m")) Is Nothing Then
Exit Sub
Else
     If Target.Value = "Completed" Then
        Application.EnableEvents = False
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> ActiveSheet.Name Then
                Set myVin = ws.Cells.Find(what:=Target.Offset(0, -12).Value)
                If Not myVin Is Nothing Then 'then we found it
                    myVin.EntireRow.ClearContents 'remove it
                End If
            Target.EntireRow.Font.Color = RGB(0, 100, 0)
            Target.EntireRow.Font.Bold = True
            End If

        Next ws
        Application.EnableEvents = True

     ElseIf Target.Value = "Needs Follow Up" Then
     Application.EnableEvents = False
            For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> ActiveSheet.Name Then
                Set myVin = ws.Cells.Find(what:=Target.Offset(0, -12).Value)
                If Not myVin Is Nothing Then 'then we found it
                    myVin.EntireRow.ClearContents 'remove it
                End If
            Target.EntireRow.Font.Color = RGB(184, 134, 11)
            Target.EntireRow.Font.Bold = True
            End If

        Next ws
        Application.EnableEvents = True
     End If

End If
End Sub

norie

May 25 2012, 05:01 PM

The reason you for the type mismatch error is because $29:$29 is a whole row and has multiple values (event empty cells have a value).

You can't compare multiple values to one value.

ipisors

May 26 2012, 10:44 PM

Yes, I think that makes sense and agrees with the fact that once I turned off Events at the right place in the code, things were OK. Since I was using the worksheet change event to change an entire row of something...which in turn triggered another worksheet change event which then didn't work since the target was a whole row.

That should have been obvious to me when immediate window said 29:29 for address. I guess I learned another thing about debugging!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.