dp_favresa
Messages postés
132
Date d'inscription
vendredi 23 juillet 2004
Statut
Membre
Dernière intervention
11 mai 2010
13 oct. 2004 à 13:15
Bonjour,
Vous pouvez vous inspirer de ce qui suis (en cours de développement).
En gros, j'ai 2 feuilles, une contenant les références de nos offres
(Offre), l'autre contenant la liste des clients (Client). Le userform
contient une combobox référencée par la colonne contenant les noms des clients (voir le paramètre RowSource du combobox).
------------------------------------------------
Private Sub CommandButton2_Click()
Unload UserForm1
End Sub
------------------------------------------------
Private Sub CommandButton1_Click()
Dim Noffre As Integer
Dim NoffrX As String * 5
Dim NbCar As Integer
Dim Compt As Integer
Dim TypCar As String * 1
Dim Prix As String * 7
Dim Soumis As String
Application.ScreenUpdating = False
NoffrX = TextBox1
NbCar = 0
For Compt = 1 To 5
TypCar = Mid(NoffrX, Compt, 1)
Select Case TypCar
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
NbCar = NbCar + 1
End Select
Next Compt
NbCar = 5 - NbCar
For Compt = 1 To NbCar
NoffrX = "0" & NoffrX
Next Compt
Sheets("Offre").Select
Range("A5000").Select
Selection.End(xlUp).Offset(1, 0).Select
ActiveCell.Value = NoffrX
Noffre = NoffrX
Noffre = Noffre + 1
NoffrX = Noffre
NbCar = 0
For Compt = 1 To 5
TypCar = Mid(NoffrX, Compt, 1)
Select Case TypCar
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
NbCar = NbCar + 1
End Select
Next Compt
NbCar = 5 - NbCar
For Compt = 1 To NbCar
NoffrX = "0" & NoffrX
Next Compt
TextBox1 = NoffrX
ActiveCell.Offset(0, 1).Value = TextBox2
ActiveCell.Offset(0, 2).Value = TextBox3
ActiveCell.Offset(0, 3).Value = ComboBox1
ActiveCell.Offset(0, 4).Value = TextBox4
ActiveCell.Offset(0, 5).Value = ComboBox2
ActiveCell.Offset(0, 6).Value = TextBox5
NoffrX = TextBox6
NbCar = 0
For Compt = 1 To 5
TypCar = Mid(NoffrX, Compt, 1)
Select Case TypCar
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
NbCar = NbCar + 1
End Select
Next Compt
NbCar = 5 - NbCar
For Compt = 1 To NbCar
NoffrX = "0" & NoffrX
Next Compt
Prix = NoffrX & "." & TextBox7
ActiveCell.Offset(0, 7).Value = Prix
If CheckBox1 Then
Soumis = "Soumission"
End If
If CheckBox2 Then
Soumis = "Adjudication"
End If
ActiveCell.Offset(0, 8).Value = Soumis
Range("A5000").Select
Selection.End(xlUp).Select
ActiveWorkbook.Save
Application.ScreenUpdating = True
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = "00"
TextBox1.SetFocus
End Sub
------------------------------------------
Private Sub UserForm_Initialize()
Dim Noffre As Integer
Dim NoffrX As String * 5
Dim NbCar As Integer
Dim Compt As Integer
Dim TypCar As String * 1
Application.ScreenUpdating = False
Sheets("Offre").Select
Range("A5000").Select
Selection.End(xlUp).Select
Noffre = ActiveCell.Value
Application.ScreenUpdating = True
Noffre = Noffre + 1
NoffrX = Noffre
NbCar = 0
For Compt = 1 To 5
TypCar = Mid(NoffrX, Compt, 1)
Select Case TypCar
Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
NbCar = NbCar + 1
End Select
Next Compt
NbCar = 5 - NbCar
For Compt = 1 To NbCar
NoffrX = "0" & NoffrX
Next Compt
TextBox1.Value = NoffrX
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = "00"
End Sub
Feuille excel dans : www.favresa.ch/Ftp_file/liste offres.zip
Bon(ne) courage, développement, salutation...
(choisissez ce qui vous convient) dp