Choosing from a list prevent macro execution

[sharky9]

Hi,

I've got a problem with a drop-down list. When I choose a number from it, my macros doesn't execute correctly. However, writing the number manually, everything works fine. I have used lign by lign execution and found that an macro I didn't write is the problem.

When using the drop-down list choice, the lign "If Not .On Then Exit Function" exit the whole execution (and not the function), while typing the number, everything works as it should.

Function AutoFilter_Criteria(Header As Range) As String
    Dim strCri1 As String, strCri2 As String
    Application.Volatile
    With Header.Parent.AutoFilter
        With .Filters(Header.Column - .Range.Column + 1)
            If Not .On Then Exit Function
            strCri1 = .Criteria1
            If .Operator = xlAnd Then
                strCri2 = " AND " & .Criteria2
            ElseIf .Operator = xlOr Then
                strCri2 = " OR " & .Criteria2
            End If
        End With
    End With
    AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function

BTW, I never execute that function from my VBA code. Instead it's used in 6 cells in my sheet. But I guess that excel recalculate that function each time a cell in the scope of that function get changed.

So, one possible way would be to stop excel to calculate the result of formula while VBA is doing some stuff, how could I do that?

Thanks,
sharky9

EDIT: I'm using Excel 2003.

 

[sharky9]

Update:
I've seen this thread: http://www.mrexcel.com/forum/showthread.php?t=434788

So I think this the same exam problem that the UDF execute with every change. So that's fine for me.

However, I still have the problem that the "exit function" end everything and not just the function itself. But, it only happen when the macro are executed using that code AND the new value is choosen from a drop-down list (entering the value manually make it to run just as expected):

Sub macroProjetsEmployes()
    Application.ScreenUpdating = False
    'La macro s'exécute automatiquement au changement de la colonne des projets,
    'le mode modification doit donc être actif.
    If Sheets("Options").Range("B6").Value = "oui" Then
        maj_Projets_Employes.majProjetsEmployes
        [B]maj_Lots_Employes.majLotsEmployes[/B]
    Else
        MsgBox "La mise à jour des listes de lots est impossible." & vbNewLine & "Veillez changer l'options de modification."
    End If
 
    Application.ScreenUpdating = True
End Sub

The UDF is triggered when inside the bold line.

However, the "exit function" does not end everything when it's launched using that code:

Sub fctProjetsEmployes()
    Application.ScreenUpdating = False
    'La macro seule ne peut pas être exécuté par un bouton,
    'elle peut toutefois être appelé par une autre macro qui
    's'exécute d'un bouton, le mode de modif doit donc être vérifié.
    If Not Sheets("Options").Range("B6").Value = "oui" Then
        'On retire la protection et met le classer en mode modification
        Application.Run "Verrou.deproteger"
        Sheets("Options").Range("B6").Value = "oui"
 
        'On met à jour les projets des employés
        maj_Projets_Employes.majProjetsEmployes
        [B]maj_Lots_Employes.majLotsEmployes[/B]
 
        'On enlève le mode modification et on remet la protection
        Sheets("Options").Range("B6").Value = "non"
        Application.Run "Verrou.proteger"
    Else
        'Le mode modification est actif, on retire la protection et on met à jour les graphiques
        Application.Run "Verrou.deproteger"
        maj_Projets_Employes.majProjetsEmployes
        [B]maj_Lots_Employes.majLotsEmployes[/B]
    End If
 
    Application.ScreenUpdating = True
End Sub

Again the UDF is triggered inside the bold line. However, the "exit function" line does not halt the full execution, but only the local UDF. The only thing I may think about is the Application.Run "Verrou.deproteger", but I'm not sure at all.

I hope I gave enough informations and made myself clear?...

 

[sharky9]

Well, another update.

I've corrected the problem with a workaround. The problem is still there under the same conditions, but I've managed to get things still working the way it's supposed by executing the UDF via VBA when the sheet is desactivate (it does not need to be executed every time a cell change and the use of the UDF is valid only when switching to the charts, which mean the sheet WILL deactivate before the cell are used.)

Thanks anyway.