Workbook_SheetChange and If Not Intersect error

[greglittle]

Hi. I keep getting run-time error 1004 (Method 'Intersect' of object '_Global' failed) when I enter a value in a cell other than the intersect range. The code works just fine when the intersect range is changed, but trips up when I change a cell in some sheets (but not all sheets). It's very strange. Is there something I'm missing here? The goal is to update formatting of a point on a chart depending on which country is selected.

I can't use the Worksheet_Change event because the country can be changed from the UI, so I need the chart to update even when the user is not on the chart's worksheet.

Thanks for your help!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Not Intersect(Range("Country"), Target) Is Nothing Then 'Updates the curve chart on the risk sheet with the correct countries
        Dim Abrv As String
        'Dim RefCtry As Variant
        'Dim US As Variant
        'Dim ProjCtry As Variant
        Abrv = Application.WorksheetFunction.VLookup(Range("Country"), Range("CountryTable"), 2, False)
               
        Set US = Sheet1.ChartObjects("CtryChart").Chart.SeriesCollection(3)
        Set RefCtry = Sheet1.ChartObjects("CtryChart").Chart.SeriesCollection(2)
        Set ProCtry = Sheet1.ChartObjects("CtryChart").Chart.SeriesCollection(4)
        
        Select Case Range("Country").Value
            Case "United States"
                US.Points(1).DataLabel.Font.ColorIndex = 3 'Make US Red
                RefCtry.Points(1).DataLabel.Font.ColorIndex = 1 'Make Ref Countries Black
                ProCtry.Points(1).HasDataLabel = False 'Remove ProCountry label
            
            Case "Brazil"
                US.Points(1).DataLabel.Font.ColorIndex = 1 'Make US Black
                RefCtry.Points(1).DataLabel.Font.ColorIndex = 1 'Make Ref Countries Black
                ProCtry.Points(1).HasDataLabel = False 'Remove ProCountry label
                
                RefCtry.Points(1).DataLabel.Font.ColorIndex = 3 'Make Brazil Red

            Case Else

         End Select
    End If

End Sub

[JosephP]

which sheet is the country range on? you need to test if Sh is that sheet before you try and use intersect because you can't use intersect with ranges on different sheets

[JosephP]

perhaps

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Dim rCountry               As Range
   Dim Abrv                   As String

   On Error Resume Next
   Set rCountry = Sh.Range("Country")
   On Error GoTo 0

   If Not rCountry Is Nothing Then
      If Not Intersect(rCountry, Target) Is Nothing Then   'Updates the curve chart on the risk sheet with the correct countries
         'Dim RefCtry As Variant
         'Dim US As Variant
         'Dim ProjCtry As Variant
         Abrv = Application.WorksheetFunction.VLookup(rCountry.Value, Range("CountryTable"), 2, False)

         With Sheet1.ChartObjects("CtryChart").Chart
            Set US = .SeriesCollection(3)
            Set RefCtry = .SeriesCollection(2)
            Set ProCtry = .SeriesCollection(4)
         End With

         Select Case rCountry.Value
            Case "United States"
               US.Points(1).DataLabel.Font.ColorIndex = 3   'Make US Red
               RefCtry.Points(1).DataLabel.Font.ColorIndex = 1   'Make Ref Countries Black
               ProCtry.Points(1).HasDataLabel = False   'Remove ProCountry label

            Case "Brazil"
               US.Points(1).DataLabel.Font.ColorIndex = 1   'Make US Black
'               RefCtry.Points(1).DataLabel.Font.ColorIndex = 1   'Make Ref Countries Black
               ProCtry.Points(1).HasDataLabel = False   'Remove ProCountry label
               RefCtry.Points(1).DataLabel.Font.ColorIndex = 3   'Make Brazil Red

            Case Else

         End Select
      End If
   End If

End Sub

[greglittle]

Joseph,

That worked, thank you. Now though, every time I change any cell on any sheet Excel will pause for a couple seconds of calculation time. I don't know why I didn't think of this before, but since the user is inputting country on the UI, I can just put all of this code in a sub that runs when the user changes the country. (doh!)

P.S. I used to live just south of Sandy back in the day... loved riding Brighton and Canyons. I hope you guys get some good snow this winter! Take care.

[JosephP]

thanks - that'd be nice :-)