Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    UDF Problem (Excel 2003, SP2)

    I have a user-defined function which is used in several formulas on Sheet 2 in my workbook. It works fine. However, when the Worksheet_Change event on Sheet 1 triggers, resulting in a change being made to Sheet 1, the UDF code runs. The UDF code calls some other functions, and they all work correctly, also. But once the function is done running, the code in the original Worksheet_Change event stops, too.

    Removing Application.Volatile does not help. If the change event on Sheet 1 results in a change on Sheet 1, the code jumps immediately to running the UDF (which is only used on Sheet 2). That would be okay, except when it finishes, it does not return processing to the change event handler, so that code does not finish running.

    I'm thinking I have something set wrong, or something missing in my code, or have the code in the wrong place (in a module named Functions) or some other something that is peculiar to using a UDF (not something I've done much). Any ideas? Functions module text is attached. The problem function is SumRange.

    Many thanks!

    --Karyl
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF Problem (Excel 2003, SP2)

    Does it help if you place

    Application.EnableEvents = False

    at the beginning of the Worksheet_Change event, and

    Application.EnableEvents = True

    at the end?

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UDF Problem (Excel 2003, SP2)

    No, that's already there. However, I tried removing Application.Volatile again, and now it works fine. You must have to Save or Open/Close to get the change to register with Excel. Or something else was up. I don't know which. But the Sheet 1 code runs fine, now. I had to add a recalc command to the change event of Sheet 2, however, because without the Volatile, the function doesn't calculate at all. (I was just coming to pull my question when I saw your response.)

    I have a new problem. I have code on Sheet 2 that runs when the user enters or removes a value in a certain cell. It works fine as long as the user only changes that cell. But if, for example, he selects several cells which include my target cell and hits delete, the code crashes with a Type Mismatch error. I still need the code to work if target cells are involved, but I guess it will have to loop through all the cells in a selection that are involved. Is that easy to do? I'm working up against a deadline and running on very little sleep right now, so it seems an insurmountable problem!

    <pre>If Not Application.Intersect(Target, Range("BudgetTask")) Is Nothing Then
    If Target.Value <> "" Then
    'Item added
    With Range(Target.Address)
    'Phase (lock)
    .Offset(0, -1).Locked = True
    'Description (select)
    .Offset(0, 1).Select
    End With
    Else
    'Item removed
    With Range(Target.Address)
    'Phase (unlock)
    .Offset(0, -1).Locked = False
    End With
    End If
    End If
    </pre>



    Thanks!

    --Karyl

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF Problem (Excel 2003, SP2)

    Is BudgetTask a single cell? If so, use
    <code>
    If Not Application.Intersect(Target, Range("BudgetTask")) Is Nothing Then
    If Range("BudgetTask") <> "" Then
    'Item added
    With Range("BudgetTask")
    'Phase (lock)
    .Offset(0, -1).Locked = True
    'Description (select)
    .Offset(0, 1).Select
    End With
    Else
    'Item removed
    With Range("BudgetTask")
    'Phase (unlock)
    .Offset(0, -1).Locked = False
    End With
    End If
    End If
    </code>
    If BudgetTask consists of multiple cells, you have to loop through the cells of the intersection.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UDF Problem (Excel 2003, SP2)

    It is not a single cell. I came up with this, that seems to be working. It is probably needlessly verbose, but this close to deadline, at least it appears to work!

    BTW, with all of my Worksheet_Change events disabling events, things go south if the code does not finish. I've put code to enable events in the Workbook Close event, and in my error handling, but other than that, it there any way to make sure it gets reenabled? My testers are finding all kinds of things that aren't problems because of this previous problem with the code.

    <pre>If Not Application.Intersect(Target, Range("BudgetTask")) Is Nothing Then
    For Each c In Range(Target.Address)
    If Not Application.Intersect(c, Range("BudgetTask")) Is Nothing Then
    If c.Value <> "" Then
    'Item added
    With Range(c.Address)
    'Phase (lock)
    .Offset(0, -1).Locked = True
    'Description (select)
    .Offset(0, 1).Select
    End With
    Else
    'Item removed
    With Range(c.Address)
    'Phase (unlock)
    .Offset(0, -1).Locked = False
    End With
    End If
    End If
    Next c
    End If
    </pre>


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF Problem (Excel 2003, SP2)

    Your code contains some superfluous items.
    1) Range(Target.Address) is equivalent to Target and Range(c.Address) is equivalent to c.
    2) If you loop through the intersection of Target and the BudgetTask range, you don't have to check whether each individual c is in the intersection.
    <code>
    If Not Intersect(Target, Range("BudgetTask")) Is Nothing Then
    For Each c In Intersect(Target, Range("BudgetTask"))
    If c.Value <> "" Then
    'Item added
    'Phase (lock)
    c.Offset(0, -1).Locked = True
    'Description (select)
    c.Offset(0, 1).Select
    Else
    'Item removed
    'Phase (unlock)
    c.Offset(0, -1).Locked = False
    End If
    Next c
    End If
    </code>
    If you want to make sure that EnableEvents is set to True:
    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    ...
    ...

    ExitHandler:
    Application.EnableEvents = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •