run Time Errors in worksheet with VB Code

[vze]

I have two dependent drop down lists.
Column J populates based on the selection of Column I.

Scenario
1. I select a value in both Column I and J but then change my mind and want to replace the column I value, I want column J to be cleared. Formula 1 does this.
2. If Column I is blank, Column J should be blank. 2nd formula does this.

Issue is if I try to select all rows in this excel and delete them I get run time 13.
If I try to enter values in the other regular text cells and tab or move to another cell I get run time 1004.

How do I prevent these run time errors because they turn off all validations.

I have some headers locked but the sheet is not protected. So not sure why this happens

 

[offthelip]

Your code is very likely to cause excel to crash, this is because it goes into a loop. You should turn of events BEFORE you write anything to the worksheet , otherwise the action of writing to the worksheet will cause the same subroutine to be called again and again and again forever or until some error cause it to crash. so just move this line of code

Application.EnableEvents =False

to just before the first if statement
Can I say the saving the workbook every time you make any change to the worksheet is likely to make the workbook very slow and tedious to work with. Even if you are saving to an SSD drive is will still be slow. Not recommended

 

[vze]

ok that didnt work, I still get run time errors. Is there another way to achieve the result in red in the attached screenshot. Basically I want the row in column Sub Root cause to reset each time a selection is made under Root Cause AND if Root Cause is deleted to a blank I want Sub Root Cause = blank

The vb code in original post works for these columns but causes run time errors on other columns when making selections, moving cells or deleting data in the other cells.

 

[offthelip]

The code you posted in post#3 will cause errors YOU MUST PUT

Application.EnableEvents =False

at the top of the module
and

Application.EnableEvents =TRUE

at the bottom.
ther is no point in lookingfor other errors until you have done that!!

 

[vze]

The code you posted in post#3 will cause errors YOU MUST PUT

Application.EnableEvents =False

at the top of the module
and

Application.EnableEvents =TRUE

at the bottom.
ther is no point in lookingfor other errors until you have done that!!

so I did in fact do this even before posting the original question. The same errors occur either way. Same run time errors I posted above.

 

[offthelip]

I think your probelm might be because you are checking validation.type on cells that don't have validation. so try this modification:

If Target.column = 9 then
 If target.validation.type=3 then
  target.offset(0,1).value=""
 end if
end if

 

[vze]

I think your probelm might be because you are checking validation.type on cells that don't have validation. so try this modification:

If Target.column = 9 then
 If target.validation.type=3 then
  target.offset(0,1).value=""
 end if
end if

Thanks I think this has worked. I did this with the 2nd part of the code as well and it seems to be working. Thanks so much!