Macro not completing when userdefined function is used as formula

**maulika** · 10-16-2012, 02:18 AM

SampleSheet.xlsHi,

I am having worksheet_change eveny specified in my sheet(sheet1 say cell A1). On this work sheet change event, I have a code that changes the cell value of some cell in another sheet(sheet2 say cell A2). I also have a formula using an userdefined function specified on some another sheet's cell (say Sheet3 cell A3). This function is something like isEmpty(Sheet1.A1).

Now the problem is, when the worksheet_change event of sheet1 is called. Just befor I write Sheet2.A2 = "", the call to isEmty function is made. This causes my original worksheet_change event to breaked withou running further. This is causing problem in my code to executed further.

Please help.

Also please note that issue is faced by me only in some machines. For the rest the macro works fine.

**Sindhus** · 10-16-2012, 02:31 AM

Hi Maulika,
Welcome to the forum!

Could you attach your workbook with dummy data and macro?

**maulika** · 10-16-2012, 03:01 AM

hi...please find the attachement in the earlier post befor hi.

Thanks.

**Sindhus** · 10-16-2012, 03:35 AM

I dont see isempty function in the workbook. And there is a circular reference in your ismandatory function in sheet2.

**maulika** · 10-16-2012, 03:38 AM

the isEmpty function I mentioned was an istance, the function provided int he sample sheets are the actual ones. And there is no circular reference hapenning.

**Sindhus** · 10-16-2012, 04:37 AM

Then can you explain what is happening in this scenario? The only problem i have seen is, there is type mismatch in the value passed to ismandatory function. Thats why it is returning error. "A1" should be entered as A1 to pass it as a range.

**maulika** · 10-16-2012, 08:49 AM

what actually hapenning is when the worksheet_change event encounters Sheet2.Range("B2:B2").value = 200 or 10 ; it goes for calling the isMandatory function of module1 and then once the isMandatory function is completed, it never returns back to last row that is executed for the worksheet_change event.

**Sindhus** · 10-16-2012, 10:31 AM

Ok. But i remember seeing sheet3.range("b2:b2") in worksheet change function. I dont have excel now. So, i cant verify your file.

If you think that is causing you the trouble, you can call ismandatory function in worksheet change event, and write the computed value in sheet2.

Anyway i will look into this tomo again