Macro VBA et formulaire Fermé

Question

Bonjour,

j'ai une macro qui fonctionne mais qui est trop longue à s'exécuter.
Dans un formulaire VBA j'ai un bouton charger le dossier. Pour charger il lui faut presque 5 minutes pour rechercher dans une base de données avec 200 lignes.

Le code utilisé est le suivant :
Private Sub CommandButton5_Click()
Dim I As Long
Sheets("demandes").Activate
I = 5
Do Until Sheets("demandes").Range("A" & I) = ListBox3.Column(0)
Sheets("demandes").Range("B" & I) = ListBox3.Column(1)
Sheets("demandes").Range("C" & I) = ListBox3.Column(2)
I = I + 1
Loop
With Sheets("demandes").Range("A" & I)
        Me.TextBox1 = Sheets("demandes").Range("A" & I).Value
        Me.TextBox2 = Sheets("demandes").Range("B" & I).Value
        Me.TextBox3 = Sheets("demandes").Range("D" & I).Value
        Me.TextBox4 = Sheets("demandes").Range("E" & I).Value
        Me.TextBox5 = Sheets("demandes").Range("F" & I).Value
        Me.TextBox6 = Sheets("demandes").Range("G" & I).Value
        Me.ComboBox1 = Sheets("demandes").Range("C" & I).Value
        Me.ComboBox2 = Sheets("demandes").Range("H" & I).Value
        Me.ComboBox3 = Sheets("demandes").Range("I" & I).Value
        Me.TextBox7 = Sheets("demandes").Range("J" & I).Value
        Me.TextBox8 = Sheets("demandes").Range("K" & I).Value
        Me.ComboBox4 = Sheets("demandes").Range("M" & I).Value
        Me.ComboBox5 = Sheets("demandes").Range("N" & I).Value
        Me.ComboBox6 = Sheets("demandes").Range("O" & I).Value
        Me.ComboBox12 = Sheets("demandes").Range("AE" & I).Value
        Me.TextBox16 = Sheets("demandes").Range("P" & I).Value
        Me.TextBox17 = Sheets("demandes").Range("Q" & I).Value
        Me.TextBox18 = Sheets("demandes").Range("R" & I).Value
        Me.TextBox19 = Sheets("demandes").Range("S" & I).Value
        Me.TextBox20 = Sheets("demandes").Range("T" & I).Value
        Me.TextBox21 = Sheets("demandes").Range("U" & I).Value
        Me.TextBox22 = Sheets("demandes").Range("V" & I).Value
        Me.TextBox23 = Sheets("demandes").Range("W" & I).Value
        Me.TextBox24 = Sheets("demandes").Range("X" & I).Value
        Me.TextBox25 = Sheets("demandes").Range("Y" & I).Value
        Me.TextBox26 = Sheets("demandes").Range("Z" & I).Value
        Me.TextBox27 = Sheets("demandes").Range("AA" & I).Value
        Me.TextBox28 = Sheets("demandes").Range("AB" & I).Value
        Me.TextBox29 = Sheets("demandes").Range("AC" & I).Value
        Me.TextBox15 = Sheets("demandes").Range("AD" & I).Value
        Me.ComboBox12 = Sheets("demandes").Range("AE" & I).Value
        Me.TextBox30 = Sheets("demandes").Range("L" & I).Value
        Me.TextBox31 = Sheets("demandes").Range("AF" & I).Value
        Me.MultiPage1.Value = Page3
End With
End Sub

Quelqu'un sait comment la modifier pour aller plus vite à faire ma recherche.

Merci par avance

Patrice33740 · Answer

Bonjour,

Peut-être comme ça :

Option Explicit
Private Sub CommandButton5_Click()
Dim c As Range
  With Sheets("demandes")
    Set c = .UsedRange.Columns(1).Find(ListBox3.Value, , xlValues, xlWhole)
    If Not c Is Nothing Then
      Me.TextBox1.Value = .Range("A" & c.Row).Value
      Me.TextBox2.Value = .Range("B" & c.Row).Value
      Me.ComboBox1.Value = .Range("C" & c.Row).Value
      Me.TextBox3.Value = .Range("D" & c.Row).Value
      Me.TextBox4.Value = .Range("E" & c.Row).Value
      Me.TextBox5.Value = .Range("F" & c.Row).Value
      Me.TextBox6.Value = .Range("G" & c.Row).Value
      Me.ComboBox2.Value = .Range("H" & c.Row).Value
      Me.ComboBox3.Value = .Range("I" & c.Row).Value
      Me.TextBox7.Value = .Range("J" & c.Row).Value
      Me.TextBox8.Value = .Range("K" & c.Row).Value
      Me.TextBox30.Value = .Range("L" & c.Row).Value
      Me.ComboBox4.Value = .Range("M" & c.Row).Value
      Me.ComboBox5.Value = .Range("N" & c.Row).Value
      Me.ComboBox6.Value = .Range("O" & c.Row).Value
      Me.TextBox16.Value = .Range("P" & c.Row).Value
      Me.TextBox17.Value = .Range("Q" & c.Row).Value
      Me.TextBox18.Value = .Range("R" & c.Row).Value
      Me.TextBox19.Value = .Range("S" & c.Row).Value
      Me.TextBox20.Value = .Range("T" & c.Row).Value
      Me.TextBox21.Value = .Range("U" & c.Row).Value
      Me.TextBox22.Value = .Range("V" & c.Row).Value
      Me.TextBox23.Value = .Range("W" & c.Row).Value
      Me.TextBox24.Value = .Range("X" & c.Row).Value
      Me.TextBox25.Value = .Range("Y" & c.Row).Value
      Me.TextBox26.Value = .Range("Z" & c.Row).Value
      Me.TextBox27.Value = .Range("AA" & c.Row).Value
      Me.TextBox28.Value = .Range("AB" & c.Row).Value
      Me.TextBox29.Value = .Range("AC" & c.Row).Value
      Me.TextBox15.Value = .Range("AD" & c.Row).Value
      Me.ComboBox12.Value = .Range("AE" & c.Row).Value
      Me.TextBox31.Value = .Range("AF" & c.Row).Value
      Me.MultiPage1.Value = Me.MultiPage1.Page3.Index
    End If
  End With
End Sub

Macro VBA et formulaire

1 réponse

Discussions similaires