How to do conditional validation on one cell, dependant on specific data in a second cell

T

tiburon guy

Is there a way to restrict data entry in one cell, ideally like how
excel generates a pop-up to the user when a cell is locked and
protected, until specific data has been entered into a second cell.

In boolean logic terms something like:

if A2=blank then A1=locked cell

Then as soon as the user enters something into A2, cell A1 would
become user accessible. Any way to accomplish this? The problem with
data validation is that it's always enabled, not conditional based on
a secondary cell. I have a building permit tracking worksheet with ~20
columns and the 'permit type' column must be completely locked from
user input until the 'permit issued' column has been filled. Thanks.
 
B

Bob Phillips

This VBA code will clear out any input in A1 if A2 is blank.

To input, right-click the sheet tab, select View Code from the menu, and
paste the code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
If Me.Range("A2").Value = "" Then
Target.Value = ""
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
E

Earl Kiosterud

ti,

In Data - Validation, try Custom, =NOT(ISBLANK(A2)). Or A2<>"" A1 should
be selected when you do this. You can put your own message telling the user
about A2.
 
Top