438 Run Time error

Question

I am trying to make it so that when one of the specific cells in my code is changed then it will display a message but i am getting the following error message "run time error 438 object doesnt suport this property or method". Not realy sure what this means. could someone please help me understand. Here is the code:

 Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Adress = "F48,I48,L48,F50,I50,L50,I52,L52,N52" Then
     MsgBox "You are about to change an AP-42 Emision Factor"

 End If
 End Sub

Stupid error I made! Still seems like it wont display message when the cells change... any ideas — David Van der Vieren, Apr 04 '13 at 21:15

Kazimierz Jawor · Answer 1 · 2013-04-04T21:39:22.353

2

1st, as mentioned in comment, use Target.Address which is correct property name.

2nd, your if statement will never return true. Target.Address will always return something like this: $E$2, $E$3:$E$4, and so on... In your situation you should use something like Intersect or Union methods.

Edited- possible solution using Union method:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngTMP As Range
    Set rngTMP = Range("F48,I48,L48,F50,I50,L50,I52,L52,N52")

If Union(Target, rngTMP).Address = Union(rngTMP, rngTMP).Address Then
        MsgBox "Ok"
End If
End Sub

edited Apr 04 '13 at 21:39

answered Apr 04 '13 at 21:17

Kazimierz Jawor

18,861
7
35
55

What is an Intersect of Union Method – David Van der Vieren Apr 04 '13 at 21:20
by the way, are you sure you will change all 9 cells at once which is difficult to imagine if that area is not continuous. – Kazimierz Jawor Apr 04 '13 at 21:29
+ 1 for suggesting `Intersect` – Siddharth Rout Apr 04 '13 at 22:13

score 1 · Accepted Answer · edited May 23 '17 at 12:13

1

Is this what you are trying?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F48,I48,L48,F50,I50,L50,I52,L52,N52")) Is Nothing Then
        MsgBox "You are about to change an AP-42 Emision Factor"
    End If
End Sub

Worth Reading: MS Excel crashes when vba code runs

edited May 23 '17 at 12:13

Community

1
1

answered Apr 04 '13 at 22:12

Siddharth Rout

147,039
17
206
250

I was thinking of using `Intersect` but could not find quick solution in which `Intersect` would check if both `Target` and other range match exactly. In your code one range must be within another but `Target` could be bigger to return true. Do you have any simple idea how to use `intersect` for exact matching? If so, please add as additional option for +1 :) – Kazimierz Jawor Apr 04 '13 at 22:22
I believe and I could be wrong but what David is trying to do (and he has to confirm that) even if one of the cells in the range `"F48,I48,L48,F50,I50,L50,I52,L52,N52"` changes, the message box should pop up which the above code will take care of. – Siddharth Rout Apr 04 '13 at 22:24
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/27595/discussion-between-siddharth-rout-and-kazjaw) – Siddharth Rout Apr 04 '13 at 22:46
That is correct that is what I am trying to do. I am having a problem with the code you made however because it is not running automaticaly. Is this code ment to be a module or under the specific sheet the change would take place? – David Van der Vieren Apr 05 '13 at 14:33

score 1 · Answer 3 · answered Apr 05 '13 at 14:47

The Code Siddharth Gave works wonders so Thanks so much. I was running into trouple because I was trying to make two Worksheet_Change events by writing the code the following way:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$8" Then
    Toggle_Rows
End If
End Sub

Private Sub Worksheet_Change(Byval Target As Range)
    If Not Intersect(Target, Range("F48,I48,L48,F50,I50,L50,I52,L52,N52")) Is Nothing Then
        MsgBox "You are about to change an AP-42 Emision Factor"
    End If
End Sub

As you can imagine this did not work, gave me an Ambiguous name error. So after some research the following is the way two write these two functions as one:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$8" Then
    Toggle_Rows
End If

    If Not Intersect(Target, Range("F48,I48,L48,F50,I50,L50,I52,L52,N52")) Is Nothing Then
        MsgBox "You are about to change an AP-42 Emision Factor"
    End If
End Sub

Thanks for all the help everyone!

438 Run Time error

3 Answers3