Scenario: The contents of a validated list ("B1") changes depending on the selection made in another validated list ("A1"). If the user makes a selection in B1, but then goes back to change the selection in A1, the selection in B1 should be cleared to prevent a mismatch.
I have used the following procedure in the past on a much simpler form.
I am using the following code to dynamically change the interface color, and hidden and locked states of several fields, based on the selected item in E11. Everything works as intended.
What I would like to be able to do is call the DefaultInterface procedure whenever the selection in C11 changes. C13:E13 and C15:E15 display one of three validated lists, based on the selection in C11.
I have tried the first method, but it does not work. I have also tried detecting that the Target was not C11 or E11, but that messed with my interface changing procedures. Am I trying to do much with a change event? Thanks!
I have used the following procedure in the past on a much simpler form.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1").ClearContents
End If
End Sub
I am using the following code to dynamically change the interface color, and hidden and locked states of several fields, based on the selected item in E11. Everything works as intended.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("E11")) Is Nothing Then Exit Sub
'########################################'
'If user-selected Data Type = nothing
If Target = "" Then
'Unprotects cells
Unprotect Password:="SDR02"
'Clears previously set borders
ClearBorders
'Clears previously entered data
ClearContents
'Changes user input area to the default color and enables applicable fields.
DefaultInterface
'Protects cells
Protect Password:="SDR02"
'########################################'
'If user-selected Data Type = Labor
ElseIf Target = "Labor" Then
'Unprotects cells
Unprotect Password:="SDR02"
'Unlocks applicable cells
Range("C13:E13,G13,C15:E15,G15").Locked = False
'Locks non-applicable cells
Range("C17,E17,G17").Locked = True
'Clears previously set borders
ClearBorders
'Clears previously entered data
ClearContents
'Changes user input area to blue and enables applicable fields.
BlueInterface
'Selects default field
Range("C13").Activate
'Protects cells
Protect Password:="SDR02"
'########################################'
'If user-selected Data Type = Materials
ElseIf Target = "Materials" Then
'Unprotects cells
Unprotect Password:="SDR02"
'Unlocks applicable cells
Range("C17,E17").Locked = False
'Locks non-applicable cells
Range("C13:E13,G13,C15:E15,G15,G17").Locked = True
'Clears previously set borders
ClearBorders
'Clears previously entered data
ClearContents
'Changes user input area to purple and enables applicable fields.
PurpleInterface
'Selects default field
Range("C17").Activate
'Protects cells
Protect Password:="SDR02"
'########################################'
'If user-selected Data Type = Inspected Product
ElseIf Target = "Inspected Product" Then
'Unprotects cells
Unprotect Password:="SDR02"
'Unlocks applicable cells
Range("G17").Locked = False
'Locks non-applicable cells
Range("C13:E13,G13,C15:E15,G15,C17,E17").Locked = True
'Clears previously set borders
ClearBorders
'Clears previously entered data
ClearContents
'Changes user input area to aqua and enables applicable fields.
AquaInterface
'Selects default field
Range("G17").Activate
'Protects cells
Protect Password:="SDR02"
End If
End Sub
What I would like to be able to do is call the DefaultInterface procedure whenever the selection in C11 changes. C13:E13 and C15:E15 display one of three validated lists, based on the selection in C11.
I have tried the first method, but it does not work. I have also tried detecting that the Target was not C11 or E11, but that messed with my interface changing procedures. Am I trying to do much with a change event? Thanks!
Last edited: