UDF conflict with Worksheet Change Event Handler

[DenoBest]

Dear All,

I have been having several problems with a complex spreadsheet I am creating, and have located the source of my problems. Namely, the problems seem to stem from conflicts between my udf's and worksheet change event handlers. Namely, I have created an input page where the user selects several options from drop down lists (made using validation). I have created a worksheet change event listener that listens for any changes to these cells. When a change is made, the event handler calls a corresponding sub that manipulates other excel sheets (performs functions such as hiding, adding deleting rows and columns, etc.). However, my problem occurs when I use these same input cells as inputs to my udf's (so that certain cells automatically change their values depending on the user option). This seems to create a conflict in excel whereby as soon as the change is made, the udf's are updated, but the subs called by the event handler arent performed correctly. Udf's seem to take the focus. Does anyone perhaps know of any way around this? perhaps telling excel to first run the subs, then allow the udf's to update? or vice-versa?

Many thanks!

DenoBest

 

[Andrew Poulsom]

The Worksheet_Change event won't fire when the result of a formula changes. To trap that you would need to use the Worksheet_Calculate event.

 

[Richard Schollar]

Andrew, I don't think that's what the OP was asking. The problem is caused by the UDFs updating following a worksheet change that in some way obstructs the Event code from functioning as intended. Is that correct DenoBest?

If so, within the UDF code itself and/or within the event code, does it improve matters if at the start of the code you insert:

Application.EnableEvents = False

and at the end of it

Application.EnableEvents = True

If I've got the wrong end of the stick, then I apologise.

Richard

 

[DenoBest]

Sorry, I haven't explained clearly.

When the user changes the cell, the worksheet change event fires correctly. However, as the cell value is also used as input into several udf's on other sheets, my guess is that those udf's begin updating just before the change event fires. This causes the procedures called by the change event to create wrong results. The problem is that these procedures are unable to set the focus (.activate and such), so the actions are not performed correctly. The udf's seem to be responsible for this. When I disable the udf's, everything works fine!

 

[DenoBest]

Richard, you are correct, but I already toggle the enableevents and screenupdating off. Here is an example in code:

The event handler:

Case Range("ERVInput").Address
Application.ScreenUpdating = False
Application.EnableEvents = False
Call ERVFunction
Sheets("Options").Activate
Target.Select
Application.ScreenUpdating = True
Application.EnableEvents = True

The UDF (where ERVType is entered as ERVInput range from above):

Function UnitERV(UnitRow As Integer, Area As Range, Area2 As Range, ERVType As String) As Double

Dim FirstCol As Range
Dim SizeCol As Integer
Dim RentCol As Integer
Dim UnitNumber As Integer
Dim FirstYearGrowth As Double
Dim LastYearGrowth As Double
Dim NumberOfYears As Integer
Dim NumberOfDays As Integer
Dim RowNum As Integer
Dim ColNum As Integer
Dim TotalERV As Double
Dim ArrayIndex As Long

Set FirstCol = Range("FirstUnitTypeLocation")
UnitNumber = Range("NumberOfUnitTypes").Value + 2
SizeCol = FirstCol.Column - (UnitNumber * 3)
RentCol = FirstCol.Column + 12

Dim ERVs(20) As Double

For IndexNum = 1 To UnitNumber
ArrayIndex = IndexNum - 1
ERVs(ArrayIndex) = Sheets("Tenancy Input").Cells(UnitRow, SizeCol + ((IndexNum - 1) * 3)).Value * Sheets("Tenancy Input").Cells(UnitRow, RentCol + ((IndexNum - 1) * 3)).Value
Next IndexNum

SizeCol = FirstCol.Column + 5
RentCol = FirstCol.Column + 12 + (UnitNumber * 3)

For IndexNum = 1 To 2
ArrayIndex = IndexNum + UnitNumber - 1
ERVs(ArrayIndex) = Sheets("Tenancy Input").Cells(UnitRow, SizeCol + ((IndexNum - 1) * 3)).Value * Sheets("Tenancy Input").Cells(UnitRow, RentCol + ((IndexNum - 1) * 3)).Value
Next IndexNum

For IndexNum = 1 To (UnitNumber + 2)
ArrayIndex = IndexNum - 1
TotalERV = TotalERV + ERVs(ArrayIndex)
Next IndexNum

If ERVType = "Month" Then
UnitERV = TotalERV * 12
Else
UnitERV = TotalERV
End If

End Function

Procedure called (ERVFunction):

Sub ERVFunction()

Sheets("Tenancy Input").Unprotect Password:="denishal07"

Sheets("Tenancy Input").Activate

Dim InitialActiveCell As Range
Set InitialActiveCell = ActiveCell.Cells

Dim EType As String
Dim RowNum As Integer
Dim SText As String

EType = Range("ERVInput").Value
RowNum = Range("FirstUnitTypeLocation").Row
SText = "ERV's (psm per " & EType & ")"

Cells(RowNum + 2, Range("FirstUnitTypeLocation").Column + 12).Select
ActiveCell.FormulaR1C1 = SText
With ActiveCell.Characters(Start:=1, Length:=Len(SText)).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
With ActiveCell.Characters(Start:=16, Length:=Len(EType)).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=16 + Len(EType), Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With

InitialActiveCell.Select
Sheets("Tenancy Input").Protect Password:="denishal07"


Sorry for all the code!

Thanks very much for the replies guys!

 

[Andrew Poulsom]

Have you tried setting Calculation to manual, and calculating at an opportune time in your code?

Also there is generally no need to activate an object to work with it in VBA.

 

[DenoBest]

I tried change the calculation settings, but the problem is that the udf's seem to start updating before the event handler changes the calculation settings!

My workbook is such that I have to have automatic except tables set as the default calculation mode.

Thanks!

 

[pgc01]

Hi DenoBest

the udf's seem to start updating before the event handler changes the calculation settings!

Yes, the UDFs calculate before both the calculate and the change events.

- Please post the exact formula that you use to call the UDF in one of the worksheets cells

- What are the Area parameters, I see they are not used in the UDF.

If I understand correctly you just want to change the sequence of the execution of the UDFs and the Events.

I believe that you can do it with an auxilliary trigger variable and by passing to the UDFs the addresses of the parameters. Then it's the events that trigger the UDFs.

Kind regards
PGC