+ Reply to Thread
Results 1 to 7 of 7

mandatory cells only if another cell is filled - excel

  1. #1
    Registered User
    Join Date
    05-20-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    3

    mandatory cells only if another cell is filled - excel

    Hi all,

    I need to make cells in my workbook (F8 - F200) mandatory only if other cells (C8-C200) is filled. I use Excel 2010 and I will need to repeat this action for a range of cells from F8 till F200 and thus also condition C8 until C200. I need a validation messsage "Must Enter Reason in Column F" for F8 till F200 if C8 - till C200 has anything entered. Can someone please help me with the correct Excel VBA coding for this issue. I am literally lost and tried several options.

    Hope it makes sense and someone can help.
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: mandatory cells only if another cell is filled - excel

    What is the sequence you want the user to go through? That is, at what point do you want your validation message to appear?

    One way to do this is to require that once C8 is filled in, the only thing allowed is to fill in F8:

    User enters data in C8 (or whatever row).
    If the user attempts to enter data anywhere except F8, it will not be allowed--they get the validation message.
    User enters data in F8
    Continue normally

    Another way is no data is allowed in C8 unless F8 is filled in first.

    F8 is blank
    User attempts to enter data in C8, not allowed--they get the validation message
    User enters data in F8
    User enters data in C8


    The second way can easily be done with data validation, with no VBA.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: mandatory cells only if another cell is filled - excel

    I did a couple of things with this sheet. First I converted the data to an Excel table. Some of the benefits of an excel table are that when you add a new line of data immediately after the end of the table, it automatically becomes part of the table. All formulas and validations and formats are copied down automatically. So you don’t have to extend these down arbitrarily.

    When you enter data into Column C, it fires off a change event. If you change the column header, you will have to change the code to match the new column header name.

    Please Login or Register  to view this content.
    There is a Parameters sheet. It contains two pieces of information: a list of reasons that you can edit and a cell that contains the working row. Do not change the name of this sheet, but you can hide it if you wish.

    Changing data in Column C launches a form. The reasons are in a drop-down list populated by the list you filled out on the parameters sheet.

    The only way out of the form is through the Edit Data button. The button checks to see if there is data in column C and won’t let you out until you make a selection that it writes to column F.

    If you delete the data in column C, the form launches, but you can exit without making a selection and the data in column F is deleted.

    There is also code for the form. If you change the name of the sheet from Blad1, you will also have to change this code.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  4. #4
    Registered User
    Join Date
    05-20-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    3

    Re: mandatory cells only if another cell is filled - excel

    Hi Jeff,

    Thanks for responding. I will try to explain my situation in more detail.

    Once the user has filled in a value in column C (for example C8) than cell F8 has to be filled in with a reason. It can not be blank. So I would like to make sure that the person who fills in C8 has to fill in F8. Also, F8 can not be deleted when C8 has a value in it. When a user tries to delete the value in F8 so that it will be blank it should get an errot message. I would like to use this principle for the range C8-200 and F8-200. Hence, C8 relates to F8, C9 - F9, C10 -F10 etc.

    Hope you can help me with the VBA coding in the workbook.

    Kind regards
    Marty1234

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: mandatory cells only if another cell is filled - excel

    I noticed that I did not address the case where the data is already filled in and the user tries to delete the reason in column F while there is data in column C. The following code fixes this situation. You will have to change "Title 2" and "Title 5" in the following if you change them in your final report.

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

  6. #6
    Registered User
    Join Date
    05-20-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    3

    Re: mandatory cells only if another cell is filled - excel

    Hi Dflak,

    Thanks for helping me out with this problem. However there is one minor issue. When I press the Delete button on Cell F8 a pop up screen tells me that I must fill n a reason because C8 has a value in it. When I select a value in the drop down menu for column F8 it does not show this value in the cell. Hence, it is blank while cell C8 has a value in it.

    Can you help me out with this minor issue?

    KR,
    Marty1234

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: mandatory cells only if another cell is filled - excel

    What happens when you blank out the cell is that you do get the pop-up message. When you click OK - the form to enter a reason should come up. You can't get out of this form unless you select a reason. You are correct, it did not show the current value and you had to reselect it. I added some code to default to the existing value.

    One thing I just noticed is that you can select a ranged of cells and delete the contents. This crashes the program.

    So I added some code to protect against this. If you select a single cell in column F, it will launch the form. It doesn't matter what you intended to do with the cell. If you select multiple cells, you get a warning message and when you cancel, the cursor is then put in the cell in column A of the top row of the selection.

+ 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. Prevent Excel workbook closing saving if the mandatory cells not filled in
    By pearlite007 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 02-07-2017, 05:12 PM
  2. [SOLVED] Need Help! Mandatory field with two cells, but one of them needs to be filled!
    By dgavranovic in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-27-2014, 05:05 PM
  3. Mandatory cells if another is filled
    By jdixon825 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2014, 10:32 PM
  4. [SOLVED] How to make it IMPOSSIBLE TO CLOSE Workbook until mandatory cells r nt filled?
    By Scofield24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2013, 05:31 AM
  5. Make several cells mandatory if a cell in a range is filled out
    By steeveho in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-18-2013, 06:49 AM
  6. Need Help | VBA (can't close before all mandatory cell are filled up)
    By trajab in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-12-2009, 05:52 AM
  7. mandatory cells only if another cell is filled
    By RA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2006, 12:45 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