Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel, Validation list, change Event (Win XP, Office2003, Excel)

    Hi,

    I've got a validation list populated with company names from a range of cells (eg. IV1:IV320)

    I would like to catch the change event on this list, when a user selects another company.

    The intention is to fill numerous other cells with related info. As soon as I have the company name I know what the other info is.

    Q: How do I catch the change event for a validation list?


    Big thanks
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel, Validation list, change Event (Win XP, Office2003, Excel)

    Try the Worksheet_Change event. You can inspect the Target range to see if the cell is in it. For example:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
    Application.EnableEvents = False
    ' your code goes here
    ...
    Application.EnableEvents = True
    End If
    End Sub

    Remember, this code MUST be in the worksheet module (right-click the sheet tab and select View Code from the popup menu), not in a standard module.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel, Validation list, change Event (Win XP, Office2003, Excel)

    I'm not 100% clear on what you mean by: "You can inspect the Target range to see if the cell is in it"

    What range do I send in as Target? (How is this done "on change")

    Think I way back saw a method mentioned, where a piece of code were added to a class module and then this monitored the changes made.
    - If I recall it correctly, it was even you who provided it.../ ;o)
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel, Validation list, change Event (Win XP, Office2003, Excel)

    Please disregard my question, just found your old answer.

    Was an identical question and answer.

    Appreciate the efforts.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel, Validation list, change Event (Win XP, Office2003, Excel)

    You don't need a custom class module here. The worksheet module IS a class module.

    The Worksheet_Change event occurs each time the value of a cell is changed by the user. The Target argument of the event procedure is filled by Excel with a Range object that represents the cells that have been changed. So if the user types a value in cell B3, the Target will be Range("B3"). If the user fills down from B3 to B10, the Target will be Range("B3:B10").

    If you want to monitor changes in a particular cell or range of cells, you use the Worksheet_Change event procedure and check whether the Target (i.e. the cells changed by the user) intersects (i.e. overlaps) the cell(s) you're interested in. The code example I posted checks Intersect(Target, Range("A2")) to see if cell A2 has changed (A2 is just an example).

    If you search the Excel forum and this forum for Worksheet_Change, you'll find numerous other examples.

Posting Permissions

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