Need to make a mandate field when one is selected

[mrdinhut]

Hello All,

I need to make a mandate field when one is selected in excel. So basically if the drop down from one column is selected, the users must enter data on the "other column" as well.

Any help greatly appreciated!


Ut

 

[Joe4]

Welcome to the Board!

I think this is going to require VBA.

One way would be to use a "Workbook_BeforeClose" or " "Workbook_BeforeSave" event procedure in VBA, which checks the cells and verifies that each row that has a value in the drop-down also has a value in the corresponding column.

Note that they must enable VBA on their computer for this workbook in order for this to work.
What I have seen people done is "hide" worksheet from view, and have VBA code in the "Workbook_Open" event procedure in VBA to unhide it.
So if they do not enable VBA, they will not see the worksheet they need (so this forces them to use VBA in order to use the workbook).

 

[mrdinhut]

Thank you Joe4 for the information.

Can you give more details about using VBA? Since I'm not familiar with that.

Many thanks!

Ut

 

[Joe4]

Can you give more details about using VBA? Since I'm not familiar with that.

Can you provide more details on your request, such as:
- the name of the sheet this should apply to
- the two columns involved
- what row they start entering data in on

 

[mrdinhut]

The name of the sheet is "Request Form"....

If the "category column" is selected, then "Model Number" must be entered.

 

[Joe4]

I need to know the exact cell addresses these fields are found in.

 

[mrdinhut]

oh ok, so if B15 is selected then E19 must be entered ..

 

[Joe4]

Just a follow-up question.

How is this form used?
After they fill out the form, what are they then supposed to do to complete the request?

 

[mrdinhut]

Hello,
After completed the form, they just save/close it...So I want them to fill that otherwise, they can't save/close the form.

Ut

 

[Joe4]

OK, because they have to save it, I think we can get away with just using the BeforeSave code.
So place this code in the "ThisWorkbook" module (it MUST be placed in there in order for this code to run automatically).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Sheets("Request Form").Range("B15") <> "" And Sheets("Request Form").Range("E19") = "" Then
        Cancel = True
        MsgBox "You must populate cell E19 before saving and try again!", vbOKOnly, "ERROR!"
    End If
End Sub

Note that as I said before, they must enable VBA on this workbook in order for this VBA code to run.