Protection feuille autoriser macros

Z

Zam

XLDnaute Nouveau
Bonjour !

C'est la premiere fois que je post sur ce forum afin d'obtenir de l'aide pour résoudre un petit problème avec les macro et la protection de la feuille.

Comment fait-on pour protéger une feuille tout en autorisant l'accès aux macros ???

Après plusieurs recherche sur internet, voilà ce que donne mes macros :

Sub laine_roche()
'
' laine_roche Macro
'

'ActiveSheet.Unprotect "zam"

Range("L14").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-12]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L14").Select
Selection.AutoFill Destination:=Range("L14:L25"), Type:=xlFillDefault
Range("L14:L25").Select
Range("L15").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-13]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L16").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-14]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L17").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-15]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L18").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-16]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L19").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-17]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L20").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-18]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L21").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-19]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L22").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-20]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L23").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-21]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L24").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-22]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L25").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-23]C[-11]:a!R[42]C[-9],3,FALSE))/0.7"
Range("L32").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-30]C[-11]:a!R[40]C[-9],3,FALSE))/0.7"
Range("L32").Select
Selection.AutoFill Destination:=Range("L32:L39"), Type:=xlFillDefault
Range("L32:L39").Select
Range("L33").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-31]C[-11]:a!R[40]C[-9],3,FALSE))/0.7"
Range("L34").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-32]C[-11]:a!R[40]C[-9],3,FALSE))/0.7"
Range("L35").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-33]C[-11]:a!R[40]C[-9],3,FALSE))/0.7"
Range("L36").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-34]C[-11]:a!R[40]C[-9],3,FALSE))/0.7"
Range("L37").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-35]C[-11]:a!R[40]C[-9],3,FALSE))/0.7"
Range("L38").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-36]C[-11]:a!R[40]C[-9],3,FALSE))/0.7"
Range("L39").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-37]C[-11]:a!R[40]C[-9],3,FALSE))/0.7"
Range("T14").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-12]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T14").Select
Selection.AutoFill Destination:=Range("T14:T25"), Type:=xlFillDefault
Range("T14:T25").Select
Range("T15").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-13]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T16").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-14]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T17").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-15]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T18").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-16]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T19").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-17]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T20").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-18]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T21").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-19]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T22").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-20]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T23").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-21]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T24").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-22]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T25").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-23]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T26").Select
ActiveCell.FormulaR1C1 = ""
Range("T32").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-30]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T32").Select
Selection.AutoFill Destination:=Range("T32:T39"), Type:=xlFillDefault
Range("T32:T39").Select
Range("T33").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-31]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T34").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-32]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T35").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-33]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T36").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-34]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T37").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-35]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T38").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-36]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("T39").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-37]C[-19]:a!R[42]C[-15],5,FALSE))"
Range("F9").Select
ActiveSheet.Protect "zam", True, True, True


End Sub
Sub mousse_polyuréthane()
'
' mousse_polyuréthane Macro
'

'ActiveSheet.Unprotect "zam"

Range("L14").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-12]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L14").Select
Selection.AutoFill Destination:=Range("L14:L25"), Type:=xlFillDefault
Range("L14:L25").Select
Range("L15").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-13]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L16").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-14]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L17").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-15]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L18").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-16]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L19").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-17]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L20").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-18]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L21").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-19]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L22").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-20]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L23").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-21]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L24").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-22]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L25").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-23]C[-11]:a!R[42]C[-10],2,FALSE))/0.7"
Range("L32").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-30]C[-11]:a!R[40]C[-10],2,FALSE))/0.7"
Range("L32").Select
Selection.AutoFill Destination:=Range("L32:L39"), Type:=xlFillDefault
Range("L32:L39").Select
Range("L33").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-31]C[-11]:a!R[40]C[-10],2,FALSE))/0.7"
Range("L34").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-32]C[-11]:a!R[40]C[-10],2,FALSE))/0.7"
Range("L35").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-33]C[-11]:a!R[40]C[-10],2,FALSE))/0.7"
Range("L36").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-34]C[-11]:a!R[40]C[-10],2,FALSE))/0.7"
Range("L37").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-35]C[-11]:a!R[40]C[-10],2,FALSE))/0.7"
Range("L38").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-36]C[-11]:a!R[40]C[-10],2,FALSE))/0.7"
Range("L39").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],a!R[-37]C[-11]:a!R[40]C[-10],2,FALSE))/0.7"
Range("T14").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-12]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T14").Select
Selection.AutoFill Destination:=Range("T14:T25"), Type:=xlFillDefault
Range("T14:T25").Select
Range("T15").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-13]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T16").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-14]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T17").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-15]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T18").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-16]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T19").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-17]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T20").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-18]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T21").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-19]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T22").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-20]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T23").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-21]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T24").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-22]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T25").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-23]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T32").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-30]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T32").Select
Selection.AutoFill Destination:=Range("T32:T39"), Type:=xlFillDefault
Range("T32:T39").Select
Range("T33").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-31]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T34").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-32]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T35").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-33]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T36").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-34]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T37").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-35]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T38").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-36]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("T39").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],a!R[-37]C[-19]:a!R[42]C[-16],4,FALSE))"
Range("F7").Select
ActiveSheet.Protect "zam", True, True, True
End Sub

Est-ce que quelqu'iun pourrait me dire pourquoi cela ne fonctionne pas ?

Merci
 

Discussions similaires

S
  • Résolu(e)
Autres ActiveWorkbook.Worksheets("vierge").AutoFilter.Sort.SortFields.Clear
2
Réponses
23
Affichages
1 K
AtTheOne
AtTheOne
S
  • Question
Microsoft 365 Erreur d'exécution '91'
Réponses
11
Affichages
444
Staple1600
Staple1600
Dadi147
  • Résolu(e)
XL 2016 cellules A, b, C et D sont automatiquement vides
Réponses
3
Affichages
378
job75
job75
L
  • Question
Microsoft 365 VBA sur outlook
Réponses
14
Affichages
668
lesoldat9
L
D
  • Résolu(e)
XL 2016 problème de renvoi resultat formule recherchev en vba
Réponses
4
Affichages
414
dav30029
D
K
  • Résolu(e)
Microsoft 365 Ouvrir un autre classeur excel (VBA) pour utiliser les données de celui dans le classeur actif
Réponses
5
Affichages
852
Dranreb
Dranreb
Mr.Adrien
  • Question
Microsoft 365 Problème VBA insertion ligne au-desus
Réponses
3
Affichages
491
Mr.Adrien
Mr.Adrien
L
  • Question
XL 2016 Macro et suppressions de lignes
Réponses
9
Affichages
611
fanfan38
fanfan38
G
  • Question
Microsoft 365 Export données d'une listview
Réponses
5
Affichages
224
GClaire
G
Eric4709
  • Résolu(e)
XL 2019 Etendre une formule à une colonne
Réponses
6
Affichages
775
sylvanu
sylvanu

Membres actuellement en ligne

16 membres et 93 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 321
Messages
2 087 259
Membres
103 498
dernier inscrit
FAHDE