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
| Private Sub Workbook_Open()
'cocher la référence Microsoft DAO 3.n Object Library si fichier .mdb
'si fichier .accdb cocher la référence Microsoft Office nn.0 Access database engine Object Library
Dim db As Database, rs As Recordset, txt1 As String, txt2 As String
'modifier les deux lignes suivantes
Set db = OpenDatabase("C:\Users\Daniel\Documents\Donnees\Daniel\mpfe\colapsus.accdb")
Set rs = db.OpenRecordset("Table1", dbOpenTable)
'Set rs = DB.OpenRecordset("SELECT * FROM " & _
TableName & " WHERE " & FieldName & _
" = 'MyCriteria'", dbReadOnly) ' filter records
i = 0
With rs
While Not .EOF
txt1 = txt1 & "," & .Fields(11)
txt2 = txt2 & "," & .Fields(2)
.MoveNext
Wend
End With
Set rs = Nothing
db.Close
Set db = Nothing
txt1 = Right(txt1, Len(txt1) - 1)
txt2 = Right(txt2, Len(txt2) - 1)
With [Feuil1!G1].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=txt1
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
With [Feuil1!E1].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=txt2
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub |
Partager