Macro to execute upon cell entry

[Tigerstyler]

Hi,

I have a drop-down box in cell B6, giving the user two data validated options, "X-DOCK" or "STOCK".

If "STOCK" is selected, I would like to clear the contents of cells B4 and B5 (B4 and B5 are also data validated drop-down boxes working from lists).

I have tried adding changing the formula of the list, so that when "STOCK" is selected, the list entries go blank. However, the drop-down selections in B4 and B5 stay on the same selection unless manually refreshed.

Any ideas?!

Many thanks

 

[Joe4]

Right click on the tab name of your worksheet, then select "View Code". Next, paste the following event procedure macro in the blank window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    
    If Target.Address = "$B$6" And Target = "Stock" Then
        Range("B4:B5").ClearContents
    End If
    
    Application.EnableEvents = True
    
End Sub

Now, if you select "Stock" in cell B6, it will automatically clear out B4 and B5.

 

[Tigerstyler]

Brilliant!

ray: