How do I only make a cell available if an option from a differentvalidated cell is selected in Excel

[Holly Carson]

I'm trying to make an application form for employees to fill out for
new hires and do the following. In a validated cell where part-time
employment is selected I want to make the cell below become available
to type in how many hours. If the temporary hire option is chosen from
that same validated cell I want to make another cell become available
to type in how long. But if full-time employee is selected from that
one validated cell I dont want anything to become available. Is this
possible?

 

[Otto Moehrbach]

What do you mean by "become available"? Exactly what do you want to see if
a specific text is selected? HTH Otto

 

[Gord Dibben]

I'm guessing OP wants cells locked or unlocked for entry based upon a DV
dropdown containing three choices.

Start with DV dropdown in A1.

A1 only cell on sheet unlocked and sheet protected with password "justme"(no
quotes)

Paste this convoluted construct into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Const pword = "justme"
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case Target.Value
Case "parttime"
Me.Unprotect Password:=pword
With Target
.Offset(1, 0).Locked = False
.Offset(2, 0).Locked = True
End With
MsgBox "Please type hours in A2"
Me.Protect Password:=pword
Case "temporary"
Me.Unprotect Password:=pword
With Target
.Offset(1, 0).Locked = True
.Offset(2, 0).Locked = False
End With
MsgBox "Please type how long in A3"
Me.Protect Password:=pword
Case Else
Me.Unprotect Password:=pword
With Target
.Offset(1, 0).Locked = True
.Offset(2, 0).Locked = True
End With
Me.Protect Password:=pword
End Select
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP