1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
| Private Sub CommandButton1_Click()
'Valeur à rechercher
Dim rcode
Dim resultat
Dim feuil As Worksheet
Dim increment, nbcode As Integer
'recuperation du code
rcode = TextBox1.Value
'recherche
Set feuil = ActiveSheet
nbcode = Application.WorksheetFunction.CountA(feuil.Range("$A:$A"))
increment = 2
'recupération des noms de code
Do
valeur = feuil.Cells(increment, 1)
If valeur = rcode Then
resultat = feuil.Cells(increment, 3).Value
resultat1 = feuil.Cells(increment, 5).Value
resultat2 = feuil.Cells(increment, 6).Value
resultat3 = feuil.Cells(increment, 7).Value
resultat4 = feuil.Cells(increment, 8).Value
resultat5 = feuil.Cells(increment, 2).Value
resultat6 = feuil.Cells(increment, 4).Value
'resultat = feuil.Cells(increment, 2).Value.......2étant la 2eme colonne
Exit Do
Else
If increment <= nbcode + 1 Then
increment = increment + 1
Else
Exit Do
End If
End If
Loop
'ajoute le resultat à la textbox
TextBox2.Value = resultat
TextBox3 = resultat1
TextBox4 = resultat2
TextBox5 = resultat3
TextBox6 = resultat4
TextBox16 = resultat5
TextBox17 = resultat6
If TextBox16 <> "" Then
TextBox16.BackColor = RGB(255, 0, 0)
End If
If TextBox16 = "" Then
TextBox16.BackColor = RGB(255, 255, 255)
End If
End Sub
Private Sub CommandButton4_Click()
'Valeur à rechercher
Dim rconcept
Dim resultatconcept
Dim feuil As Worksheet
Dim increment, nbconcept As Integer
'recuperation du code
rconcept = TextBox7.Value
'recherche
Set feuil = ActiveSheet
nbconcept = Application.WorksheetFunction.CountA(feuil.Range("$I:$I"))
increment = 2
'recupération des noms de code
Do
valeur = feuil.Cells(increment, 9)
If valeur = rconcept Then
resultatconcept = feuil.Cells(increment, 10).Value
resultatconcept1 = feuil.Cells(increment, 11).Value
resultatconcept2 = feuil.Cells(increment, 12).Value
resultatconcept3 = feuil.Cells(increment, 13).Value
resultatconcept4 = feuil.Cells(increment, 14).Value
resultatconcept5 = feuil.Cells(increment, 15).Value
resultatconcept6 = feuil.Cells(increment, 16).Value
resultatconcept7 = feuil.Cells(increment, 17).Value
'resultat = feuil.Cells(increment, 2).Value.......2étant la 2eme colonne
Exit Do
Else
If increment <= nbconcept + 1 Then
increment = increment + 1
Else
Exit Do
End If
End If
Loop
'ajoute le resultat à la textbox
TextBox8.Value = resultatconcept
TextBox9 = resultatconcept1
TextBox10 = resultatconcept2
TextBox11 = resultatconcept3
TextBox12 = resultatconcept4
TextBox13 = resultatconcept5
TextBox14 = resultatconcept6
TextBox15 = resultatconcept7
End Sub
Private Sub TextBox16_Change()
If TextBox16 = "" Then
TextBox16.BackColor = RGB(255, 255, 255)
End If
End Sub
Private Sub UserForm_Activate()
Dim A As Integer, wk As Workbook
For Each wk In Application.Workbooks
If wk.Windows(1).Visible = True Then
A = A + 1
End If
Next
If A = 1 Then
Application.Visible = False
Else
ThisWorkbook.Windows(1).Visible = False
End If
End Sub
'----------------------
Private Sub UserForm_Terminate()
If Application.Visible = False Then
Application.Visible = True
Else
ThisWorkbook.Windows(1).Visible = True
End If
End Sub
Private Sub CommandButton2_Click()
TextBox1 = "": TextBox2 = "": TextBox3 = "": TextBox4 = "": TextBox5 = "": TextBox6 = "": TextBox16 = "": TextBox17 = ""
End Sub
Private Sub CommandButton3_Click()
TextBox7 = "": TextBox8 = "": TextBox9 = "": TextBox10 = "": TextBox11 = "": TextBox12 = "": TextBox13 = "": TextBox14 = "": TextBox14 = ""
End Sub
Private Sub CommandButton5_Click()
Unload Me
ThisWorkbook.Close True
End Sub
Private Sub CommandButton6_Click()
Dim strPw As String
strPw = "123"
If InputBox("Veuillez rentrer le mot de passe...", "Mot de passe") <> strPw Then
MsgBox ("Mot de passe incorrect!")
Exit Sub
Else
ActiveSheet.Unprotect
MsgBox ("Bien joué Bill Gates, 1ère étape franchie avec succés :-)")
End If
Unload Me
Application.Visible = True
End Sub
Private Sub TextBox1_Change()
TextBox1.Value = UCase(TextBox1.Value)
End Sub
Private Sub TextBox7_Change()
TextBox7.Value = UCase(TextBox7.Value)
End Sub
'desactiver la croix de fermeture de l'userform
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "Vous ne pouvez pas utiliser ce bouton de fermeture." & Chr(10) _
& "Pour fermer cette boîte de dialogue, veuillez utiliser le bouton Quitter"
Cancel = True
End If
End Sub |
