VB code to clear cell contents when another cell changes - Only works first time when workbook is opened

[JV0710]

Good Day

Please can I have some help on the following

I have a worksheet where I have dependent drop-down lists. The drop downs in cells F13,G13,H13,I13,J13,K13,L13,M13,N13,O13 are all dependent on what is selected in cell B13 and they work fine. I would like the contents of the cells to clear every time I change the info in bell b13 (which is also a drop-down list)

I have the following code that I want to run every time the contents of cell B13 change

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B13")) Is Nothing Then
Range("F13,G13,H13,I13,J13,K13,L13,M13,N13,O13").ClearContents
End If
End Sub

The problem I am having is that this only works the first time when I open the workbook. after that every time I change cell B13 the data in cells F13,G13,H13,I13,J13,K13,L13,M13,N13,O13 do not clear

I cannot understand why because the same code works fine in other spreadsheets that I have - albeit not the exact same ranges

Thanks in Advance for any help you can offer

Joe

 

[dmt32]

Hi,
in the Image of your code you have set EnableEvents to False which disables ALL EVENTS until you explicitly return the property to True.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Not Intersect(Target, Range("B13")) Is Nothing Then
        Range("F13,G13,H13,I13,J13,K13,L13,M13,N13,O13").ClearContents
    End If
    
    Application.EnableEvents = True
    
End Sub

Dave

 

[JV0710]

Hi Dave

Thanks for your reply - I tried your updated code but it also does not work. My last try was to take out the "EnableEvents" completely and that did not work either

Thanks
Joe

 

[dmt32]

Hi Dave

Thanks for your reply - I tried your updated code but it also does not work. My last try was to take out the "EnableEvents" completely and that did not work either

Thanks
Joe

if the workbook is open and EnableEvents = False you will need to first reset the property from another code

try running this code from a standard module

Sub Reset()
   Application.EnableEvents = True
End Sub

Dave

 

[JV0710]

Hi Dave

Tried the reset you sent - - It still does not clear the contents when cell B13 changes

Joe

 

[dmt32]

Hi Dave

Tried the reset you sent - - It still does not clear the contents when cell B13 changes

Joe

Is cell B13 changed by Formula or by direct entry / VBA?

Dave

 

[JV0710]

Okay . . . Applied your reset, saved and closed the workbook and re-opened - - - and now it works great

Thanks very for your help on this - really appreciate it

Joe