Prevent selection of a dropdown option if a different cell is empty

Hi all,

I'm new to the forum so bear with me! Running MS Excel 2010. Very new to VBA so just finding my footing. My problem is this:


I have a large table of data with rows of tasks. The columns are things like the client name, the date the task was added, the employee assigned to the task, etc.

One of the columns is the "Status" of the task - i.e. whether it is 'In Progress', 'Not Started', or 'Complete'. There is a dropdown using data validation for this column.

I want the spreadsheet to be such that the user cannot choose the "Complete" option unless the 'Date Completed' field has been filled in (ie a message box appears).

The table starts in cell B5 (ie the table headers are all in Row B).
Status: Column G
Date Completed: Column Q


Can someone advise as to how I can write a macro to help me acheive this?

Many thanks!

Hi guys

Thanks so much for your responses. The code doesn't seem to work for me (I just copied and pasted into the worksheet module).

Not sure if this makes a difference, but there are already lots of tasks (i.e rows of information) in the table already, and when i mark one of them as complete, nothing happens even though there is no completed date filled in Column Q. Any ideas?

Thanks again!

Hi Winon,

Sorry, I'm a bit confused by your suggestion. Just to reiterate, the data validation is in col G where the user selects the "Status"of their task and the date completed (col Q) is a freely typing field. Where should I place your IF formula?

Thanks! Will try and get a sample file uploaded as well.

Ah it worked! Brilliant. Thanks so much!

I spoke too soon! The way the formula works is that the user can mark a row as complete, and then simply not fill in the "Completed Date". They have to click on the "Completed Date" cell for the error message to come up.

Ideally, I want it such that the person can't select completed and then just leave the row like that - they have to fill in the completed date. Any ideas?

That is awesome, thanks so much. It works perfectly - apart from one slight problem! There is already a macro in there that I've pasted below. It is to insert a new row at the bottom of the table with the same formatting (colours, date formatting) etc. When I click the button with that macro, I get the error message twice! Any thoughts? Thanks again!

The macro may also be really bad code - i just tried recording and messing with it until it worked.

Sub insertnewrow()

Dim Lr As Integer

Lr = Range("B" & Rows.Count).End(xlUp).Row

Rows(Lr + 1).Insert Shift:=xlDown 'Ins

Rows(Lr).Copy

Rows(Lr + 1).PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

End Sub

Sorry Winon - never got round to making a sample and couldn't upload the whole thing because of names of clients etc! Ollie - it worked perfectly. Thanks for all of your help both!

Hi all - An additional question related to this. The code in post #10 works perfectly - until I want to add or delete a row. When I do so, I get the error message "Status cannot be completed without providing a completion date".

Any ideas? Thanks!

Winon - this is awesome, thanks!