+ Reply to Thread
Results 1 to 11 of 11

VBA to validate entry?

  1. #1
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    VBA to validate entry?

    Hi all,

    I have a range of cells (A1:A50). If "Hello" is written in any of those cells then a MsgBox says "Are you sure?". If vbYes the cell is colour coded blue. If vbNo then it is red.

    The problem I have is that "Hello" may already exist within the above range. I only want the above to fire on the cell that has just been changed within the range.

    I have some code but it checks every cell within the range whenever any cell is changed within the range. Whereas I just want it to fire on the active cell if that makes sense?

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: VBA to validate entry?

    Use Worksheet_Change event and check each cell in supplied Target argument:
    Please Login or Register  to view this content.
    for example.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  3. #3
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: VBA to validate entry?

    Thanks Izandol! I gave it a good try but just couldn't quite get it. I put your code within a worksheet_change event and it works great.

    if you wouldn't mind, could you explain the below as I'm learning VBA at the mo. It's going well but I've not seen the Intersect before:

    Please Login or Register  to view this content.

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: VBA to validate entry?

    This line tests if any of the changed cells are inside range A1:A50. If yes, Intersect returns the range that overlaps A1:A50. If no, it returns Nothing.

  5. #5
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: VBA to validate entry?

    Ahh I see. That's useful.

    Can I ask one quick Q - how do I embed this within this:

    x being 1 to finalrow

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: VBA to validate entry?

    rgCell is already part of loop - why do you wish to loop x also?

  7. #7
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: VBA to validate entry?

    edit - ooops almost exactly the same (I had a phone call in a meanwhile) so changed to check first letter

    It makes sense. You can use Worksheet_Change event handler. Paste in the sheet code:
    Please Login or Register  to view this content.
    Last edited by Kaper; 02-25-2014 at 09:39 AM.
    Best Regards,

    Kaper

  8. #8
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: VBA to validate entry?

    Oh blimey yes you're right.

    Essentially I don't wish to check for 'hello' but whether the first letter of the entry is 'A'

  9. #9
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: VBA to validate entry?

    So:
    Please Login or Register  to view this content.
    UCase is so test will catch "a" and "A" - if you do not want this, remove UCase part.

  10. #10
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: VBA to validate entry?

    Brilliant! Thanks a million. Bless you.

  11. #11
    Forum Contributor
    Join Date
    08-23-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    404

    Re: VBA to validate entry?

    Hi Izandol,

    Apologies - I'm not sure if I'm meant to ask as I have set the thread to solved. If it's ok, may I ask how I add another value to the below:

    i.e. "A" or "B" or "C", etc

    Please Login or Register  to view this content.

  12. #12
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: VBA to validate entry?

    You may use:
    Please Login or Register  to view this content.
    or Select Case construction:
    Please Login or Register  to view this content.

+ 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. Validate date entry in a text box.
    By Flintstone in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2008, 03:44 PM
  2. MsgBox to validate an entry
    By TimN in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-21-2006, 04:15 PM
  3. Validate InputBox entry
    By davidm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-30-2005, 10:03 PM
  4. Validate textbox entry
    By Stuart in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2005, 03:06 PM
  5. Validate Combobox entry
    By MBlake in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-04-2005, 04: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