VBA - function takes over

[YvorL]

Hi!

I've a sub (lists opened workbooks name) in wb1 in module1:

Sub Auto_Open()
Dim wb As Workbook
Dim Wbc As Byte
ActiveSheet.Range("T1:T65536").ClearContents
Wbc = 1
For Each wb In Application.Workbooks
If wb.Name <> "wb1.xls" Then
Cells(Wbc, 20) = wb.Name
Wbc = Wbc + 1
End If
Next

It works perfectly, but when I open my other wb (lets say wb2) which contains module1 (gives the comment of the target range):

Function MyComment(rng As Range)
        Application.Volatile
        Dim str As String
        str = Trim(rng.Comment.Text)
        MyComment = str
    End Function

and Module2 (checks if there's a comment in the target range):

Function HasComment(Target As Range) As Boolean
     On Error Resume Next
   Dim txt As String
    txt = Target.Comment.Text
    HasComment = Err.Number = 0
    Err.Clear
End Function

and run (step-by step), after
"ActiveSheet.Range("T1:T65536").ClearContents"
it jumps to Module2 loops through the cells which contains the formula, then Module1 loops through the cells which contains the formula and stops. Never gets back to the macro I intended to run.

Can I prevent to run these functions (or any function/sub in other worksheet)?
Or is there another option?

Than you,
YvorL

 

[Andrew Poulsom]

Your formulas won't recalculate if you remove:

Application.Volatile

but that may not be what you want. Have you tried setting calculation to manual?

 

[YvorL]

To be honest the functions aren't mine and I'd prefer to have a way without modifying the other workbook. I'm working on a sheet which helps co-workers in a few ways (gathers data), but if they have something similar code in any of the opened wbs, mine just won't run, makes it worthless (and looks bad).
If there's no other way than cut the other code, can I detect somehow and print to MsgBox why the macro won't work?