VBA If Not Intersect not working for larger values on worksheet_change

Question

I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim btn As Button
  Dim t As Range
  Dim i As Integer
  i = Target.Row
  If Not Intersect(Target, Range("$B10:$B103")) Then
    If Target.Value <> "" Then
      For Each btn In ActiveSheet.Buttons
        If btn.Name = "I" & i Then
          btn.Delete
        End If
      Next btn
      Set t = ActiveSheet.Range(Cells(i, 9), Cells(i, 9))
      Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
      With btn
        .OnAction = "imageshow"
        .Caption = "View Images"
        .Name = "I" & i
      End With
    Else
      For Each btn In ActiveSheet.Buttons
        If btn.Name = "I" & i Then
          btn.Delete
        End If
      Next btn
    End If
  End If
End Sub

When I run it, it works if the value entered into B10:B103 is an Integer number, but if I use text or a Long number a combination of text and numbers (data entered in here will be of this form) then it will not work.

I took the liberty to indent the code, for readabiliy purposes. I hope you don't mind. For me a correctly indented code makes *a huge* difference. — d-stroyer, Aug 23 '13 at 11:39
My first guess is that the line is incorrect. Intersect method returns a Range Object, and you have used it as boolean. You should use like this: Set rng = Intersect(your Range, new Range) and then compare the rng Object if your If Statement. — Vikas, Aug 23 '13 at 11:40
Yes thats not a problem. Anyway, it seems that my Intersect function is actually rubbish too as it throws up an error when trying to edit cells outside of this range too. EDIT: @vikas you read my mind ;) — bmgh1985, Aug 23 '13 at 11:41
So now I have seen my error (thanks to @vikas for pointing me towards it). Just changed `If Not Intersect(Target, Range("$B10:$B103")) Then` to `If Not Intersect(Target, Range("$B10:$B103")) is Nothing Then` and works fine now. Can't believe I missed that. Guess it was working better in my head ;) — bmgh1985, Aug 23 '13 at 11:55

score 1 · Answer 1 · answered Aug 23 '13 at 12:03

1

Change two lines:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim btn As Button
  Dim t As Range
  Dim i As Long
  i = Target.Row
  If Not Intersect(Target, Range("$B10:$B103")) Is Nothing Then
    If Target.Value <> "" Then
      For Each btn In ActiveSheet.Buttons
        If btn.Name = "I" & i Then
          btn.Delete
        End If
      Next btn
      Set t = ActiveSheet.Range(Cells(i, 9), Cells(i, 9))
      Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
      With btn
        .OnAction = "imageshow"
        .Caption = "View Images"
        .Name = "I" & i
      End With
    Else
      For Each btn In ActiveSheet.Buttons
        If btn.Name = "I" & i Then
          btn.Delete
        End If
      Next btn
    End If
  End If
End Sub

answered Aug 23 '13 at 12:03

Gary's Student

95,722
10
59
99

Thanks Gary. Don't think the other change is needed in this case as the "i" will only be going up to 103 so it doesnt need to be set as Long. – bmgh1985 Aug 23 '13 at 12:51
Try to use `Long` to provide for unexpected overflows (frequently the case in row counters exceeding the limit of 32767); *internally* your 16-bit integer (silently) gets converted to a long 32-bit integer anyway - see [Overflow error in VBA involving integers and long integer](https://stackoverflow.com/questions/47024114/overflow-error-in-vba-involving-two-integers-and-a-long-integer/47024456#47024456). Further hint: feel free to accept an answer by ticking the green checkmark if you consider it helpful - see ["Someone answers"](https://stackoverflow.com/help/someone-answers) - @bmgh1985 – T.M. Sep 17 '21 at 09:23

VBA If Not Intersect not working for larger values on worksheet_change

1 Answers1