VBA code to delete contents in a range then prevent further input to the range until correct criteria is met.

[Declamatory]

I have a dropdown in cell D8 which when selected populates a number of cells but nothing in column F. The dropdown has five options.

Could anyone provide me with VBA coding that deletes content in a range in column F and then prevents people from inputting to that range unless the correct option in the dropdown in cell D8 has been selected.

Only one of the dropdown options should allow input to the range in column F. Any other option in cell D8 would delete the content in the range in column F and prevent further input to the range until the correct dropdown option has been selected.

Thanks in anticipation.

Declamatory

 

[Declamatory]

I have a dropdown in cell D8 which when selected populates a number of cells but nothing in column F. The dropdown has five options.

Could anyone provide me with VBA coding that deletes content in a range in column F and then prevents people from inputting to that range unless the correct option in the dropdown in cell D8 has been selected.

Only one of the dropdown options should allow input to the range in column F. Any other option in cell D8 would delete the content in the range in column F and prevent further input to the range until the correct dropdown option has been selected.

Thanks in anticipation.

Declamatory

Figured it out in the end Phew! Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ReEnableEvents
Application.EnableEvents = False
If Target.Address = Range("D8").Address Then
Select Case UCase(Target.Value)
Case ""
Application.Undo
GoTo ReEnableEvents
Case UCase("Option 1")
Range("F10:F48").ClearContents
Case UCase("Option 2")
Range("F10:F48").ClearContents
Case UCase("Option 3")
Range("F10:F48").ClearContents
Case UCase("Option 4")
Range("F10:F48").ClearContents
End Select
End If
ReEnableEvents:
Application.EnableEvents = True

If Intersect(Target, Range("F10:F48")) Is Nothing Then Exit Sub
On Error GoTo ExitPoint
Application.EnableEvents = False
If Range("D8").Value <> "Option 5" Then
Application.Undo
MsgBox "Cell D8 must be Option 5 in order to input to this cell", vbCritical, "Error"
End If
ExitPoint:
Application.EnableEvents = True
End Sub