Force data entry in cell with drop down list (i.e. Prevent user from leaving cell blank)

I'm trying to use the following code to prevent my users from leaving cell (D3) blank.

Please Login or Register  to view this content.

The cell is contained in a protected worksheet. The contents of the drop down list are contained in a hidden worksheet that I'm using for data validation purposes. Let's say the list on the separate worksheet is as follows:

'
Dog
Cat

The " ' " is used so the cell appears blank until the user clicks on the cell and selects an option. I need the blank option since both options (i.e. Dog or Cat) are programmed to auto populate other cells with specific data.

The above code works great in cells that do NOT contain a drop down list. Any ideas how to modify it to suit my need? Thanks.

Matthew

AlphaFrog,

Thanks. That works great. I placed the following code in the same worksheet as D3. The only issues now I'm having are:

Please Login or Register  to view this content.

1) Since I've coded D3 to be the first field highlighted when the workbook opens and D3 is blank upon open, the warning message pops up automatically when the workbook loads. Is there a way to prevent this but to allow it if the user attempts to leave D3 without first selecting an option from the drop down list?

2) The user is required to click the "OK" button twice to close the warning message. Thoughts?

Other than that, perfect solution. Thanks.

Matthew

AlphaFrog,

Thanks again. I placed the code in my "working form" (form I use to test out code) and it works great. I then applied the code to another workbook and I'm still getting the two issues I indicated in post #3. I believe I have competing or redundant codes at work. I've attached the two workbooks that contain the code I'm trying to use. The HCT-RCS ITP project is the form where the code is glitchy. The password for the workbook, worksheets and VBA code is "j". In both workbooks I placed the code in worksheet1. Thanks.

If this gets super complicated I'll just back the code out of the form and go at it from another angle. Thanks again for taking a look.

Matthew

AlphaFrog,

That makes perfect sense. Thank you for steering me in the correct direction. The other file works perfectly since no cells are merged. Thanks.

Matthew