Re : checkbox OK sur 2003 et non sur 2000
merci pour ta réponse,
le fichier est " trés gros"(6 M0).
Il s'agit de la mise en memoire et du vidage tampon des checkBox
je t'en envoi un extrait.
pour L'USF:
Private Sub CommandButton1_Click()
Dim derlig As Integer
Application.ScreenUpdating = False
Call effaceBSD_Vierge
If Sheets("CAHIER DE SUIVI").Range("D4") = "" Then
Me.TextBox1 = CDate(Date)
Me.TextBox10 = Format(Date, "DD/MM")
Me.TextBox12 = ""
End If
If ComboBox2 = "" Then
MsgBox "vous n'avez pas ajouté votre nom !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If ComboBox1 = "" Then
MsgBox "vous n'avez pas sélectionné votre produit !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If ComboBox3 = "" Then
MsgBox "vous n'avez pas sélectionné votre transporteur !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If TextBox5 = "" Then
MsgBox "vous n'avez pas sélectionné votre destinataire !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If CheckBox1 = False And CheckBox2 = False And CheckBox3 = False And CheckBox4 = False Then
MsgBox "vous n'avez pas coché votre statut !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If CheckBox5 = False And CheckBox6 = False And CheckBox7 = False Then
MsgBox "vous n'avez pas coché la consistance du produit !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If CheckBox8 = False And CheckBox9 = False Then
MsgBox "vous n'avez pas coché la Quantité !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If TextBox4 = "" Then
MsgBox "vous n'avez pas mentionné la Quantité !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If CheckBox10 = False And CheckBox11 = False And CheckBox12 = False _
And CheckBox13 = False And TextBox2 = "" Then
MsgBox "vous n'avez pas complété le conditionnement du produit !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If CheckBox12 = True And TextBox8 = "" Then
MsgBox "vous n'avez pas indiqué le nombre de colis !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
If TextBox2 <> "" And TextBox8 = "" Then
MsgBox "vous n'avez pas indiqué le nombre de colis !!", vbCritical + vbOKOnly, "ATTENTION"
Exit Sub
End If
Sheets("BDS Vierge +annexes").Visible = True
With Sheets("BDS Vierge +annexes")
.Range("E16") = Sheets("Entreprises").Range("D2").Value
.Range("E17") = Sheets("Entreprises").Range("B2").Value
.Range("E18") = Sheets("Entreprises").Range("E2").Value
.Range("E19") = (Sheets("Entreprises").Range("G2").Value) & " " & (Sheets("Entreprises").Range("F2").Value)
.Range("D20") = Sheets("Entreprises").Range("H2").Value
.Range("J20") = Sheets("Entreprises").Range("I2").Value
.Range("G22") = Sheets("Entreprises").Range("J2").Value
If Me.CheckBox1 = True Then Sheets("BDS Vierge +annexes").CheckBox1 = True
If Me.CheckBox2 = True Then Sheets("BDS Vierge +annexes").CheckBox17 = True
If Me.CheckBox3 = True Then Sheets("BDS Vierge +annexes").CheckBox18 = True
If Me.CheckBox4 = True Then Sheets("BDS Vierge +annexes").CheckBox19 = True
If Me.CheckBox5 = True Then Sheets("BDS Vierge +annexes").CheckBox11 = True
If Me.CheckBox6 = True Then Sheets("BDS Vierge +annexes").CheckBox10 = True
If Me.CheckBox7 = True Then Sheets("BDS Vierge +annexes").CheckBox6 = True
If Me.CheckBox8 = True Then Sheets("BDS Vierge +annexes").CheckBox12 = True
If Me.CheckBox9 = True Then Sheets("BDS Vierge +annexes").CheckBox13 = True
If Me.CheckBox10 = True Then Sheets("BDS Vierge +annexes").CheckBox4 = True
If Me.CheckBox11 = True Then Sheets("BDS Vierge +annexes").CheckBox7 = True
If Me.CheckBox12 = True Then Sheets("BDS Vierge +annexes").CheckBox8 = True
If Me.CheckBox13 = True Then Sheets("BDS Vierge +annexes").CheckBox9 = True
If Me.TextBox7 = "Oui" Then Sheets("BDS Vierge +annexes").CheckBox2 = True
If Me.TextBox7 = "Non" Then Sheets("BDS Vierge +annexes").CheckBox3 = True
Sheets("BDS Vierge +annexes").Range("L30") = Format(Me.TextBox4.Value, "##,##")
If Me.TextBox2 <> "" Then Sheets("BDS Vierge +annexes").CheckBox5 = True: Sheets("BDS Vierge +annexes").Range("R29") = TextBox2.Value
Sheets("BDS Vierge +annexes").Range("G24") = Me.TextBox3.Value
Sheets("BDS Vierge +annexes").Range("G7") = Me.TextBox9.Value
Sheets("BDS Vierge +annexes").Range("I7") = Format(Me.TextBox10.Value, "DD/MM")
Sheets("BDS Vierge +annexes").Range("J7") = Format(Me.TextBox11.Value, "000")
Sheets("BDS Vierge +annexes").Range("G25") = Me.ComboBox1.Value
Sheets("BDS Vierge +annexes").Range("E39") = Me.Sir_Trans.Value
Sheets("BDS Vierge +annexes").Range("E40") = Me.ComboBox3.Value
Sheets("BDS Vierge +annexes").Range("E41") = Me.Ad_Trans.Value
Sheets("BDS Vierge +annexes").Range("E42") = Me.CP_Trans.Value & " " & Vil_trans.Value
Sheets("BDS Vierge +annexes").Range("D43") = Format(Me.Tel_Trans.Value, "00 00 00 00 00")
Sheets("BDS Vierge +annexes").Range("I43") = Format(Me.Fax_Trans.Value, "00 00 00 00 00")
Sheets("BDS Vierge +annexes").Range("G45") = Me.Contact_Trans.Value
Sheets("BDS Vierge +annexes").Range("U21") = Me.NumCap_Dest.Value
Sheets("BDS Vierge +annexes").Range("Z22") = Me.DR_Dest.Value
Sheets("BDS Vierge +annexes").Range("R14") = Me.Sir_Dest.Value
Sheets("BDS Vierge +annexes").Range("R15") = Me.TextBox5.Value
Sheets("BDS Vierge +annexes").Range("R16") = Me.Adr_Dest.Value
Sheets("BDS Vierge +annexes").Range("R17") = Me.CP_Dest.Value & " " & Me.Vil_Dest.Value
Sheets("BDS Vierge +annexes").Range("P18") = Format(Me.Tel_Dest.Value, "00 00 00 00 00")
Sheets("BDS Vierge +annexes").Range("V18") = Format(Me.Fax_Dest.Value, "00 00 00 00 00")
Sheets("BDS Vierge +annexes").Range("T19") = Me.Contact_Dest.Value
Sheets("BDS Vierge +annexes").Range("O27") = Me.TextBox6.Value
Sheets("BDS Vierge +annexes").Range("E50") = Me.ComboBox2.Value
Sheets("BDS Vierge +annexes").Range("S50") = Format(Me.TextBox1.Value, "DD/MM/YYYY")
Sheets("BDS Vierge +annexes").Range("Q29") = Me.TextBox2.Value
Sheets("BDS Vierge +annexes").Range("Z29") = Me.TextBox8.Value
End With
If Me.CheckBox1 = True And Me.TextBox7 = "Non" Then
Sheets("BDS Vierge +annexes").Range("Z4") = "1/1"
Sheets("BDS Vierge +annexes").PrintOut From:=1, To:=1, Copies:=1, Collate:=True
End If
If Me.CheckBox1 = True And Me.TextBox7 = "Oui" Then
Sheets("BDS Vierge +annexes").Range("Z4") = "1/2"
Sheets("BDS Vierge +annexes").Range("Z77") = "2/2"
Sheets("BDS Vierge +annexes").PrintOut From:=1, To:=2, Copies:=1, Collate:=True
End If
If Me.CheckBox2 = True And Me.TextBox7 = "Oui" Then
Sheets("BDS Vierge +annexes").Range("Z4") = "1/3"
Sheets("BDS Vierge +annexes").Range("Z77") = "2/3"
Sheets("BDS Vierge +annexes").Range("Z146") = "3/3"
Sheets("BDS Vierge +annexes").PrintOut From:=1, To:=3, Copies:=1, Collate:=True
End If
If Me.CheckBox2 = True And Me.TextBox7 = "Non" Then
Sheets("BDS Vierge +annexes").Range("Z4") = "1/2"
Sheets("BDS Vierge +annexes").Range("Z146") = "2/2"
Sheets("BDS Vierge +annexes").PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Sheets("BDS Vierge +annexes").PrintOut From:=3, To:=3, Copies:=1, Collate:=True
End If
If Me.CheckBox3 = True And Me.TextBox7 = "Non" Then
Sheets("BDS Vierge +annexes").Range("Z4") = "1/2"
Sheets("BDS Vierge +annexes").Range("Z205") = "2/2"
Sheets("BDS Vierge +annexes").PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Sheets("BDS Vierge +annexes").PrintOut From:=4, To:=4, Copies:=1, Collate:=True
End If
If Me.CheckBox3 = True And Me.TextBox7 = "Oui" Then
Sheets("BDS Vierge +annexes").Range("Z4") = "1/3"
Sheets("BDS Vierge +annexes").Range("Z77") = "2/3"
Sheets("BDS Vierge +annexes").Range("Z205") = "3/3"
Sheets("BDS Vierge +annexes").PrintOut From:=1, To:=2, Copies:=1, Collate:=True
Sheets("BDS Vierge +annexes").PrintOut From:=4, To:=4, Copies:=1, Collate:=True
End If
If Me.CheckBox4 = True And Me.TextBox7 = "Non" Then
Sheets("BDS Vierge +annexes").Range("Z4") = "1/1"
Sheets("BDS Vierge +annexes").PrintOut From:=1, To:=1, Copies:=1, Collate:=True
End If
If Me.CheckBox4 = True And Me.TextBox7 = "Oui" Then
Sheets("BDS Vierge +annexes").Range("Z4") = "1/2"
Sheets("BDS Vierge +annexes").Range("Z77") = "2/2"
Sheets("BDS Vierge +annexes").PrintOut From:=1, To:=2, Copies:=1, Collate:=True
End If
'Sheets("BDS Vierge +annexes").Visible = False
derlig = Sheets("CAHIER DE SUIVI").Range("B65536").End(xlUp).Row + 1
Sheets("CAHIER DE SUIVI").Range("B" & derlig) = Format(Me.TextBox1.Value, "MM/DD/YYYY")
Sheets("CAHIER DE SUIVI").Range("C" & derlig) = Me.TextBox9.Value ' & "/" & Format(Me.TextBox10.Value, "DD/MM") & "/" & Format(Me.TextBox11.Value, "000")
Sheets("CAHIER DE SUIVI").Range("D" & derlig) = Format(Me.TextBox10.Value, "MM/DD")
Sheets("CAHIER DE SUIVI").Range("E" & derlig) = Format(Me.TextBox11.Value, "000") & Me.TextBox12.Value
Sheets("CAHIER DE SUIVI").Range("F" & derlig) = Me.TextBox4.Value
Sheets("CAHIER DE SUIVI").Range("G" & derlig) = Me.ComboBox1.Value
Sheets("CAHIER DE SUIVI").Range("H" & derlig) = Me.ComboBox2.Value
Sheets("CAHIER DE SUIVI").Activate
Range("B3:I3").Select
Selection.Copy
Range("B" & derlig).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B4").Select
Range("B4:I" & derlig).Select
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' , _
' DataOption1:=xlSortNormal
derlig = Sheets("Registre").Range("A65536").End(xlUp).Row + 1
Sheets("Registre").Range("A" & derlig) = Me.ComboBox1.Value
Sheets("Registre").Range("B" & derlig) = Me.TextBox3.Value
Sheets("Registre").Range("C" & derlig) = Format(Me.TextBox1.Value, "MM/DD/YYYY")
Sheets("Registre").Range("D" & derlig) = Me.TextBox4.Value
Sheets("Registre").Range("E" & derlig) = Me.TextBox9.Value & "/" & Me.TextBox10.Value & "/" & Format(Me.TextBox11.Value, "000") & Me.TextBox12.Value
Sheets("Registre").Range("G" & derlig) = Me.TextBox5.Value & " " & Me.Sir_Dest.Value & " " & Me.Adr_Dest.Value & " " & Me.CP_Dest.Value & " " & Me.Vil_Dest.Value
Sheets("Registre").Range("H" & derlig) = Me.ComboBox3.Value & " " & Me.Sir_Trans.Value & " " & Me.Ad_Trans.Value & " " & Me.CP_Trans.Value & " " & Me.Vil_trans.Value
Sheets("Registre").Activate
Range("A4:J4").Select
Selection.Copy
Range("A" & derlig).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A5").Select
Range("A5:J" & derlig).Select
Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Key2:=Range("E4") _
, Order2:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ', _
' DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
Unload Me
Application.ScreenUpdating = False
Sheets("bsdi").Activate
End Sub
pour le module:
Sub effaceBSD_Vierge()
Dim n As Integer
Dim cc As OLEObject
With Sheets("BDS Vierge +annexes")
For Each cc In Sheets("BDS Vierge +annexes").OLEObjects
If cc.Name Like "CheckBox*" Then
Sheets("BDS Vierge +annexes").OLEObjects(cc.Name).Object.Value = False
End If
Next
.Range("G7") = ""
.Range("I7:J7") = ""
.Range("E17:E20") = ""
.Range("D21") = ""
.Range("J21") = ""
.Range("G23") = ""
.Range("G25") = ""
.Range("H26") = ""
.Range("L30") = ""
.Range("E39:E42") = ""
.Range("R15:R18") = ""
.Range("D43") = ""
.Range("I43") = ""
.Range("G45") = ""
.Range("U22") = ""
.Range("Z23") = ""
.Range("P19") = ""
.Range("Z29") = ""
.Range("T20") = ""
.Range("O27") = ""
.Range("E50") = ""
.Range("S50") = ""
.Range("Q29") = ""
.Range("Z4") = ""
.Range("Z277") = ""
.Range("Z146") = ""
.Range("Z205") = ""
.Range("V19") = ""
End With
End Sub