Force entry in multiple cells

[ng3000]

I have a spreadsheet in which has many different cells that need to be completed if another cell has data.

Examples

If the user inputs a name into A3, they must complete cells B3 thru D3. Cells B3:D3 contain different entry methods including a drop down list.

On the same sheet, if the user chooses GTD from a drop down list in cell U3, cells BA:BJ must be completed. And if they choose GCI from the drop down list in U3, they must complete cells BK:BR

Thanks for the help

 

[Smitty]

Welcome to the Board!

You can't really force users to enter data, but you can validate their entries before they can proceed to a next step. That requires VBA though.

Here's an example of checking a range when a button is pressed:
<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">If</SPAN> LenB(Range("A1")) = 0 <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Oops, you forgot to fill in A1!", vbCritical + vbOKOnly, "You missed something"<br>        Range("A1").Activate<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

 

[ng3000]

That code requires them to complete A1.

I need a code that if there is any data entered into A3, they must complete cells B3:D3

If they don't have any data for that cell, they will not be required to complete the other cells.

Also I need this to be applicable to the entire A column, not just a single cell.

Thank you for the help.

 

[Smitty]

That code requires them to complete A1.

Right. As I said, it's just an example.

There may be several options to do what you want. The first thing that comes to mind would be to use a change event targeted to column A. If an entry is made, then jump to column B in the same row. However, you're looking at a lot of conditional validating to do what you want.

Have you thought of a user form instead? That's generally a better approach for rigid data entry needs. Here's a good tutorial on them: http://www.contextures.com/xlUserForm01.html

 

[ng3000]

I've considered the user form idea, but the sheet I'm creating will be used in instances where large amounts of data will be inputted. Many of the columns are repetitive and I didn't want the user to have to enter the same data over and over. But I do want the form to have conditions where, if the user selects something from a drop down list, they are required to complete certain columns, but not others.

Could you give me a basic vba script where if the user puts something in A3, then columns B3:D3 need completed?

 

[Smitty]

Here's an example of jumping to column B if an entry is made in column A:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A:A")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN><br>            <SPAN style="color:#007F00">'   Do your thing here</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> LenB(Target.Value) <> 0 <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox "You need to enter data in cells B" & Target.Row & "through D" & Target.Row, vbInformation + vbOKOnly, "Data Entry Required"<br>                Target.Offset(, 1).Select<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I would imagine you'll need to add additional tests to see if B:D actually get filled in or not. Or you could use Input Boxes at that point, which would probably be the easiest way to ensure entry, because you can check if they're left empty.

 

[ng3000]

This is definitely a step in the right direction. But since I'm new to vba, I'm still a little confused with certain things.

1. The code currently shows an error, before the user has had a chance to fill out the columns. Can you give me a code that will check for these errors when a user attempts to save or close the sheet?

Thanks

 

[Smitty]

The code goes in the worksheet specific module, but it shouldn't throw any errors when trying to save or deactivate the sheet.

What's the error message you get?

 

[ng3000]

It just the message that is in the code, stating that b3 thru d3 need completed.

The user enters a name in A3 then immediately gets that message before getting a chance to enter anything into B3:D3. That's why I think it would be better to run the vba script after the person enters in their data.

 

[Smitty]

You can swap out the bottom portion of the previous code with this:

<font face=Tahoma>            <SPAN style="color:#00007F">If</SPAN> LenB(Target.Value) <> 0 <SPAN style="color:#00007F">Then</SPAN><br>                Target.Offset(, 1) = InputBox("Please enter data for cell B" & Target.Row, "Data Required")<br>                Target.Offset(, 2) = InputBox("Please enter data for cell C" & Target.Row, "Data Required")<br>                Target.Offset(, 3) = InputBox("Please enter data for cell D" & Target.Row, "Data Required")<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

It will prompt the users for their entries in columns B:D.

Note that I didn't add any error handling for empty contents or if the user hits cancel.