Clear cell contents not working based on menu selection

Galley

Board Regular
Joined
Nov 8, 2012
Messages
64
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.

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:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
What I ended up doing was locking C11 when a selection has been made in C13. C11 is required for several VLOOKUP procedures, so locking was the best option. The user has no reason to change the selection in C11 after the initial selection.

Thanks for looking!
 
Upvote 0

Forum statistics

Threads
1,214,586
Messages
6,120,402
Members
448,958
Latest member
Hat4Life

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top