0

I am relatively new to VBA.

I have a table Range(E16:DW39), where each Cell has a dropdown menu (Data Validation = List) to pick a value manually BUT at the same time I have a Macro to copy data from another worksheet (two ways to populate the table).

I want to get a Message every time the user changes any cell value Manually.

I want to be able to still run the Macro (as I noticed that I cannot after adding the following code) independently. This Intersect check I want it only for the manual data input.

In order to do this check I have the following code (in the specific Sheet):

Private Sub Worksheet_Change(ByVal Target As Range)
------------------------------------------------------
Dim myRange As Range

myRange = Range("E16:DW39") '-> Mission Mix Table

If Intersect(myRange, Target) Then

MsgBox "Mission Plan is not matching with this change"

End If

End Sub
------------------------------------------------

I get the following ERROR

Run-Time Error 91: Object variable or With block variable not set

When I tried to change a cell value to another one from the dropdown menu, I get the above ERROR, any idea how to solve this problem?

Please notice that due to this code I cannot run my previously running fine Copy macro.

Mihai Matei
  • 24,166
  • 5
  • 32
  • 50
Yanis
  • 1
  • 2

1 Answers1

0

The error code is because you are referencing an null object when the two ranges dont intersect. Fix:

If Intersect(myRange, Target) Is Nothing then...

If you want to process changes that happen only from Excel GUI and not from code, The usual approach is to use Application.EnableEvents = False at the beginning of your macros, then Application.EnableEvents = true at the end of the macro. This way, any change made inside the macro will not toggle event handling.

Private Sub Worksheet_Change(ByVal Target As Range)
        On Error goto finish
        Dim myRange As Range

        Set myRange = Range("E16:DW39") ' <~~ dont forget "Set"
        If Not Intersect(myRange, Target) Is Nothing Then
            MsgBox ...
        End If
    finish:
        Application.EnableEvents = True
End Sub
A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • A.S.H thanks for your reply, but even when adding the Nothing did not change anything, same error again. I further changed the code to your suggestion and I can now run my previous macro, but still I dont get the Pop Up Warning/Message I want when I change the cell value. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo finish Dim myRange As Range myRange = Range("E16:DW39") '-> Mission Mix Table If Intersect(myRange, Target) Is Nothing Then Application.EnableEvents = False MsgBox "Mission Plan is not maching" End If finish: Application.EnableEvents = True End Sub – Yanis Sep 30 '15 at 08:35
  • @Yanis I see, it is because you should use Set for objects : `Set myRange = Range("E16:DW39")` (edited the answer) – A.S.H Sep 30 '15 at 08:38
  • @Yanis, you want to call you subroutine if the target and the range do overlap, and the message box if not, did I understand correctly? – A.S.H Sep 30 '15 at 08:42
  • I don’t want to call any subroutine. The subroutine is executed at a click of a button, but it was prevented before your suggestion. – Yanis Sep 30 '15 at 08:46
  • Still the Pop Up Message does not work, is this related to the fact that the Cell is using a LIST of values, therefore cannot identify that the displayed value has changed? Only guessing here. – Yanis Sep 30 '15 at 08:49
  • No, the event should be triggered even if the cell is picked through a list. But the doubt i have is, if you change the cells inside your range, the msgbox will not popup because the If condition is not met. try inverting the condition? (if Not...) – A.S.H Sep 30 '15 at 08:51
  • A.S.H Apologies, I missed the If NOT, it works as I wanted to BUT when I run my independent Macro (the second way to copy data) I get the Message at every change which is not good as you can imagine, any ideas? Thanks for your time. – Yanis Sep 30 '15 at 08:56
  • This is supposed to be the role of `Appllication.EnableEvents=False`. Are you sure you are setting this statement when entering the routine? I mean, at the entry of the routine, add `Application.EnableEvents=False`, and at the exit, add `Application.EnableEvents=true`. – A.S.H Sep 30 '15 at 08:58
  • A.S.H, found the solution (as you have already said so in your first comment). Thanks again for your time. – Yanis Sep 30 '15 at 09:01
  • A.S.H, is there any way to store the initial Cell value, and only if the value is different to that, only then show the message, e.g. lets say that the initial cell value if "F" and I change to "D", then I get the Message, but if I "change" to "F" then I shouldn't. Just noticed this detail, that even if I click on the same value from the dropdown menu, I still get the Message. I know not big issue, but nice to have. Thanks again for your wisdom sharing. – Yanis Sep 30 '15 at 09:13
  • That is a little bit more complicated. The answer is here:http://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba – A.S.H Sep 30 '15 at 09:20
  • Thanks, I will have a look. – Yanis Sep 30 '15 at 09:27