Edition cellule impossible sans la barre de formule

Bonjour à tous,

J'ai réalisé un programme VBA sur Excel 2010, qui fonctionne très bien mais des que je l'utilise sur une version plus récente (2013), j'ai un bug surprenant.

J'ai une combobox qui est le resultat d'un filtre sur un tableau en fonction d'une valeur d'une cellule. Le remplissage fonctionne très bien. Je sélectionne une valeur dans ma combox -> la valeur est transféré dans une cellule .

Mais dés que je veux saisir une valeur dans une cellule de mon classeur impossible. Je suis obligé de mettre l'affichage de la barre de formule pour que cela fonctionne.Dans excel 2010 ce bug n'existe pas.

Avez vous déjà rencontré cette difficulté ?

Précision :

Si je clique sur une cellule correspondant à une liste , cela débloque l'édition de mes cellules !!

Merci pour votre aide.

Bonjour et bienvenue,

Merci de joindre un fichier pour illustrer tes propos et nous permettre d'intervenir.

Cdlt.

Bonjour,

Je joins l'nsemble du code VBA de cette feuille

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim appli As String
Dim wcol As Integer
'Sheets("Formula").Protect Password:="EVEA", UserInterfaceOnly:=True
Set KeyCells = Range("E6")
Application.ScreenUpdating = False
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
ComboBox1.CLEAR
ComboBox2.CLEAR
ComboBox1.AddItem "New formula"
appli = Range("E6").Value
'BOX TOUTES LES FORMULES

wcol = Sheets("Bearing").UsedRange.Columns.Count
ComboBox2.AddItem "________"
For i = 5 To wcol
ComboBox2.AddItem "Bearing / " & Sheets("Bearing").Cells(1, i)
Next i
wcol = Sheets("Hydraulic").UsedRange.Columns.Count
ComboBox2.AddItem "________"

For i = 5 To wcol
ComboBox2.AddItem "Hydraulic  / " & Sheets("Hydraulic").Cells(1, i)
Next i

wcol = Sheets("Turbines").UsedRange.Columns.Count
ComboBox2.AddItem "________"
For i = 5 To wcol

ComboBox2.AddItem "Turbines  / " & Sheets("turbines").Cells(1, i)
Next i

wcol = Sheets("Reducing").UsedRange.Columns.Count
ComboBox2.AddItem "________"
For i = 5 To wcol

ComboBox2.AddItem "Reducing  / " & Sheets("Reducing").Cells(1, i)
Next i

wcol = Sheets("Compressors").UsedRange.Columns.Count
ComboBox2.AddItem "________"
For i = 5 To wcol

ComboBox2.AddItem "Compressors  / " & Sheets("Compressors").Cells(1, i)
Next i

wcol = Sheets("Greases").UsedRange.Columns.Count
ComboBox2.AddItem "________"
For i = 5 To wcol
ComboBox2.AddItem "Greases  / " & Sheets("Greases").Cells(1, i)
Next i

wcol = Sheets("HDDO").UsedRange.Columns.Count
ComboBox2.AddItem "________"
For i = 5 To wcol

ComboBox2.AddItem "HDDO  / " & Sheets("HDDO").Cells(1, i)
Next i

wcol = Sheets("PCMO").UsedRange.Columns.Count
ComboBox2.AddItem "________"
For i = 5 To wcol
ComboBox2.AddItem "PCMO  / " & Sheets("PCMO").Cells(1, i)
Next i

Select Case appli
Case "Bearing":
wcol = Sheets("Bearing").UsedRange.Columns.Count
For i = 5 To wcol
ComboBox1.AddItem Sheets("Bearing").Cells(1, i)
Next i

Case "Hydraulic":
wcol = Sheets("Hydraulic").UsedRange.Columns.Count
For i = 5 To wcol
ComboBox1.AddItem Sheets("Hydraulic").Cells(1, i)
Next i

Case "Turbines":
wcol = Sheets("Turbines").UsedRange.Columns.Count
For i = 5 To wcol
ComboBox1.AddItem Sheets("turbines").Cells(1, i)
Next i

Case "Reducing":
wcol = Sheets("Reducing").UsedRange.Columns.Count
For i = 5 To wcol
ComboBox1.AddItem Sheets("Reducing").Cells(1, i)
Next i

Case "Compressors":
wcol = Sheets("Compressors").UsedRange.Columns.Count
For i = 5 To wcol
ComboBox1.AddItem Sheets("Compressors").Cells(1, i)
Next i

Case "Greases":
wcol = Sheets("Greases").UsedRange.Columns.Count
For i = 5 To wcol
ComboBox1.AddItem Sheets("Greases").Cells(1, i)
Next i

Case "HDDO":
wcol = Sheets("HDDO").UsedRange.Columns.Count
For i = 5 To wcol
ComboBox1.AddItem Sheets("HDDO").Cells(1, i)
Next i

Case "PCMO":
wcol = Sheets("PCMO").UsedRange.Columns.Count
For i = 5 To wcol
ComboBox1.AddItem Sheets("PCMO").Cells(1, i)
Next i

End Select
Range("F11").Value = ComboBox1.Text
End If

Set KeyCells = Range("G19:G41")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Rows.AutoFit
End If
'Application.DisplayFormulaBar = True
'Application.DisplayFormulaBar = False
End Sub

Private Sub ComboBox1_click()
Dim maVariable As String
Dim source As Range
Dim cible As Range
Dim appli As String
Dim DerniereLigne As Integer
Dim cpt_huile As Integer
Dim cpt_addi As Integer
Dim cpt_pack As Integer
Dim formule As String
Dim formulea As String
Dim formule_pack As String
Dim ad As Integer
Dim wcolad As Integer
Dim DerniereLigneAD As Integer
Dim x As Integer
cpt_huile = 14
'Application.DisplayFormulaBar = True
For t = 1 To 28

If t >= 1 And t <= 5 Then
formule2 = "Base oil"
formule = "=SI(G" & cpt_huile & "<>"""";""Base oil"";"""")"

