I am confused on purpose of using “Not”, also on matching of “If” criteria, code says “is nothing” else run code for “MsgBox”

by

Explanations are in the macro

'Change-event procedure for a specific sheet
'(The code is placed in the code module of this sheet)
'Always called when something changes in the sheet that triggers the change event
Private Sub Worksheet_Change(ByVal Target As Range)
  'We need the double negation in the following line because the answer of the
  'core question "Application.Intersect(Range("A1"), Range(Target.Address))"
  'can have two different data types. An Object or the special type "Nothing"
  'if there is no object. There is no other way to ask for **no** address.
  'To ask only for an address will throw a runtime error if there is no one
  'But if it's "Not" "Nothing", then there is an address.
  'This is a bit hard to understand, but programming is like this sometimes ;-)
  If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
    'Message field with the text "Working" is displayed
    'when the If statement is evaluated as "true"
    MsgBox "Working"
  End If
End Sub

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. Excel Number Separation [closed]
  3. VBA Stuck after certain loop
  4. Cutting letters from alphanumeric cell entry, pasting to another cell
  5. Why MS Excel crashes and closes during Worksheet_Change Sub procedure?
  6. Detect whether Excel workbook is already open [closed]
  7. How to add a custom Ribbon tab using VBA?
  8. Delete row based on partial text
  9. Way to run Excel macros from command line or batch file?
  10. Transpose multiple columns to multiple rows with VBA
  11. ExecuteExcel4Macro to get value from closed workbook
  12. How to put a tooltip on a user-defined function
  13. If WorkSheet(“wsName”) Exists [duplicate]
  14. How to merge rows in a column into one cell in excel?
  15. How to copy sheets to another workbook using vba?
  16. Autofilter with column formatted as date
  17. hiding formulas in formula bar
  18. Excel VBA: getting row of clicked button [duplicate]
  19. VBA Many buttons point to the same _Click sub
  20. Excel VBA Filter Change event handler
  21. How to copy only values in excel vba from a range?
  22. How to suppress Update Links warning?
  23. How do we use restful APIs from Excel macros (vba)?
  24. Cannot VBA write data to cells in Excel 2007/2010 within a function
  25. How to send email to multiple recipients with addresses stored in Excel?
  26. Writing a VLOOKUP function in vba
  27. Handle cancellation of InputBox to select range
  28. VBA: How to combine two ranges on different sheets into one, to loop through
  29. How to highlight selected text within excel
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment