Recherche sur formulaire

Bonjour,

Je suis on ne peut plus novice sur VBA et je vous demande de l'aide.

Je mets en attache le formulaire que j'ai créé pour toute perspective de correction.

Sinon mon problème est le suivant:

  • Ma base de données contient des doublons, comment faire en sorte pour que le bouton Chercher Demande m'affiche toutes les résultantes de la recherche, comme lorsqu'on fait ctrl+f sur une feuille excel?
  • Certains tiers n'étant pas renseignés, comment faire en sorte que ledit bouton fasse également une recherche par nom (il suffit de taper une partie du nom pour qu'il m'affiche le truc)?

Merci bcp!

Option Explicit

Private Sub CheckBox1_Click()
If CheckBox1 Then
CheckBox1.Value = True
CheckBox2 = Not CheckBox1
CheckBox3 = Not CheckBox1
End If
End Sub

Private Sub CheckBox2_Click()
If CheckBox2 Then
CheckBox2.Value = True
CheckBox1 = Not CheckBox2
CheckBox3 = Not CheckBox2
End If
End Sub

Private Sub CheckBox3_Click()
If CheckBox3 Then
CheckBox3.Value = True
CheckBox2 = Not CheckBox3
CheckBox1 = Not CheckBox3
End If
End Sub

Private Sub CheckBox4_Click()
If CheckBox4 Then
CheckBox4.Value = True
CheckBox5 = Not CheckBox4
End If
End Sub

Private Sub CheckBox5_Click()
If CheckBox5 Then
CheckBox5.Value = True
CheckBox4 = Not CheckBox5
End If
End Sub

Private Sub CheckBox6_Click()
If CheckBox6 Then
CheckBox6.Value = True
CheckBox7 = Not CheckBox6
End If
End Sub

Private Sub CheckBox7_Click()
If CheckBox7 Then
CheckBox7.Value = True
CheckBox6 = Not CheckBox7
End If
End Sub

Private Sub CheckBox8_Click()
If CheckBox8 Then
CheckBox8.Value = True
CheckBox9 = Not CheckBox8
End If
End Sub

Private Sub CheckBox9_Click()
If CheckBox9 Then
CheckBox9.Value = True
CheckBox8 = Not CheckBox9
End If
End Sub

Private Sub CheckBox10_Click()
If CheckBox10 Then
CheckBox10.Value = True
CheckBox11 = Not CheckBox10
CheckBox12 = Not CheckBox10
CheckBox13 = Not CheckBox10
End If
End Sub

Private Sub CheckBox11_Click()
If CheckBox11 Then
CheckBox11.Value = True
CheckBox10 = Not CheckBox11
CheckBox12 = Not CheckBox11
CheckBox13 = Not CheckBox11
End If
End Sub

Private Sub CheckBox12_Click()
If CheckBox12 Then
CheckBox12.Value = True
CheckBox11 = Not CheckBox12
CheckBox10 = Not CheckBox12
CheckBox13 = Not CheckBox12
End If
End Sub

Private Sub CheckBox13_Click()
If CheckBox13 Then
CheckBox13.Value = True
CheckBox11 = Not CheckBox13
CheckBox12 = Not CheckBox13
CheckBox10 = Not CheckBox13
End If
End Sub

'Bouton Nouvelle demande

Private Sub CommandButton2_Click()

Worksheets("Formulaire").CheckBox1.Value = False
Worksheets("Formulaire").CheckBox2.Value = False
Worksheets("Formulaire").CheckBox3.Value = False
Worksheets("Formulaire").CheckBox4.Value = False
Worksheets("Formulaire").CheckBox5.Value = False
Worksheets("Formulaire").CheckBox6.Value = False
Worksheets("Formulaire").CheckBox7.Value = False
Worksheets("Formulaire").CheckBox8.Value = False
Worksheets("Formulaire").CheckBox9.Value = False
Worksheets("Formulaire").CheckBox10.Value = False
Worksheets("Formulaire").CheckBox11.Value = False
Worksheets("Formulaire").CheckBox12.Value = False
Worksheets("Formulaire").CheckBox13.Value = False

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""

End Sub

'Bouton Chercher tiers

Private Sub CommandButton3_Click()
Dim i   As Integer
i = 2
Dim trouver As Boolean
trouver = False
Dim j As Integer
j = 0

While Worksheets("Dossiers DRG").Cells(i, 2) <> "" And trouver = False

If Worksheets("Dossiers DRG").Cells(i, 1) = TextBox1.Text Then
trouver = True
j = i
End If

If j <> 0 Then
Worksheets("Formulaire").TextBox2.Text = Worksheets("Dossiers DRG").Cells(i, 2)
Worksheets("Formulaire").TextBox3.Text = Worksheets("Dossiers DRG").Cells(i, 3)
Worksheets("Formulaire").TextBox4.Text = Worksheets("Dossiers DRG").Cells(i, 8)
Worksheets("Formulaire").TextBox5.Text = Worksheets("Dossiers DRG").Cells(i, 9)
Worksheets("Formulaire").TextBox6.Text = Worksheets("Dossiers DRG").Cells(i, 10)
Worksheets("Formulaire").TextBox7.Text = Worksheets("Dossiers DRG").Cells(i, 12)
Worksheets("Formulaire").TextBox8.Text = Worksheets("Dossiers DRG").Cells(i, 13)

If Worksheets("Dossiers DRG").Cells(i, 4) = "Dossier" Then
Worksheets("Formulaire").CheckBox1.Value = True
End If
If Worksheets("Dossiers DRG").Cells(i, 4) = "Note" Then
Worksheets("Formulaire").CheckBox2.Value = True
End If
If Worksheets("Dossiers DRG").Cells(i, 4) = "Lotus" Then
Worksheets("Formulaire").CheckBox3.Value = True
End If

If Worksheets("Dossiers DRG").Cells(i, 5) = "Avis" Then
Worksheets("Formulaire").CheckBox4.Value = True
End If
If Worksheets("Dossiers DRG").Cells(i, 5) = "Information" Then
Worksheets("Formulaire").CheckBox5.Value = True
End If

If Worksheets("Dossiers DRG").Cells(i, 6) = "DEG" Then
Worksheets("Formulaire").CheckBox6.Value = True
End If
If Worksheets("Dossiers DRG").Cells(i, 6) = "Marché" Then
Worksheets("Formulaire").CheckBox7.Value = True
End If

If Worksheets("Dossiers DRG").Cells(i, 7) = "DGE" Then
Worksheets("Formulaire").CheckBox8.Value = True
End If
If Worksheets("Dossiers DRG").Cells(i, 7) = "RESEAU ESE" Then
Worksheets("Formulaire").CheckBox9.Value = True
End If

If Worksheets("Dossiers DRG").Cells(i, 11) = "Avis Favorable" Then
Worksheets("Formulaire").CheckBox10.Value = True
End If
If Worksheets("Dossiers DRG").Cells(i, 11) = "Refus" Then
Worksheets("Formulaire").CheckBox11.Value = True
End If
If Worksheets("Dossiers DRG").Cells(i, 11) = "AF sous conditions" Then
Worksheets("Formulaire").CheckBox12.Value = True
End If
If Worksheets("Dossiers DRG").Cells(i, 11) = "AF partiel" Then
Worksheets("Formulaire").CheckBox13.Value = True
End If

End If

i = i + 1

Wend

End Sub

'Bouton Enregistrer demande

Private Sub CommandButton1_Click()

Dim i As Integer
i = 2
Dim x As Integer

While Worksheets("Dossiers DRG").Cells(i, 2) <> ""

i = i + 1

Wend

Enregistrement (i)

End Sub

Private Sub Enregistrement(x%)

Worksheets("Dossiers DRG").Cells(x, 1) = TextBox1.Text
Worksheets("Dossiers DRG").Cells(x, 2) = TextBox2.Text
Worksheets("Dossiers DRG").Cells(x, 3) = TextBox3.Text
Worksheets("Dossiers DRG").Cells(x, 8) = TextBox4.Text
Worksheets("Dossiers DRG").Cells(x, 9) = TextBox5.Text
Worksheets("Dossiers DRG").Cells(x, 10) = TextBox6.Text
Worksheets("Dossiers DRG").Cells(x, 12) = TextBox7.Text
Worksheets("Dossiers DRG").Cells(x, 13) = TextBox8.Text

If Worksheets("Formulaire").CheckBox1.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 4) = "Dossier"
Else
Worksheets("Dossiers DRG").Cells(x, 4) = ""
End If

If Worksheets("Formulaire").CheckBox2.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 4) = "Note"
Else
Worksheets("Dossiers DRG").Cells(x, 4) = ""
End If

If Worksheets("Formulaire").CheckBox3.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 4) = "Lotus"
Else
Worksheets("Dossiers DRG").Cells(x, 4) = ""
End If

If Worksheets("Formulaire").CheckBox4.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 5) = "Avis"
Else
Worksheets("Dossiers DRG").Cells(x, 5) = ""
End If

If Worksheets("Formulaire").CheckBox5.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 5) = "Information"
Else
Worksheets("Dossiers DRG").Cells(x, 5) = ""
End If

If Worksheets("Formulaire").CheckBox6.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 6) = "DEG"
Else
Worksheets("Dossiers DRG").Cells(x, 6) = ""
End If

If Worksheets("Formulaire").CheckBox7.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 6) = "Marché"
Else
Worksheets("Dossiers DRG").Cells(x, 6) = ""
End If

If Worksheets("Formulaire").CheckBox8.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 7) = "DGE"
Else
Worksheets("Dossiers DRG").Cells(x, 7) = ""
End If

If Worksheets("Formulaire").CheckBox9.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 7) = "RESEAU ESE"
Else
Worksheets("Dossiers DRG").Cells(x, 7) = ""
End If

If Worksheets("Formulaire").CheckBox10.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 11) = "Avis Favorable"
Else
Worksheets("Dossiers DRG").Cells(x, 11) = ""
End If

If Worksheets("Formulaire").CheckBox11.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 11) = "Refus"
Else
Worksheets("Dossiers DRG").Cells(x, 11) = ""
End If

If Worksheets("Formulaire").CheckBox12.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 11) = "AF sous conditions"
Else
Worksheets("Dossiers DRG").Cells(x, 11) = ""
End If

If Worksheets("Formulaire").CheckBox13.Value = True Then
Worksheets("Dossiers DRG").Cells(x, 11) = "AF partiel"
Else
Worksheets("Dossiers DRG").Cells(x, 11) = ""
End If

End Sub

C bon j'ai trouvé toute seule comme une grande!

Rechercher des sujets similaires à "recherche formulaire"