Cells(cpt_huile, 4).Value = ""
Cells(cpt_huile, 5).Value = ""
Cells(cpt_huile, 6).FormulaLocal = formule
Cells(cpt_huile, 7).Value = ""
Cells(cpt_huile, 8).Value = ""
cpt_huile = cpt_huile + 1
End If
If t >= 6 Then
formule = "=SIERREUR(RECHERCHEV(G" & cpt_huile
formulea = formule & ";parametres!$G2:$I93;2;0);"""")"
formule_pack = "=SI(F" & cpt_huile & " = ""Additive Package"";add_pack(G" & cpt_huile & ");"""")"
Cells(cpt_huile, 4).FormulaLocal = formule_pack
Cells(cpt_huile, 6).FormulaLocal = formulea
formulea = formule & ";parametres!$G2:$I93;3;0);"""")"
Cells(cpt_huile, 5).FormulaLocal = formulea
Cells(cpt_huile, 7).Value = "-"
Cells(cpt_huile, 8).Value = ""
Rows(cpt_huile).AutoFit
cpt_huile = cpt_huile + 1
End If
Next t

maVariable = ComboBox1.Text
appli = Range("E6").Value
f = maVariable
Range("F11").Value = maVariable
DerniereLigne = Sheets(appli).Range("C1").End(xlDown).Row
DerniereLigneAD = Sheets("Packages").Range("C1").End(xlDown).Row

wcol = Sheets(appli).UsedRange.Columns.Count
wcolad = Sheets("Packages").UsedRange.Columns.Count

For i = 5 To wcol + 1
    If f = Sheets(appli).Cells(1, i).Value Then
    Exit For
   End If
Next i

cpt_huile = 14
cpt_addi = 19
cpt__pack = 0
cpt_ligne_debut = 0
cpt_col = i
DerniereLigne = Sheets(appli).Range("C1").End(xlDown).Row

For j = 2 To DerniereLigne
If Sheets(appli).Cells(j, i).Value <> "" Then
       If cpt_ligne_debut = 0 Then
        cpt_ligne_debut = j
        End If
        cpt_ligne_fin = j
        If Sheets(appli).Cells(j, 3).Value Like "*oil*" Then

        Cells(cpt_huile, 4) = Sheets(appli).Cells(j, 1).Value
        'Cells(cpt_huile, 5) = Sheets(appli).Cells(J, 2).Value
        'Cells(cpt_huile, 6) = Sheets(appli).Cells(J, 3).Value
        Cells(cpt_huile, 7) = Sheets(appli).Cells(j, 4).Value
        Cells(cpt_huile, 8) = Sheets(appli).Cells(j, i).Value
        cpt_huile = cpt_huile + 1
        Else

            If Sheets(appli).Cells(j, 3).Value Like "*Additive Package*" Then
                'recupération des composants de l'additif

                For ad = 4 To wcolad
                     If Sheets(appli).Cells(j, 4).Value = Sheets("Packages").Cells(1, ad).Value Then
                     Exit For
                     End If
                Next ad

                    For x = 2 To DerniereLigneAD
                    If Sheets("Packages").Cells(x, ad).Value <> "" Then               
                        Cells(cpt_addi, 4) = Cells(cpt_addi, 4) & Chr(10) & Sheets("Packages").Cells(x, 3).Value & " => " & FormatNumber(Sheets("Packages").Cells(x, ad).Value * 100, 2) & " % "

                    End If
                    Next x
            End If

        'Cells(cpt_addi, 4) = Sheets(appli).Cells(j, 1).Value
        'Cells(cpt_addi, 5) = Sheets(appli).Cells(J, 2).Value
        'Cells(cpt_addi, 6) = Sheets(appli).Cells(J, 3).Value
        Cells(cpt_addi, 7) = Sheets(appli).Cells(j, 4).Value
        Cells(cpt_addi, 8) = Sheets(appli).Cells(j, i).Value
        cpt_addi = cpt_addi + 1
        End If
End If

Next j

Range("F11").Select
Sheets("Formula").Select

 Application.DisplayFormulaBar = False
End Sub
Private Sub ComboBox2_click()

Dim maVariable As String
Dim source As Range
Dim cible As Range
Dim appli As String
Dim DerniereLigne As Integer
Dim cpt_huile As Integer
Dim cpt_addi As Integer
Dim cpt_pack As Integer
Dim formule As String
Dim formulea As String
Dim val_combo As Integer
Dim ad As Integer
Dim wcolad As Integer
Dim DerniereLigneAD As Integer
Dim x As Integer

val_combo = ComboBox2.ListIndex
Range("liste_appli") = ComboBox2.Text
If Range("E6") <> "________" Then
maVariable = Trim(Split(Range("E6"), "/")(1))
Else
MsgBox ("Your choice is not valid")
Exit Sub
End If
Range("E6") = Trim(Split(Range("E6"), "/")(0))
ComboBox1.Value = maVariable
ComboBox2.Value = maVariable

cpt_huile = 14
For t = 1 To 28
If t >= 1 And t <= 5 Then
formule2 = "Base oil"
formule = "=SI(G" & cpt_huile & "<>"""";""Base oil"";"""")"
Cells(cpt_huile, 4).Value = ""
Cells(cpt_huile, 5).Value = ""
Cells(cpt_huile, 6).FormulaLocal = formule
Cells(cpt_huile, 7).Value = ""
Cells(cpt_huile, 8).Value = ""
cpt_huile = cpt_huile + 1
End If
If t >= 6 Then
formule = "=SIERREUR(RECHERCHEV(G" & cpt_huile
formulea = formule & ";parametres!$G2:$I70;2;0);"""")"
Cells(cpt_huile, 4).Value = ""
Cells(cpt_huile, 6).FormulaLocal = formulea
formulea = formule & ";parametres!$G2:$I70;3;0);"""")"
Cells(cpt_huile, 5).FormulaLocal = formulea
Cells(cpt_huile, 7).Value = "-"
Cells(cpt_huile, 8).Value = ""
Rows(cpt_huile).AutoFit
cpt_huile = cpt_huile + 1
End If
Next t

appli = Range("E6").Value
f = maVariable
Range("F11") = maVariable
DerniereLigne = Sheets(appli).Range("C1").End(xlDown).Row
DerniereLigneAD = Sheets("Packages").Range("C1").End(xlDown).Row
wcol = Sheets(appli).UsedRange.Columns.Count
wcolad = Sheets("Packages").UsedRange.Columns.Count

For i = 5 To wcol + 1
    If f = Sheets(appli).Cells(1, i).Value Then
    Exit For
   End If
Next i

cpt_huile = 14
cpt_addi = 19
cpt__pack = 0
cpt_ligne_debut = 0
cpt_col = i
DerniereLigne = Sheets(appli).Range("C1").End(xlDown).Row

For j = 2 To DerniereLigne
If Sheets(appli).Cells(j, i).Value <> "" Then
       If cpt_ligne_debut = 0 Then
        cpt_ligne_debut = j
        End If
        cpt_ligne_fin = j
        If Sheets(appli).Cells(j, 3).Value Like "*oil*" Then

        Cells(cpt_huile, 4) = Sheets(appli).Cells(j, 1).Value
        'Cells(cpt_huile, 5) = Sheets(appli).Cells(J, 2).Value
        'Cells(cpt_huile, 6) = Sheets(appli).Cells(J, 3).Value
        Cells(cpt_huile, 7) = Sheets(appli).Cells(j, 4).Value
        Cells(cpt_huile, 8) = Sheets(appli).Cells(j, i).Value
        cpt_huile = cpt_huile + 1
        Else
                If Sheets(appli).Cells(j, 3).Value Like "*Additive Package*" Then
                'recupération des composants de l'additif

                For ad = 4 To wcolad
                     If Sheets(appli).Cells(j, 4).Value = Sheets("Packages").Cells(1, ad).Value Then
                     Exit For
                     End If
                Next ad

                    For x = 2 To DerniereLigne
                    If Sheets("Packages").Cells(x, ad).Value <> "" Then               
                        'Cells(cpt_addi, 4) = Cells(cpt_addi, 4) & Chr(10) & Sheets("Packages").Cells(x, 3).Value & " => " & FormatNumber(Sheets("Packages").Cells(x, 4).Value * 100, 2) & " % "
                        Cells(cpt_addi, 4) = Cells(cpt_addi, 4) & Chr(10) & Sheets("Packages").Cells(x, 3).Value & " => " & FormatNumber(Sheets("Packages").Cells(x, ad).Value * 100, 2) & " % "
                    End If
                    Next x
                    End If
       'Cells(cpt_addi, 4) = Sheets(appli).Cells(j, 1).Value
        'Cells(cpt_addi, 5) = Sheets(appli).Cells(J, 2).Value
        'Cells(cpt_addi, 6) = Sheets(appli).Cells(J, 3).Value
        Cells(cpt_addi, 7) = Sheets(appli).Cells(j, 4).Value
        Cells(cpt_addi, 8) = Sheets(appli).Cells(j, i).Value
        cpt_addi = cpt_addi + 1
        End If
End If
Next j

Range("F11").Select
Sheets("Formula").Select

End Sub

A l'ouverture de la feuille tout est ok l'édition fonctionne.

Dés que l'on clique Combobox1 ou Combox2 et que l'on choisit, l'édition est bloquée et je dois afficher la barre de formule pour que cela fonctionne.

Merci pour votre aide

Bonsoir dhubert,

Jean-Eric t'a seulement demandé de joindre ton fichier, pas de publier tout un roman !!!

Tu dois utiliser le bouton « Ajouter des fichiers » situé sous la fenêtre d'édition de ton post :

dhany