djevandro76
XLDnaute Nouveau
Bonjour,
Dans le cadre de la création d'une application destinée a la gestion du planning de quart de plusieurs equipes,j'ai realiser une userform comportant un spreadsheet.Ce spreadsheets permet a l'utilisateur de rentrer les modification qu'il souhaite apporter a son planning.
Exemple de modifications :
CA : congé
A : remplacement d'apres midi
M : remplacement du matin
N : remplacement de nuit
...
ces modifications sont rentrer dans une base de données , je les recupere donc avec l'appel de plusieurs requetes.
En bas du Spreadsheet, ce trouve un petit tableau qui represente le nombre de personne présentes le matin, l'apres midi, et la nuit.
Mon souhait est donc que ce tableau se modifie en fonction des modifications que l'utilisateur effectues , c'est a dire que lorsque la personne se met en repos lorsqu'elle est du matin le nombre de personne diminue de 1, lorsqu'elle se met de l'apres midi alors qu'elle est dui matin le nombre de personne du matin diminue de 1 et que le nombre de personne de l'apres midi augmente ...
Mon probleme est que je dois m'enbrouiller un peu dans mes boucles...!!
Si sur une meme journée du matin une personne A prend sa journée et qu'une personne deux remplace d'apres midi, le decompte se fait comme ci les deux prennaient leur apres midi.......
pour etre un peu plus clair
voici mon code avec ci joint une image de mon spreadsheet :
Public Sub ControleNbPersonne()
Dim mois, annee, tranche, quart As String
Dim jour As Integer
Dim qDef As QueryDef
Dim i, j, k As Integer
mois = UserForm1.ComboBox4.Text
annee = UserForm1.ComboBox7.Text
tranche = UserForm1.ComboBox5.Text
Set qDef = db.QueryDefs("jourModif/tranche")
With qDef
.Parameters("mois") = mois
.Parameters("annee") = annee
.Parameters("tranche") = tranche
End With
Set curseur = qDef.OpenRecordset
i = 0
Do While i <> curseur.RecordCount
jour = curseur.Fields(0)
Set qDef = db.QueryDefs("Modif/jour")
With qDef
.Parameters("mois") = mois
.Parameters("annee") = annee
.Parameters("tranche") = tranche
.Parameters("jour") = jour
End With
Set curseur2 = qDef.OpenRecordset
j = 0
Do While j <> curseur2.RecordCount
modif = curseur2.Fields(0)
Set qDef = db.QueryDefs("Quart/modif")
With qDef
.Parameters("mois") = mois
.Parameters("annee") = annee
.Parameters("tranche") = tranche
.Parameters("jour") = jour
.Parameters("modif") = modif
End With
Set curseur3 = qDef.OpenRecordset
k = 0
Do While k <> curseur3.RecordCount
quart = curseur3.Fields(0)
If quart = "M" Then
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value
Else
If modif <> "" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
End If
End If
End If
End If
End If
If quart = "A" Then
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value
Else
If modif <> "" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
End If
End If
End If
End If
End If
If quart = "N" Then
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value
Else
If modif <> "" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
End If
End If
End If
End If
End If
If quart = "J" Then
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
Else
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
End If
End If
End If
End If
If quart = "R" Or quart = "H" Then
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
Else
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
End If
End If
End If
End If
If curseur3.EOF Then
curseur3.MoveNext
End If
k = k + 1
Loop
If Not curseur2.EOF Then
curseur2.MoveNext
End If
j = j + 1
Loop
If Not curseur.EOF Then
curseur.MoveNext
End If
i = i + 1
Loop
End Sub
merci d'avance pour vos reponses
Dans le cadre de la création d'une application destinée a la gestion du planning de quart de plusieurs equipes,j'ai realiser une userform comportant un spreadsheet.Ce spreadsheets permet a l'utilisateur de rentrer les modification qu'il souhaite apporter a son planning.
Exemple de modifications :
CA : congé
A : remplacement d'apres midi
M : remplacement du matin
N : remplacement de nuit
...
ces modifications sont rentrer dans une base de données , je les recupere donc avec l'appel de plusieurs requetes.
En bas du Spreadsheet, ce trouve un petit tableau qui represente le nombre de personne présentes le matin, l'apres midi, et la nuit.
Mon souhait est donc que ce tableau se modifie en fonction des modifications que l'utilisateur effectues , c'est a dire que lorsque la personne se met en repos lorsqu'elle est du matin le nombre de personne diminue de 1, lorsqu'elle se met de l'apres midi alors qu'elle est dui matin le nombre de personne du matin diminue de 1 et que le nombre de personne de l'apres midi augmente ...
Mon probleme est que je dois m'enbrouiller un peu dans mes boucles...!!
Si sur une meme journée du matin une personne A prend sa journée et qu'une personne deux remplace d'apres midi, le decompte se fait comme ci les deux prennaient leur apres midi.......
pour etre un peu plus clair
voici mon code avec ci joint une image de mon spreadsheet :
Public Sub ControleNbPersonne()
Dim mois, annee, tranche, quart As String
Dim jour As Integer
Dim qDef As QueryDef
Dim i, j, k As Integer
mois = UserForm1.ComboBox4.Text
annee = UserForm1.ComboBox7.Text
tranche = UserForm1.ComboBox5.Text
Set qDef = db.QueryDefs("jourModif/tranche")
With qDef
.Parameters("mois") = mois
.Parameters("annee") = annee
.Parameters("tranche") = tranche
End With
Set curseur = qDef.OpenRecordset
i = 0
Do While i <> curseur.RecordCount
jour = curseur.Fields(0)
Set qDef = db.QueryDefs("Modif/jour")
With qDef
.Parameters("mois") = mois
.Parameters("annee") = annee
.Parameters("tranche") = tranche
.Parameters("jour") = jour
End With
Set curseur2 = qDef.OpenRecordset
j = 0
Do While j <> curseur2.RecordCount
modif = curseur2.Fields(0)
Set qDef = db.QueryDefs("Quart/modif")
With qDef
.Parameters("mois") = mois
.Parameters("annee") = annee
.Parameters("tranche") = tranche
.Parameters("jour") = jour
.Parameters("modif") = modif
End With
Set curseur3 = qDef.OpenRecordset
k = 0
Do While k <> curseur3.RecordCount
quart = curseur3.Fields(0)
If quart = "M" Then
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value
Else
If modif <> "" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
End If
End If
End If
End If
End If
If quart = "A" Then
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value
Else
If modif <> "" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
End If
End If
End If
End If
End If
If quart = "N" Then
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value
Else
If modif <> "" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value - 1
End If
End If
End If
End If
End If
If quart = "J" Then
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
Else
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
End If
End If
End If
End If
If quart = "R" Or quart = "H" Then
If modif = "A" Then
UserForm1.Spreadsheet1.Cells(9, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
Else
If modif = "M" Then
UserForm1.Spreadsheet1.Cells(7, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
Else
If modif = "N" Then
UserForm1.Spreadsheet1.Cells(11, jour).Select
UserForm1.Spreadsheet1.Selection.Value = UserForm1.Spreadsheet1.Selection.Value + 1
End If
End If
End If
End If
If curseur3.EOF Then
curseur3.MoveNext
End If
k = k + 1
Loop
If Not curseur2.EOF Then
curseur2.MoveNext
End If
j = j + 1
Loop
If Not curseur.EOF Then
curseur.MoveNext
End If
i = i + 1
Loop
End Sub
merci d'avance pour vos reponses
