+ Reply to Thread
Results 1 to 10 of 10

Macro that will test values and require input if outside given ranges

  1. #1
    Registered User
    Join Date
    03-06-2013
    Location
    London, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Macro that will test values and require input if outside given ranges

    I am working on a project in which I need a series of values in the "B" column to be tested to see if each value entered is within the given range specified for that cell. If it is not then I would like the program to require some explanation to be entered in the adjacent "C" column.

    Here is a Macro that does exactly what I want it to for "B16". I however cannot seem to get it to work for other values in the "B" column.


    Please Login or Register  to view this content.
    please HELP!!
    Last edited by NovoRapid; 05-15-2013 at 02:03 PM.

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

    Re: Macro that will test values and require input if outside given ranges

    Change this line:
    Please Login or Register  to view this content.
    to this line:
    Please Login or Register  to view this content.

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Macro that will test values and require input if outside given ranges

    Try:

    Please Login or Register  to view this content.
    Gary's Student

  4. #4
    Registered User
    Join Date
    03-06-2013
    Location
    London, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Macro that will test values and require input if outside given ranges

    This is Progress!

    More specifically then what I need is for some B values to accept anything, and others to be tested against paramaters sepcific for that cell. For example I also want an imput to be required if the number entered in B14 is over 4 and and/or if B18 is over 6,000.

  5. #5
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Macro that will test values and require input if outside given ranges

    In which columns do you want the upper and lower limits stored?

  6. #6
    Registered User
    Join Date
    03-06-2013
    Location
    London, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Macro that will test values and require input if outside given ranges

    Ideally these would be stored on Sheet2 in the Book. Otherwise anywhere in Column K or in the Macro is fine.

  7. #7
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Macro that will test values and require input if outside given ranges

    Here is a sample workbook in which the limits are stored in column N & M (any columns can be substituted).
    If the limits are blank, no limits are applied.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-06-2013
    Location
    London, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Macro that will test values and require input if outside given ranges

    This is exactly the kind of thing I am looking for. The only problem is that even when I input an entry that is within the range, the program is still requiring an input. Is there a way not require an input if an entry is within the given range? I only need an input if it is outside the specified range.

  9. #9
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Macro that will test values and require input if outside given ranges

    My error
    I switched the limits.
    Here is the corrected version
    Sorry..................
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-06-2013
    Location
    London, Canada
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Macro that will test values and require input if outside given ranges

    Great! Thank you for your help, this is now working very well.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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