Filling Mandatory Cells in Excel

[Richard Smit]

Hi there,

I have a very confusing and tough task on hand once again.:dunno

I have a workbook where if specific text is entered is one particular cell say ?A1? then there should be a comment entered in ?B1? and this should be made mandatory. I was looking at something that would make sure that the following cell is filled. Please help. I?ve tried a few coding I found online, however they do not seem to work,. Please help.

Regards,
Richard

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Offset(0, 1).Value = "" Then

MsgBox "Adjacent cell must be filled", vbExclamation + vbOKOnly, "Data Input"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[Richard Smit]

Hi there,

Thank you for the code. However, I'm still stuck. I guess I did not explain clearly. If "A1" is filled with Value "Yes" or " Maybe" only then the adjuant cell should be mandatoryly populated. If "A1" Is filled with "No" then it is not mandatory to fill the adjusant cell. Also I tryed the system to told me even after adding the code, I was not asked to fill the empty cells. I would be very happy if you can give me a sample workbook. I've attached one simple sheet with which I'm currently working.

[Bob Phillips]

This is a perennial problem.

You could throw up a message box as I did telling them to populate the adjacent cell, but you cannot force them to do it. Your only choice then is to stop them doing something else until they change it, such as enter any more data, close the workbook, etc. But IMO, process is a better solution, educate the user.

[aniket]

Is this what you wanted?

[Mitchelson]

Richard,

This is something that I've also been working on with help from xld and Simon Lloyd. Please read the following threads.

http://vbaexpress.com/forum/showthread.php?t=19605

http://vbaexpress.com/forum/showthread.php?t=19657

I modified the code in those posts to meet your requirements (see attached file)

But the code will stop the user from closing the workbook when it finds missing data and then give an error message asking the user to fill in the cells that are highlighted.

8879

[Richard Smit]

Hi Guys,

I would like to thank you all for your help. I consider each of ya'll as a Genius. You guys are too good. Mitchelson, aniket and xld thank you very much. Mitchelson's solution was PERFECT and exactly what I was looking for. aniket your solution was good too, however sorry to say, I have users who would still not fill in the cell. xld thanks to you as well for your help I learned quite a lot from your coding.

Thank you all once again. This is a very big help for me.

Regards,
Richard

[Richard Smit]

Hi guys,

I'm back and stuck again. There is a command button in the sheets that transfers the contents to the next page. You see that before the content is added to the next page by clicking the commend button the cells need to be filled not before closing. Can you guys help me with this? I'm not sure how to reference this to the command button. Sorry for the trouble and thanks in advance to you all.

Regards,
Richard

[Bob Phillips]

If it is a forms button, just assign the macro to that button - right-click, select assign macro.

[Richard Smit]

Hello and thank you,

yes, however I already have a macro assigned to it, I've assigned a macro that saves the entries from sample file i sent you earlier and transfers them to the next page which i use like a data base. So now when the user adds this content to the next page, he should be prompted to add the comments before saving the entires using the command button.

Thanks.

Regards,
Richard