Allow Free Text field if Condition is met

[leonielroux]

Good day,

I have a sheet that have drop down lists in columns H, K and O. However, I want these columns to become free text if the condition of column D = "Add New".

Please help

 

[Joe4]

The only way I can think of to do that is to use VBA to remove the Data Validation from those cells.
But once it is gone, it is gone and you cannot get it back from that cell, unless you use VBA to rebuild it again, or copy it from some other cell.

 

[leonielroux]

Thanks @Joe4 ,

Can you assist with a VBA code for this? It's okay to remove the data validation as I send it to a client and once they complete it, I don't need it. I will keep the original document with me so that I can access the validations again the next month.

 

[Joe4]

If you want the columns H, K, and O to be updated to remove the Data Validation automatically as cell D is manually updated to "Add New", right-click on the sheet tab name at the bottom of our screen, select "View Code", and paste this code into the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if cells in column D updated
    Set rng = Intersect(Columns("D:D"), Target)
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells in column D
    For Each cell In rng
'       See if value in column D is "Add New"
        If cell.Value = "Add New" Then
'           Remove Data Validation from columns H, K, and O
            Cells(cell.Row, "H").Validation.Delete
            Cells(cell.Row, "K").Validation.Delete
            Cells(cell.Row, "O").Validation.Delete
        End If
    Next cell

End Sub

This should do what you want automatically as you update column D manually.