Thread: Fill in drop down box when item entered cell

Hi all,

I am trying to do the following:

I have a list created which allows me to select between "Pass", "Fail", "Not Tested". This is done using data validation. All cells in column D have this option from D5 down.

I want to column to be blank (Default) when nothing is entered in the corresponding A column (e.g. When A7 is blank I would like D7 to be blank). D7 should not be allowed to enter data when there is nothing in A7.

When the column A cell is populated, I want the Default to go to "Not Tested" in the D cell (e.g. Cell A7 is populated with number 1.4.6.2, and D7 now states "Not Tested").

I want to be able to select "Pass" or "Fail" at a later date with the information still entered, but I should not be able to delete "Not Tested".

Is there an easy way to accomplish this? I'm not too familiar with VB, and I've stumbled upon some sites with useful commands, but nothing I'm doing seems to work. For example, I click cell D7 and tried typing =IF(ISBLANK(A7),"","Not Tested") but that didn't seem to work as excel spewed out "The value you entered is not valid. A user has restricted values that can be entered into this cell." I'm guessing this is due to my list.

Any ideas?

suppose data is in sheet 1
right click the sheet tab and click view code and in the window that comes up copy tis EVENT CODE

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Application.EnableEvents = False
Set r = UsedRange.Cells.SpecialCells(xlCellTypeAllValidation)
If Not Intersect(Target, r) Is Nothing And Cells(Target.Row, 1) = "" Then
Target = ""
End If
Application.EnableEvents = True
End Sub