Sub Etats()
[c2] = Right$(Mid(Application.Caller, InStr(Application.Caller, "-") + 1), 2)
[c2] = Mid(Application.Caller, InStr(Application.Caller, "-") + 1)
' ôter toutes les couleurs
For n = 1 To ActiveSheet.Shapes.Count - 3
ActiveSheet.Shapes(n).Fill.ForeColor.SchemeColor = 9 'Blanc
Next n
' ci-dessous on colorie tous les dépt concernés en bleu
Select Case [c2]
Case 1 '(Delaware)
ActiveSheet.Shapes("US-1").Fill.ForeColor.SchemeColor = 4 ';;
Case 2 '(Pennsylvanie)
ActiveSheet.Shapes("US-2").Fill.ForeColor.SchemeColor = 4 ';;;;;
Case 3 '(New Jersey)
ActiveSheet.Shapes("US-3").Fill.ForeColor.SchemeColor = 4 ';;;;;;
Case 4 '(Géorgie)
ActiveSheet.Shapes("US-4").Fill.ForeColor.SchemeColor = 4 ';;;;;;
Case 5 '(Connecticut)
ActiveSheet.Shapes("US-5").Fill.ForeColor.SchemeColor = 4 ';;;;;
Case 6 '(Massachusetts)
ActiveSheet.Shapes("US-6").Fill.ForeColor.SchemeColor = 4 ';;;;
Case 7 '(Maryland)
ActiveSheet.Shapes("US-7").Fill.ForeColor.SchemeColor = 4 ';;;;;;
Case 8 '(Caroline du Sud)
ActiveSheet.Shapes("US-8").Fill.ForeColor.SchemeColor = 4 ';;;;;;
Case 9 '(New Hampshire)
ActiveSheet.Shapes("US-9").Fill.ForeColor.SchemeColor = 4 ';;;;;;
Case 10 '(Virginie)
ActiveSheet.Shapes("US-10").Fill.ForeColor.SchemeColor = 4
Case 11 '(New York)
ActiveSheet.Shapes("US-11").Fill.ForeColor.SchemeColor = 4
Case 12 '(Caroline du Nord)
ActiveSheet.Shapes("US-12").Fill.ForeColor.SchemeColor = 4
Case 13 '(Rhode Island)
ActiveSheet.Shapes("US-13").Fill.ForeColor.SchemeColor = 4
Case 14 '(Vermont)
ActiveSheet.Shapes("US-14").Fill.ForeColor.SchemeColor = 4
Case 15 '(Kentucky)
ActiveSheet.Shapes("US-15").Fill.ForeColor.SchemeColor = 4
Case 16 '(Tennessee)
ActiveSheet.Shapes("US-16").Fill.ForeColor.SchemeColor = 4
Case 17 '(Ohio)
ActiveSheet.Shapes("US-17").Fill.ForeColor.SchemeColor = 4
Case 18 '(Louisiane)
ActiveSheet.Shapes("US-18").Fill.ForeColor.SchemeColor = 4
Case 19 '(Indiana)
ActiveSheet.Shapes("US-19").Fill.ForeColor.SchemeColor = 4
Case 20 '(Mississippi)
ActiveSheet.Shapes("US-20").Fill.ForeColor.SchemeColor = 4
Case 21 '(Illinois)
ActiveSheet.Shapes("US-21").Fill.ForeColor.SchemeColor = 4
Case 22 '(Alabama)
ActiveSheet.Shapes("US-22").Fill.ForeColor.SchemeColor = 4
Case 23 '(Maine)
ActiveSheet.Shapes("US-23").Fill.ForeColor.SchemeColor = 4
Case 24 '(Missouri)
ActiveSheet.Shapes("US-24").Fill.ForeColor.SchemeColor = 4
Case 25 '(Arkansas)
ActiveSheet.Shapes("US-25").Fill.ForeColor.SchemeColor = 4
Case 26, 260 '(Michigan)
ActiveSheet.Shapes("US-26").Fill.ForeColor.SchemeColor = 4
ActiveSheet.Shapes("US-260").Fill.ForeColor.SchemeColor = 4
Case 27 '(Floride)
ActiveSheet.Shapes("US-27").Fill.ForeColor.SchemeColor = 4
Case 28 '(Texas)
ActiveSheet.Shapes("US-28").Fill.ForeColor.SchemeColor = 4
Case 29 '(Iowa)
ActiveSheet.Shapes("US-29").Fill.ForeColor.SchemeColor = 4
Case 30 '(Wisconsin)
ActiveSheet.Shapes("US-30").Fill.ForeColor.SchemeColor = 4
Case 31 '(Californie)
ActiveSheet.Shapes("US-31").Fill.ForeColor.SchemeColor = 4
Case 32 '(Minnesota)
ActiveSheet.Shapes("US-32").Fill.ForeColor.SchemeColor = 4
Case 33 '(Oregon)
ActiveSheet.Shapes("US-33").Fill.ForeColor.SchemeColor = 4
Case 34 '(Kansas)
ActiveSheet.Shapes("US-34").Fill.ForeColor.SchemeColor = 4
Case 35 '(Virginie-Occidentale)
ActiveSheet.Shapes("US-35").Fill.ForeColor.SchemeColor = 4
Case 36 '(Nevada)
ActiveSheet.Shapes("US-36").Fill.ForeColor.SchemeColor = 4
Case 37 '(Nebraska)
ActiveSheet.Shapes("US-37").Fill.ForeColor.SchemeColor = 4
Case 38 '(Colorado)
ActiveSheet.Shapes("US-38").Fill.ForeColor.SchemeColor = 4
Case 39 '(Dakota du Sud)
ActiveSheet.Shapes("US-39").Fill.ForeColor.SchemeColor = 4
Case 40 '(Dakota du Nord)
ActiveSheet.Shapes("US-40").Fill.ForeColor.SchemeColor = 4
Case 41 '(Montana)
ActiveSheet.Shapes("US-41").Fill.ForeColor.SchemeColor = 4
Case 42 '(Washington)
ActiveSheet.Shapes("US-42").Fill.ForeColor.SchemeColor = 4
Case 43 '(Idaho)
ActiveSheet.Shapes("US-43").Fill.ForeColor.SchemeColor = 4
Case 44 '(Wyoming)
ActiveSheet.Shapes("US-44").Fill.ForeColor.SchemeColor = 4
Case 45 '(Utah)
ActiveSheet.Shapes("US-45").Fill.ForeColor.SchemeColor = 4
Case 46 '(Oklahoma)
ActiveSheet.Shapes("US-46").Fill.ForeColor.SchemeColor = 4
Case 47 '(Nouveau-Mexique)
ActiveSheet.Shapes("US-47").Fill.ForeColor.SchemeColor = 4
Case 48 '(Arizona)
ActiveSheet.Shapes("US-48").Fill.ForeColor.SchemeColor = 4
Case 49 '(Alaska)
ActiveSheet.Shapes("US-49").Fill.ForeColor.SchemeColor = 4
Case 50 '(Hawaï)
ActiveSheet.Shapes("US-50").Fill.ForeColor.SchemeColor = 4
End Select
On Error Resume Next
' et on colorie le dépt cliqué en vert
'=======Parti de L'Userf==========
choix = Format(CStr([c2]), "00")
ActiveSheet.Shapes("US-" & choix).Fill.ForeColor.SchemeColor = 4
If ActiveSheet.CheckBox1.Value = True Then
Pref = LTrim(Replace(ActiveSheet.Shapes("US-" & choix).TextFrame2.TextRange.Characters.Text, vbLf, ""))
Pref = LTrim(Mid(Pref, 3))
Sheets(Pref).Visible = True
Sheets(Pref).Activate
End If
For Each sh In Sheets
If sh.Name <> "Etats_Unis" And sh.Name <> "Bd" And sh.Name <> Pref Then '"Feuil3" And sh.Name <> Pref Then
sh.Visible = False
End If
Next
With Feuil2
Set cel = .Columns(2).Find(Feuil1.Range("c2"), , , xlWhole)
If Not cel Is Nothing Then lig = cel.Row
End With
With UserEtats_Unis
.T2 = Feuil2.Cells(lig, 4): .T3 = Feuil2.Cells(lig, 3): .T4 = Feuil2.Cells(lig, 2): .T1 = Feuil2.Cells(lig, 2)
For i = 5 To 11
.Controls("T" & i) = Feuil2.Cells(lig, i)
Next i
.Show 0
'Stop
'Code pour avoir le rafraichissement des textbox
With UserEtats_Unis
.T2 = Feuil2.Cells(lig, 4): .T3 = Feuil2.Cells(lig, 3): .T4 = Feuil2.Cells(lig, 1): .T1 = Feuil2.Cells(lig, 2)
For i = 5 To 17
.Controls("T" & i) = Feuil2.Cells(lig, i)
Next i
.UserForm_Activate
.Show 0
End With
End With
End Sub
Sub init()
Dim sh As Shape
For Each sh In Feuil1.Shapes
If InStr(sh.Name, "-") > 0 Then sh.OnAction = "Etats"
Next sh
End Sub
Sub Oter_Couleur()
Dim n As Integer
For n = 1 To ActiveSheet.Shapes.Count - 3
'ActiveSheet.Shapes(n).Fill.ForeColor.SchemeColor = 9
Next n
End Sub
Sub Liste()
For ctl = 1 To ActiveSheet.Shapes.Count
MsgBox ActiveSheet.Shapes(ctl).Name & " - n°" & ctl
ActiveSheet.Shapes(ctl).Select
Next
End Sub