Excel file hangs when trying to change the drop down value which has VBA script

[BMD44]

Hello All,

I have an excel file which is populating target values (Col B) for the source value (Col J) selected from the drop down. These values are for different ERPs.

The ERP is given in cell K4 (Choice_SE) field.

I have code which when selecting ERP in cell K4 from drop down, it should clear the existing values. New values will then be entered for the ERP selected in K4.

But, when I do this, the excel hangs with out clearing the data.

I think this is because of the If Not intersect code. Please suggest how can we keep if Not Intersect code in a separate function.

Thanks in advance.

 

[MARK858]

Meanwhile, to your question, yes. Col J is a drop down for source and target is only a single which is based on the value in column J

So what is the loop trying to do? If Target is only a single cell then so is

Intersect(Target, Range("J:J"))

in fact it is the same cell as Target because Target is already in Column J

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Private Sub Worksheet_Change(ByVal Target As Range) issue in VBA

Hello All, I have excel file where in I have included 2 logics for Private Sub Worksheet_Change(ByVal Target As Range). In sheet1, we have cell K4 which is ERP and this is drop down. Once we select value from the drop down, it should display message to clear all the details. Once the...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.