svaiskau


I have the following user-defined function, which works independently (that is, it does what it is supposed to):


Function ActivityIndex(currentDate As Date, projectName As String _
, firstRow As Integer, lastRow As Integer)

Dim dateColumn As Integer
dateColumn = (currentDate + 1) / 7 - 5575

Dim count As Integer
count = 0

Dim i As Integer
i = firstRow

Do While i <= lastRow
If i >= 63 And i <= 69 Then
i = i + 1
ElseIf Worksheets(projectName).Cells(i, dateColumn) = _
Worksheets(projectName).Cells(i, dateColumn - 1) Then
i = i + 1
Else
count = count + 1
i = i + 1
End If
Loop

ActivityIndex = count / (lastRow - firstRow + 1 - (70 - 63))

End Function

However, since I have added it to my worksheet, my Worksheet_Change sub no longer works:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 3 Then
Call UpdateLink("E1", "Project View")
Call GetDates
Call ResetDate("Project View", "Weighting", 56, 5)
Call ResizeFont
End If
End Sub

Every part of the Worksheet_Change worked before I started calling my function ACTIVITYINDEX from cells on the worksheet.

Is it possible that, because it is called from a cell, ACTIVITYINDEX is also being called with every Worksheet_Change trigger and somehow out-competing my Worksheet_Change sub




Re: User-defined function appears to be preventing Worksheet_Change trigger?

Andy Pope


Hi,

If any of the routines you Call change the worksheet the worksheet change event will be raised again and could cause problems.

Maybe you could disable events while do the other routines, turning back on when complete.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 3 Then
application.EnableEvents False

Call UpdateLink("E1", "Project View")
Call GetDates
Call ResetDate("Project View", "Weighting", 56, 5)
Call ResizeFont

application.EnableEvents true

End If
End Sub







Re: User-defined function appears to be preventing Worksheet_Change trigger?

svaiskau

Is it the Worksheet_Change that needs EnableEvents turned off, or is it my function

Edit: I tried it with both, and either way I recieved "Compile Error: Invalid Use of Property"

Edit2: Missing an =, but when i fixed it, either way it's still not running my Workbook_Change.






Re: User-defined function appears to be preventing Worksheet_Change trigger?

Andy Pope

Sorry about the dropped ='s

Is there anything in the routines that uses ActivityIndex

Can you get the workbook back to a working state by simple not using the function





Re: User-defined function appears to be preventing Worksheet_Change trigger?

svaiskau

None of the routines I call use ActivityIndex.

If I comment out the ActivityIndex function (turning all the uses in Excel formulas to #NAME! errors), it will run properly after resetting EnableEvents to true (see below):

I've made some progress:

Using the immediate window, I determined that ActivityIndex was mysteriously turning EnableEvents to false. I fixed this by adding Application.EnableEvents = true to the last line without any = false at the top. This still wasn't running my Calls in Worksheet_Change properly, but at least I could get a MsgBox to pop whenever I changed the sheet.

I moved the call for UpdateLink before the IF statement and added a MsgBox debug downstream. My MsgBox doesn't pop after the call to UpdateLink, nor does UpdateLink work properly (it worked properly before.) After UpdateLink gets called, the immediate window tells me EnableEvents is still true, but it doesn't work and it doesn't allow anything downstream.

Code:

Code Snippet

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

'MsgBox "trigger "
Call UpdateLink("E1", "Project View")

If Target.Row = 1 And Target.Column = 3 Then
MsgBox "Well it looks like its working"
' Call UpdateLink("E1", "Project View")
' Call GetDates
' Call ResetDate("Project View", "Weighting", 56, 5)
' Call ResizeFont
End If

Application.EnableEvents = True
End Sub

Code Snippet

Sub UpdateLink(targetCell, outTarget)
'MsgBox "UpdateLink called" 'DEBUG
Range(targetCell).Select
Selection.Hyperlinks(1).SubAddress = "'" + Worksheets(outTarget).Cells(1, 3) + "'!A1"
MsgBox "UpdateLink has run" 'DEBUG
Application.EnableEvents = True
End Sub

If I uncomment the first MsgBox in UpdateLink, it pops properly. The second MsgBox never pops.




Re: User-defined function appears to be preventing Worksheet_Change trigger?

Andy Pope

I can not replicate your problem.

Can you share the file if so email address is andy at andypope dot info






Re: User-defined function appears to be preventing Worksheet_Change trigger?

RonZ2

Hi Guys. I'm having a similar problem and wonder if yo can help. My code executes an advanced filter as a macro, but not as VBA code once I've added a few lines. I think there's something wrong with 'range' but can't tell. Here's the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wsRefo2 As Worksheet
Dim rng As Range
Set wsRefo2 = Worksheets("Refo2")

'***************************************************************
'Created by Ron Zanetti 06/15/07
'***************************************************************

If Target.Count > 1 Then Exit Sub


If Target.Address = wsRefo2.Range("Department") _
Or Target.Address = wsRefo2.Range("Division") _
'**I think the code is exiting here. The If stmts seem wrong. The named ranges are correct though.

Then
wsRefo2.Range("Z2") = "Stepping out"


' Or Target.Address = wsReport.Range("$Ac$2") Then

' Sheets("Data Entry").Range("D3").ClearContents
' wsRefo2.Columns(26).Clear

' Sheets("Report").Range("LineItems").ClearContents

' wsReport.Range("LineItems").Value = ActiveSheet.Cells(4, 3) 'Target.Value

' ************************************************************************************
' Successfully filters on division and Department and created a list of accounts. Cool
' ************************************************************************************
wsRefo2.Range("D1:F368").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsRefo2.Range("AC1:AD2"), _
CopyToRange:=wsRefo2.Range("Z5:Z121"), Unique:=True


' ************************************************************************************
wsRefo2.Range("Z5:Z121").CurrentRegion.Sort _
Key1:=wsRefo2.Range("Z5:Z121"), _
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Set rng = wsRefo2.Range("z6").CurrentRegion
rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1) _
.Name = "LineItems"
End If
End Sub