Make multiple cell input mandatory if initial cell has input.

[dwporter]

Have a spreadsheet that I require a user to fill out nightly. Basically, they keep forgetting to fill in necessary information. I want to force them to put information in Cells F8, G8 if they put their initials in cell E8.

Also, if they put a value of No in cell H8, I want for them to complete cells D18, E18 and
F18. If they don't complete all the cells, I don't want the Spreadsheet to save and\or close.

If anyone is a vb wizard I'd appreciate your help, I also need to know how to replicate the code for sequence's of other cells as well.

Thanks

[mrice]

Try pasting this into the ThisWorkbook tab in the VB editor (Alt F11)

Please Login or Register  to view this content.

[mrice]

Try

Please Login or Register  to view this content.

My mistake

[dwporter]

Still need some help. Code needs to work as follows:

If cell E8 is blank, the f8, g8, ahd h8 can also be blank but if e8 has data entered in the cell then f8, g8, and h8 has to have mandatory data. Also if H8 value is "no" then cells
d18, e18, and f18 must have mandatory input as well.

So ... if e8 has data, f8 is mandatory input , g8 in mandatory input, and h8 is mandatory input, but if h8 input is "yes" then save\close worksheet, otherwise if h8 is "no" then cells d18, e18 and f18 must have mandatory input, then save\close spreadsheet

[mrice]

Try

Please Login or Register  to view this content.

If the code fails to run when save is attempted, you may need to reset the EnableEvents to True.

Do this by opening the immediate window in the VBA editor (Alt G) and typing in

Please Login or Register  to view this content.

followed by a return.

To get the same behaviour on close, but the same code into the BeforeClose event.

Please Login or Register  to view this content.

If you still are experiencing problems, it would be useful to post the actual workbook as there may be something in there which is not apparent from a distance.

[dwporter]

I'm still unable to achieve any type results. I can save or close the document after completing the initial field E8. I've attached the spreadsheet. Thanks for all your assistance

[mrice]

Try

Please Login or Register  to view this content.

You have cells that appear to be empty but actually contain a single space. The code was detecting these as full. I've added a TRIM function to remove whitespace - hopefully this will help.

[dwporter]

Works GREAT!!!!!

One last request.Can you show me how to replicate the code for the other remaining rows in the spreadsheet...row 9(e,f,g,h) row 10(e,f,g,h) etc.

[dwporter]

I just duplicated the code as follows. It seems to work fine.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = False
Application.EnableEvents = True

If Trim(Range("E8")) <> "" Then
If Trim(Range("F8")) = "" Then Cancel = True
If Trim(Range("G8")) = "" Then Cancel = True
If Trim(Range("H8")) = "" Then Cancel = True
End If

If Range("H8") = "No" Then
If Trim(Range("D18")) = "" Then Cancel = True
If Trim(Range("E18")) = "" Then Cancel = True
If Trim(Range("F18")) = "" Then Cancel = True
End If

If Trim(Range("E9")) <> "" Then
If Trim(Range("F9")) = "" Then Cancel = True
If Trim(Range("G9")) = "" Then Cancel = True
If Trim(Range("H9")) = "" Then Cancel = True
End If

If Range("H9") = "No" Then
If Trim(Range("D18")) = "" Then Cancel = True
If Trim(Range("E18")) = "" Then Cancel = True
If Trim(Range("F18")) = "" Then Cancel = True
End If

If Cancel = True Then MsgBox "Please fill in all the cells required."

End Sub

[mrice]

Looks good.

You might consider giving a more specific message like below.

Please Login or Register  to view this content.

[Joceau]

hey there,

I know it's an old post but really useful. I just have one question.
How can i select a whole column? I've tried everything but i cannot find something that works...
Could you help me with that?

Thanks,

[FDibbins]

Joceau welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[Joceau]

No problem sorry :-)