Macro to execute upon cell entry

Tigerstyler

Board Regular
Joined
Mar 17, 2003
Messages
53
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Right click on the tab name of your worksheet, then select "View Code". Next, paste the following event procedure macro in the blank window:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

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
Top