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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,143
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.
 

Forum statistics

Threads
1,181,790
Messages
5,932,062
Members
436,816
Latest member
Composh

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