+ Reply to Thread
Results 1 to 4 of 4

VBA Data Validation

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    Bath, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    VBA Data Validation

    Hi,

    I have inserted data validation into the attached example spreadsheet ensuring only one value can appear in cells in columns C/D to give the user one option of recording a pay increase in terms of or %. If the user attempts to complete both columns an error message prevents them from doing so.

    I need to add an additional validation to prevent both C/D columns being completed if the employee (identified by the unique number in column A) is present on the 'Promotions' worksheet. I think I can amend my data validation using the OR/ CountIf function, but ideally would like to include a different error message if the employee appears on the 'Promotions' sheet - is this something that can be done via VBA?

    Here is a summary of how the function should work.

    - Employee 2 does not appear on promotions sheet. They can have an increase recorded on the 'Pay' worksheet in either the C or D columns, but not both.
    - Employee 1 appears on the promotions sheet. They cannot have an increase in either the C or D columns and if the user attempts to insert one an error message appears telling them this is because they appear on the 'Promotions' worksheet.

    Thanks,

    cd254
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,373

    Re: VBA Data Validation

    First delete the data validation you have for columns C and D then copy and paste this macro into the worksheet code module. Do the following: right click the "Pay" tab name and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make an entry in the "Pay" sheet and press the RETURN key.
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    04-11-2013
    Location
    Bath, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: VBA Data Validation

    This is perfect, thanks so much for your help!

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,373

    Re: VBA Data Validation

    My pleasure.

+ 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] VBA to Prevent Data Entry in Cell with Data Validation List - Two Criteria Validation
    By AliGW in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 05-25-2019, 11:48 AM
  2. Replies: 1
    Last Post: 01-14-2018, 11:04 AM
  3. Multi-select from data validation isn't working with auto-assigning data validation
    By iPenguin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2017, 12:37 PM
  4. Replies: 3
    Last Post: 06-04-2015, 02:27 PM
  5. [SOLVED] Data Validation: How to clear/delete the content of the cell and not Data Validation List?
    By lukelucky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2015, 09:42 AM
  6. Replies: 4
    Last Post: 12-19-2013, 10:44 AM
  7. Using Defined Names with Data Validation Depend and Data Validation Multi Select
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-01-2012, 05:36 PM

Tags for this Thread

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