VBA Data Validation depending on Target Value
Results 1 to 6 of 6

Thread: VBA Data Validation depending on Target Value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Skybluekid's Avatar
    Join Date
    Apr 2012
    Location
    Coventry England
    Posts
    823
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Data Validation depending on Target Value

    Hi All.

    I have Data Sheet which logs Penalty Charge Notices. There is a column which has all Authorities, including Police. In another column i have a list of Fine Types, for both police and councils. What i would like to happen is if i choose a council then no police fine would show in a drop list and visa versa.

    I have tried various vba code but it always generates an error and the corrupts the spreadsheet the next time it is opened.

    Any ideas would be useful.
    Always awake not always on the Ball

    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Data Validation depending on Target Value

    A bit more information?
    What are the (relevant) columns of the database?
    How do I recognise that you have chosen a council? (or police)
    How do i differentiate between council fines and police fines (in order to filter)?
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  3. #3
    Board Regular Skybluekid's Avatar
    Join Date
    Apr 2012
    Location
    Coventry England
    Posts
    823
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Data Validation depending on Target Value

    Thank you for the reply. On the main data sheet, in column B, I have a drop list of all authorities, inc police forces. In column F, i have a drop list of all the fines for both Council and police. This comes from a table on the Extra worsheet. In the table there is a "P" against all the fines that relate to police.

    Hope this helps
    Always awake not always on the Ball

    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

  4. #4
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Data Validation depending on Target Value

    A few more questions:

    You talk about a datasheet (called 'Data Sheet'?). This is where you log the administered fines.

    The columns with authorities and fines, are they on the same sheet (bad practice) or on different sheets?

    Your dropdown lists, are they on the sheet (which?) or in a pop-up userform? What are the names of these dropdown lists? Are they ActiveX lists or Form lists?
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  5. #5
    Board Regular Skybluekid's Avatar
    Join Date
    Apr 2012
    Location
    Coventry England
    Posts
    823
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Data Validation depending on Target Value

    Yes, the data sheet is where the fines are logged. The fines and authrorites are on a sheet called Extra. The Dropdown lists are Data Validation lists on the Data Sheet.

    Hope this helps
    Always awake not always on the Ball

    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

  6. #6
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Data Validation depending on Target Value

    In the sheet Extra, is it not possible to have two additional ranges "Council" and "Police" where each holds a filtered list of the fines.
    Then in the Data sheet, you add a (hidden) column that checks if the authority selected is Police or Council.

    Then you can set the formula for the validatiion of the fines to point to the correct list using =INDIRECT().
    See for instance https://trumpexcel.com/excel-drop-down-list/
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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