Macro ne s'exécutant plus une fois la feuille Excel protégée
J
Bonjour,
J'ai, après un bout de temps, enfin terminé mon programme; cependant, comme il devra être utilisé par plusieurs personnes, j'aimerai protéger les cellules qu'elles n'auront pas le droit de modifier. Je suis passer par la procédure classique de protection en utilisant un mot de passe, puis, j'ai verrouiller la feuille. Seulement, quand je lance ma macro: horreur!! une erreur 400 à laquelle je ne comprends strictement rien. J'ai regarder un peu sur le forum et une solution était de programmer la macro pour qu'elle désactive la sécurité de la feuille, le temps de l'opération avant de la réactiver. Cependant, je m'inquiète un peu de la façon dont je dois écrire cette opération. Serait-ce une macro indépendante des autres ou des lignes de plus à ajouter à la macro existante. Aussi, est-ce vraiment sur pour le programme cette méthode? jusqu'à quel point les cellules que je veux protéger le seront? Je beug vraiment là...hepl!!!
Pour ceux qui auraient besoin du code:
Option Explicit
Sub Cigares()
Dim one As String
Dim X As Long
Dim Y As Long
Dim ron As Date
Dim rav As Date
Dim dens As Double
Dim Init As Double
Dim Fin As Double
Dim débit As Double
one = Sheets("Cigares").Range("C5").Value
X = CLng(Sheets("Cigares").Range("C8").Value)
Y = CLng(Sheets("Cigares").Range("C10").Value)
ron = CDate(Sheets("Cigares").Range("F8").Value)
rav = CDate(Sheets("Cigares").Range("F10").Value)
dens = CDbl(Sheets("Cigares").Range("H8").Value)
Init = CDbl(Sheets("Cigares").Range("A12").Value)
Fin = CDbl(Sheets("Cigares").Range("A13").Value)
débit = CDbl(Sheets("Cigares").Range("J8").Value)
Select Case one
Case "B1061"
If X < 260 Or X = 260 Then
Init = (0.052 * X ^ 2) + (8.623 * X) - 58.37
ElseIf X > 260 And (X < 790 Or X = 790) Then
Init = (0.019 * X ^ 2) + (22.11 * X) - 1596
ElseIf X > 790 And (X < 1140 Or X = 1140) Then
Init = (0.007 * X ^ 2) + (37.61 * X) - 6329
ElseIf X > 1140 And (X < 1790 Or X = 1790) Then
Init = (0.002 * X ^ 2) + (49.99 * X) - 13743
ElseIf X > 1790 And (X < 2060 Or X = 2060) Then
Init = (-0.007 * X ^ 2) + (81.75 * X) - 42156
ElseIf X > 2060 And (X < 2190 Or X = 2190) Then
Init = (-0.012 * X ^ 2) + (104.3 * X) - 67791
ElseIf X > 2190 And (X < 2370 Or X = 2370) Then
Init = (-0.015 * X ^ 2) + (117.2 * X) - 82003
ElseIf X > 2370 And (X < 2450 Or X = 2450) Then
Init = (-0.021 * X ^ 2) + (149.9 * X) - 126239
ElseIf X > 2450 And (X < 2570 Or X = 2570) Then
Init = (-0.023 * X ^ 2) + (159.1 * X) - 137205
ElseIf X > 2570 And (X < 2690 Or X = 2690) Then
Init = (-0.026 * X ^ 2) + (176.1 * X) - 161594
ElseIf X > 2690 And (X < 2770 Or X = 2770) Then
Init = (-0.043 * X ^ 2) + (270.5 * X) - 293049
ElseIf X > 2770 And (X < 2890 Or X = 2890) Then
Init = (-0.054 * X ^ 2) + (332.2 * X) - 380020
Else
Init = (-0.103 * X ^ 2) + (618.4 * X) - 798294
End If
If Y < 260 Or Y = 260 Then
Fin = (0.052 * Y ^ 2) + (8.623 * Y) - 58.37
ElseIf Y > 260 And (Y < 790 Or Y = 790) Then
Fin = (0.019 * Y ^ 2) + (22.11 * Y) - 1596
ElseIf Y > 790 And (Y < 1140 Or Y = 1140) Then
Fin = (0.007 * Y ^ 2) + (37.61 * Y) - 6329
ElseIf Y > 1140 And (Y < 1790 Or Y = 1790) Then
Fin = (0.002 * Y ^ 2) + (49.99 * Y) - 13743
ElseIf Y > 1790 And (Y < 2060 Or Y = 2060) Then
Fin = (-0.007 * Y ^ 2) + (81.75 * Y) - 42156
ElseIf Y > 2060 And (Y < 2190 Or Y = 2190) Then
Fin = (-0.012 * Y ^ 2) + (104.3 * Y) - 67791
ElseIf Y > 2190 And (Y < 2370 Or Y = 2370) Then
Fin = (-0.015 * Y ^ 2) + (117.2 * Y) - 82003
ElseIf Y > 2370 And (Y < 2450 Or Y = 2450) Then
Fin = (-0.021 * Y ^ 2) + (149.9 * Y) - 126239
ElseIf Y > 2450 And (Y < 2570 Or Y = 2570) Then
Fin = (-0.023 * Y ^ 2) + (159.1 * Y) - 137205
ElseIf Y > 2570 And (Y < 2690 Or Y = 2690) Then
Fin = (-0.026 * Y ^ 2) + (176.1 * Y) - 161594
ElseIf Y > 2690 And (Y < 2770 Or Y = 2770) Then
Fin = (-0.043 * Y ^ 2) + (270.5 * Y) - 293049
ElseIf Y > 2770 And (Y < 2890 Or Y = 2890) Then
Fin = (-0.054 * Y ^ 2) + (332.2 * Y) - 380020
Else
Fin = (-0.103 * Y ^ 2) + (618.4 * Y) - 798294
End If
Case "B1062"
If X < 270 Or X = 270 Then
Init = (0.049 * X ^ 2) + (9.246 * X) - 90.62
ElseIf X > 270 And (X < 720 Or X = 720) Then
Init = (0.02 * X ^ 2) + (21.45 * X) - 1480
ElseIf X > 720 And (X < 1210 Or X = 1210) Then
Init = (0.009 * X ^ 2) + (35.03 * X) - 5758
ElseIf X > 1210 And (X < 1430 Or X = 1430) Then
Init = (0.001 * X ^ 2) + (51.29 * X) - 13743
ElseIf X > 1430 And (X < 1860 Or X = 1860) Then
Init = (-0.001 * X ^ 2) + (59.6 * X) - 21532
ElseIf X > 1860 And (X < 2280 Or X = 2280) Then
Init = (-0.007 * X ^ 2) + (80.82 * X) - 40630
ElseIf X > 2280 And (X < 2400 Or X = 2400) Then
Init = (-0.016 * X ^ 2) + (123.3 * X) - 91077
ElseIf X > 2400 And (X < 2680 Or X = 2680) Then
Init = (-0.02 * X ^ 2) + (141.6 * X) - 112352
Else
Init = (-0.05 * X ^ 2) + (305.9 * X) - 337797
End If
If Y < 270 Or Y = 270 Then
Fin = (0.049 * Y ^ 2) + (9.246 * Y) - 90.62
ElseIf Y > 270 And (Y < 720 Or Y = 720) Then
Fin = (0.02 * Y ^ 2) + (21.45 * Y) - 1480
ElseIf Y > 720 And (Y < 1210 Or Y = 1210) Then
Fin = (0.009 * Y ^ 2) + (35.03 * Y) - 5758
ElseIf Y > 1210 And (Y < 1430 Or Y = 1430) Then
Fin = (0.001 * Y ^ 2) + (51.29 * Y) - 13743
ElseIf Y > 1430 And (Y < 1860 Or Y = 1860) Then
Fin = (-0.001 * Y ^ 2) + (59.6 * Y) - 21532
ElseIf Y > 1860 And (Y < 2280 Or Y = 2280) Then
Fin = (-0.007 * Y ^ 2) + (80.82 * Y) - 40630
ElseIf Y > 2280 And (Y < 2400 Or Y = 2400) Then
Fin = (-0.016 * Y ^ 2) + (123.3 * Y) - 91077
ElseIf Y > 2400 And (Y < 2680 Or Y = 2680) Then
Fin = (-0.02 * Y ^ 2) + (141.6 * Y) - 112352
Else
Fin = (-0.05 * Y ^ 2) + (305.9 * Y) - 337797
End If
Case "B1063"
If X < 130 Or X = 130 Then
Init = (0.05 * X ^ 2) + (1.958 * X) - 1.457
ElseIf X > 130 And (X < 370 Or X = 370) Then
Init = (0.016 * X ^ 2) + (6.519 * X) - 201
ElseIf X > 370 And (X < 690 Or X = 690) Then
Init = (0.008 * X ^ 2) + (11.28 * X) - 1001
ElseIf X > 690 And (X < 970 Or X = 970) Then
Init = (0.004 * X ^ 2) + (16.06 * X) - 2093
ElseIf X > 970 And (X < 1140 Or X = 1140) Then
Init = (0.002 * X ^ 2) + (19.65 * X) - 3327
ElseIf X > 1140 And (X < 1900 Or X = 1900) Then
Init = (26.99 * X) - 9038
ElseIf X > 1900 And (X < 2310 Or X = 2310) Then
Init = (24.4 * X) - 4113
ElseIf X > 2310 And (X < 2460 Or X = 2460) Then
Init = (-0.007 * X ^ 2) + (57.18 * X) - 42843
ElseIf X > 2460 And (X < 2630 Or X = 2630) Then
Init = (-0.009 * X ^ 2) + (66.22 * X) - 53266
ElseIf X > 2630 And (X < 2800 Or X = 2800) Then
Init = (-0.016 * X ^ 2) + (103.7 * X) - 103858
Else
Init = (-0.021 * X ^ 2) + (132 * X) - 144192
End If
If Y < 130 Or Y = 130 Then
Fin = (0.05 * Y ^ 2) + (1.958 * Y) - 1.457
ElseIf Y > 130 And (Y < 370 Or Y = 370) Then
Fin = (0.016 * Y ^ 2) + (6.519 * Y) - 201
ElseIf Y > 370 And (Y < 690 Or Y = 690) Then
Fin = (0.008 * Y ^ 2) + (11.28 * Y) - 1001
ElseIf Y > 690 And (Y < 970 Or Y = 970) Then
Fin = (0.004 * Y ^ 2) + (16.06 * Y) - 2093
ElseIf Y > 970 And (Y < 1140 Or Y = 1140) Then
Fin = (0.002 * Y ^ 2) + (19.65 * Y) - 3327
ElseIf Y > 1140 And (Y < 1900 Or Y = 1900) Then
Fin = (26.99 * Y) - 9038
ElseIf Y > 1900 And (Y < 2310 Or Y = 2310) Then
Fin = (24.4 * Y) - 4113
ElseIf Y > 2310 And (Y < 2460 Or Y = 2460) Then
Fin = (-0.007 * Y ^ 2) + (57.18 * Y) - 42843
ElseIf Y > 2460 And (Y < 2630 Or Y = 2630) Then
Fin = (-0.009 * Y ^ 2) + (66.22 * Y) - 53266
ElseIf Y > 2630 And (Y < 2800 Or Y = 2800) Then
Fin = (-0.016 * Y ^ 2) + (103.7 * Y) - 103858
Else
Fin = (-0.021 * Y ^ 2) + (132 * Y) - 144192
End If
Case "B1064"
If X < 250 Or X = 250 Then
Init = (0.05 * X ^ 2) + (8.254 * X) - 55.86
ElseIf X > 250 And (X < 890 Or X = 890) Then
Init = (0.017 * X ^ 2) + (22.12 * X) - 1710
ElseIf X > 890 And (X < 1520 Or X = 1520) Then
Init = (0.005 * X ^ 2) + (40.42 * X) - 8520
ElseIf X > 1520 And (X < 1930 Or X = 1930) Then
Init = (53.83 * X) - 17695
ElseIf X > 1930 And (X < 2330 Or X = 2330) Then
Init = (48.14 * X) - 6653
ElseIf X > 2330 And (X < 2420 Or X = 2420) Then
Init = (-0.023 * X ^ 2) + (156.6 * X) - 134852
ElseIf X > 2420 And (X < 2850 Or X = 2850) Then
Init = (-0.02 * X ^ 2) + (139.5 * X) - 111523
Else
Init = (-0.055 * X ^ 2) + (338.2 * X) - 393783
End If
If Y < 250 Or Y = 250 Then
Fin = (0.05 * Y ^ 2) + (8.254 * Y) - 55.86
ElseIf Y > 250 And (Y < 890 Or Y = 890) Then
Fin = (0.017 * Y ^ 2) + (22.12 * Y) - 1710
ElseIf Y > 890 And (Y < 1520 Or Y = 1520) Then
Fin = (0.005 * Y ^ 2) + (40.42 * Y) - 8520
ElseIf Y > 1520 And (Y < 1930 Or Y = 1930) Then
Fin = (53.83 * Y) - 17695
ElseIf Y > 1930 And (Y < 2330 Or Y = 2330) Then
Fin = (48.14 * Y) - 6653
ElseIf Y > 2330 And (Y < 2420 Or Y = 2420) Then
Fin = (-0.023 * Y ^ 2) + (156.6 * Y) - 134852
ElseIf Y > 2420 And (Y < 2850 Or Y = 2850) Then
Fin = (-0.02 * Y ^ 2) + (139.5 * Y) - 111523
Else
Fin = (-0.055 * Y ^ 2) + (338.2 * Y) - 393783
End If
Case "B1065"
If X < 260 Or X = 260 Then
Init = (0.051 * X ^ 2) + (8.502 * X) - 57.54
ElseIf X > 260 And (X < 490 Or X = 490) Then
Init = (0.017 * X ^ 2) + (23.09 * X) - 1803
ElseIf X > 490 And (X < 1080 Or X = 1080) Then
Init = (0.011 * X ^ 2) + (31.28 * X) - 4361
ElseIf X > 1080 And (X < 1920 Or X = 1920) Then
Init = (55.16 * X) - 17662
ElseIf X > 1920 And (X < 2030 Or X = 2030) Then
Init = (-0.009 * X ^ 2) + (91.55 * X) - 54699
ElseIf X > 2030 And (X < 2150 Or X = 2150) Then
Init = (-0.011 * X ^ 2) + (100.4 * X) - 64876
ElseIf X > 2150 And (X < 2630 Or X = 2630) Then
Init = (-0.013 * X ^ 2) + (106.1 * X) - 68138
Else
Init = (-0.035 * X ^ 2) + (222.7 * X) - 223072
End If
If Y < 260 Or Y = 260 Then
Fin = (0.051 * Y ^ 2) + (8.502 * Y) - 57.54
ElseIf Y > 260 And (Y < 490 Or Y = 490) Then
Fin = (0.017 * Y ^ 2) + (23.09 * Y) - 1803
ElseIf Y > 490 And (Y < 1080 Or Y = 1080) Then
Fin = (0.011 * Y ^ 2) + (31.28 * Y) - 4361
ElseIf Y > 1080 And (Y < 1920 Or Y = 1920) Then
Fin = (55.16 * Y) - 17662
ElseIf Y > 1920 And (Y < 2030 Or Y = 2030) Then
Fin = (-0.009 * Y ^ 2) + (91.55 * Y) - 54699
ElseIf Y > 2030 And (Y < 2150 Or Y = 2150) Then
Fin = (-0.011 * Y ^ 2) + (100.4 * Y) - 64876
ElseIf Y > 2150 And (Y < 2630 Or Y = 2630) Then
Fin = (-0.013 * Y ^ 2) + (106.1 * Y) - 68138
Else
Fin = (-0.035 * Y ^ 2) + (222.7 * Y) - 223072
End If
Case "B1066"
If X < 320 Or X = 320 Then
Init = (0.043 * X ^ 2) + (9.211 * X) - 81.9
ElseIf X > 320 And (X < 940 Or X = 940) Then
Init = (0.015 * X ^ 2) + (23.35 * X) - 1746
ElseIf X > 940 And (X < 1320 Or X = 1320) Then
Init = (0.001 * X ^ 2) + (47.78 * X) - 12402
ElseIf X > 1320 And (X < 1780 Or X = 1780) Then
Init = (52.18 * X) - 16183
ElseIf X > 1780 And (X < 2190 Or X = 2190) Then
Init = (-0.007 * X ^ 2) + (76.72 * X) - 38077
ElseIf X > 2190 And (X < 2720 Or X = 2720) Then
Init = (-0.017 * X ^ 2) + (119.5 * X) - 84058
Else
Init = (-0.054 * X ^ 2) + (319.7 * X) - 355117
End If
If Y < 320 Or Y = 320 Then
Fin = (0.043 * Y ^ 2) + (9.211 * Y) - 81.9
ElseIf Y > 320 And (Y < 940 Or Y = 940) Then
Fin = (0.015 * Y ^ 2) + (23.35 * Y) - 1746
ElseIf Y > 940 And (Y < 1320 Or Y = 1320) Then
Fin = (0.001 * Y ^ 2) + (47.78 * Y) - 12402
ElseIf Y > 1320 And (Y < 1780 Or Y = 1780) Then
Fin = (52.18 * Y) - 16183
ElseIf Y > 1780 And (Y < 2190 Or Y = 2190) Then
Fin = (-0.007 * Y ^ 2) + (76.72 * Y) - 38077
ElseIf Y > 2190 And (Y < 2720 Or Y = 2720) Then
Fin = (-0.017 * Y ^ 2) + (119.5 * Y) - 84058
Else
Fin = (-0.054 * Y ^ 2) + (319.7 * Y) - 355117
End If
Case "B1067"
If X < 270 Or X = 270 Then
Init = (0.049 * X ^ 2) + (8.24 * X) - 55.8
ElseIf X > 270 And (X < 750 Or X = 750) Then
Init = (0.018 * X ^ 2) + (21.35 * X) - 1573
ElseIf X > 750 And (X < 1460 Or X = 1460) Then
Init = (0.007 * X ^ 2) + (35.16 * X) - 5802
ElseIf X > 1460 And (X < 1790 Or X = 1790) Then
Init = (-0.003 * X ^ 2) + (63.09 * X) - 25657
ElseIf X > 1790 And (X < 2290 Or X = 2290) Then
Init = (-0.008 * X ^ 2) + (80.73 * X) - 41599
ElseIf X > 2290 And (X < 2430 Or X = 2430) Then
Init = (-0.018 * X ^ 2) + (127.9 * X) - 97639
ElseIf X > 2430 And (X < 2800 Or X = 2800) Then
Init = (-0.024 * X ^ 2) + (155.2 * X) - 128892
ElseIf X > 2800 And (X < 2870 Or X = 2870) Then
Init = (-0.085 * X ^ 2) + (498.1 * X) - 611183
Else
Init = 118.13
End If
If Y < 270 Or Y = 270 Then
Fin = (0.049 * Y ^ 2) + (8.24 * Y) - 55.8
ElseIf Y > 270 And (Y < 750 Or Y = 750) Then
Fin = (0.018 * Y ^ 2) + (21.35 * Y) - 1573
ElseIf Y > 750 And (Y < 1460 Or Y = 1460) Then
Fin = (0.007 * Y ^ 2) + (35.16 * Y) - 5802
ElseIf Y > 1460 And (Y < 1790 Or Y = 1790) Then
Fin = (-0.003 * Y ^ 2) + (63.09 * Y) - 25657
ElseIf Y > 1790 And (Y < 2290 Or Y = 2290) Then
Fin = (-0.008 * Y ^ 2) + (80.73 * Y) - 41599
ElseIf Y > 2290 And (Y < 2430 Or Y = 2430) Then
Fin = (-0.018 * Y ^ 2) + (127.9 * Y) - 97639
ElseIf Y > 2430 And (Y < 2800 Or Y = 2800) Then
Fin = (-0.024 * Y ^ 2) + (155.2 * Y) - 128892
ElseIf Y > 2800 And (Y < 2870 Or Y = 2870) Then
Fin = (-0.085 * Y ^ 2) + (498.1 * Y) - 611183
Else
Fin = 118.13
End If
Case Else
If X < 290 Or X = 290 Then
Init = (0.054 * X ^ 2) + (11.22 * X) - 99.38
ElseIf X > 290 And (X < 600 Or X = 600) Then
Init = (0.02 * X ^ 2) + (27.66 * X) - 2296
ElseIf X > 600 And (X < 1270 Or X = 1270) Then
Init = (0.012 * X ^ 2) + (37.98 * X) - 5204
ElseIf X > 1270 And (X < 2060 Or X = 2060) Then
Init = (0.001 * X ^ 2) + (63.13 * X) - 19418
ElseIf X > 2060 And (X < 2190 Or X = 2190) Then
Init = (-0.011 * X ^ 2) + (113.2 * X) - 72091
ElseIf X > 2190 And (X < 2750 Or X = 2750) Then
Init = (-0.014 * X ^ 2) + (124.4 * X) - 82627
ElseIf X > 2750 And (X < 2840 Or X = 2840) Then
Init = (-0.031 * X ^ 2) + (220.4 * X) - 218451
Else
Init = (-0.046 * X ^ 2) + (304.9 * X) - 337992
End If
If Y < 290 Or Y = 290 Then
Fin = (0.054 * Y ^ 2) + (11.22 * Y) - 99.38
ElseIf Y > 290 And (Y < 600 Or Y = 600) Then
Fin = (0.02 * Y ^ 2) + (27.66 * Y) - 2296
ElseIf Y > 600 And (Y < 1270 Or Y = 1270) Then
Fin = (0.012 * Y ^ 2) + (37.98 * Y) - 5204
ElseIf Y > 1270 And (Y < 2060 Or Y = 2060) Then
Fin = (0.001 * Y ^ 2) + (63.13 * Y) - 19418
ElseIf Y > 2060 And (Y < 2190 Or Y = 2190) Then
Fin = (-0.011 * Y ^ 2) + (113.2 * Y) - 72091
ElseIf Y > 2190 And (Y < 2750 Or Y = 2750) Then
Fin = (-0.014 * Y ^ 2) + (124.4 * Y) - 82627
ElseIf Y > 2750 And (Y < 2840 Or Y = 2840) Then
Fin = (-0.031 * Y ^ 2) + (220.4 * Y) - 218451
Else
Fin = (-0.046 * Y ^ 2) + (304.9 * Y) - 337992
End If
End Select
débit = (((Fin - Init) * dens * (10 ^ -3)) / ((rav - ron) * 24))
Sheets("Cigares").Range("J8").Value = débit
Sheets("cigares").Range("A12").Value = Init
Sheets("Cigares").Range("A13").Value = Fin
End SubBonsoir
Il faut déprotéger la feuille au début de la macro et la reprotéger à la fin
Sub Cigares()
Sheets("cigares").Unprotect Password:="Ton Mot de Passe"
......
......
Sheets("cigares").Protect Password:="Ton Mot de Passe"
ActiveSheets.ProtectAmicalement
Nad
J
Merci Dan pour ta réponse claire et précise.
Imaginons maintenant que je veuille empêcher l'utilisateur d'entrer des valeurs qui poserait problème, comment pourrai je faire pour faire afficher un message d'erreur. L'action se passera dans l'instruction après le End Select: Je voudrais mettre l'affichage du message d'erreur dans une structure condition:
If ran<>ron then
débit = (((Fin - Init) * dens * (10 ^ -3)) / ((rav - ron) * 24))
Else
'Affichage message d'erreur: rav=ron'