+ Reply to Thread
Results 1 to 20 of 20

How to identify a source of event ?

  1. #1
    Registered User
    Join Date
    09-18-2017
    Location
    Czech Republic
    MS-Off Ver
    Office 2007,13,16
    Posts
    18

    How to identify a source of event ?

    Hello,

    I put "Data Validation" element on my Sheet - Data validation based on LIST i.e. list of A1:A20 cells.
    And accodring to selected item I would like to change the color of cell in ValidationListComboBox. I am using
    Sub Worksheet_Change(ByVal Target As Range) to catch the event ValidationListChange. However, there
    is a small problem. Worksheet_Change catching me ALL events e.g. column insertion, column deletion,
    text insertion etc. How can I recognize, that Worksheet_Change cought just right the "Validation List Changed"
    event ??? Is it possible to obtain this info from "ByVal Target As Range" ??? How can I Identify source of Event ?

    Many thanks.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to identify a source of event ?

    Hi,

    You should check if the Target intersects the cell you are interested in- for instance
    Please Login or Register  to view this content.
    where B1 is the cell with the data validation applied.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    09-18-2017
    Location
    Czech Republic
    MS-Off Ver
    Office 2007,13,16
    Posts
    18

    Re: How to identify a source of event ?

    it is NOT possible for me, my DataValidationFromList can change its position
    I need to identify the source of event
    Is it possible ?

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to identify a source of event ?

    Not directly. The Change event is triggered by cells being altered (excluding formula calculations). If it is only one cell that has the data validation, you should name it and use the name in code. If it could be any validation cell, you can test each cell in the Target range to see if it uses a validation list.

  5. #5
    Registered User
    Join Date
    09-18-2017
    Location
    Czech Republic
    MS-Off Ver
    Office 2007,13,16
    Posts
    18

    Re: How to identify a source of event ?

    ok, and how I can obtain the NAME of named cell from parameter of Worksheet_Change prosedure ?
    e.g. Range.Parent.Name ? Where I can find name of a named cell ??

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to identify a source of event ?

    Target.Name.Name would give the name of the selected range, assuming it has only one name.

  7. #7
    Registered User
    Join Date
    09-18-2017
    Location
    Czech Republic
    MS-Off Ver
    Office 2007,13,16
    Posts
    18

    Re: How to identify a source of event ?

    thanks, Target.Name.Name seems to be the right solution
    and .. where can I find manual for this dot notation ... Excel VBA does NOT give me contextual help at second "." dot is written ...

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to identify a source of event ?

    Range.Name returns a variant, which is why Intellisense can't help you after that. You should use the object browser to locate the objects and properties, methods, and events available.

  9. #9
    Registered User
    Join Date
    09-18-2017
    Location
    Czech Republic
    MS-Off Ver
    Office 2007,13,16
    Posts
    18

    Re: How to identify a source of event ?

    ok, and now, how can I ask for the name of named cell. This does NOT work:


    If (Target.Name.Name = "MyNamedCell") Then ' ERROR in here

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to identify a source of event ?

    If the Target isn't named, you will get an error, so you need to handle that. For example
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-18-2017
    Location
    Czech Republic
    MS-Off Ver
    Office 2007,13,16
    Posts
    18

    Re: How to identify a source of event ?

    ok, it is nice solution,

    but why I can NOT use this:

    If (Target.Name Is Nothing) Then ??? this does not work

    this also does not work

    Debug.Print "count: " & Target.Name.Count

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to identify a source of event ?

    Because the Name property causes an error if there isn't a Name, it does not return Nothing.

  13. #13
    Registered User
    Join Date
    09-18-2017
    Location
    Czech Republic
    MS-Off Ver
    Office 2007,13,16
    Posts
    18

    Re: How to identify a source of event ?

    ok, thanks

    and ... is it possible to have more cells with identical name (named cells) ?
    or every cell must have an uniqe name ?
    Last edited by JerryMouse; 09-20-2017 at 08:38 AM.

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to identify a source of event ?

    One named range can be multiple cells, but that would defeat the purpose of this code. If you can set up a named range for all the validation cells, you can go back to using Intersect.

  15. #15
    Registered User
    Join Date
    09-18-2017
    Location
    Czech Republic
    MS-Off Ver
    Office 2007,13,16
    Posts
    18

    Re: How to identify a source of event ?

    ok, my problem is that I have a sheet with several columns and in every cell of these columns is ValidationList Combobox.
    And I need to identify in VBA if an event (item in validation list changed) originate from ValidationList.
    The event is caught in Worksheet_Change procedure. And only input info is in Target variable.
    So, how can I create such "if" condition...

  16. #16
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to identify a source of event ?

    You need only change the range in post #2 to cover all the relevant columns- such as
    Please Login or Register  to view this content.

  17. #17
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: How to identify a source of event ?

    Another way :
    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  18. #18
    Registered User
    Join Date
    09-18-2017
    Location
    Czech Republic
    MS-Off Ver
    Office 2007,13,16
    Posts
    18

    Re: How to identify a source of event ?

    ok jerry this is solution of your problem:

    Target.Validation.Formula1

    indeed, this identify the source range assigned to your DataValidation ComboBox because this returns the area of cells
    assigned to validation list.

    and compare if your area is identical to area actually clicked by user is

    range_A = Range(Mid(Target.Validation.Formula1, 2)).address(RowAbsolute:=True, ColumnAbsolute:=True)
    range_B = Range("EXCEL_DATA!" & startCell_cmbb & ":" & stopCell_cmbb).address(RowAbsolute:=True, ColumnAbsolute:=True)

    If (range_A <> range_B) Then

    Exit Sub

    End If

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: How to identify a source of event ?

    Maybe you could consider adding a helper column which does a calculation based upon the validation list value and a value in that same row?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  20. #20
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: How to identify a source of event ?

    Ok Jerry, if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Trying to return userform after changing source - triggers TextBox Change event
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2017, 10:46 AM
  2. column comparison, trying to identify identify identical columns.
    By Jowel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-24-2014, 04:12 AM
  3. Replies: 4
    Last Post: 11-07-2012, 04:02 PM
  4. Identify Source of Manual Move Operation
    By miguel3d in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2011, 03:26 PM
  5. Identify possible source of calculation error from list of values
    By Absa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2009, 08:46 AM
  6. Cell Contents to identify source Filename
    By ThalesNate in forum Excel General
    Replies: 1
    Last Post: 06-07-2006, 02:35 PM
  7. How to identify source or location of MAX_VALUE in range
    By Phil in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-07-2005, 08:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1