Problème avec UserForm avec liste déroulante
Résolu
laura54960
Messages postés
17
Statut
Membre
-
laura54960 Messages postés 17 Statut Membre -
laura54960 Messages postés 17 Statut Membre -
Bonjour à tous,
Je suis en train de mettre en place un programme afin d'automatiser un calcul avec plusieurs conditions. Je suis déjà venue vers vous plusieurs fois d'ailleurs et je fais encore appelle à votre aide.
J'ai mis en place un UserForm qui est lancé par un Module "Calcul" si une condition est respectée. Le formulaire se lance : une liste déroulante apparaît, je sélectionne ma réponse et je clique sur "OK". J'ai ensuite voulu initialiser un Worksheet afin d'afficher ma réponse, mais rien ne se passe, et je passe à la suite du Code. Voici mes écritures :
Module Calcul :
Sub Calcul()
Dim hanc As String
Dim hnouv As String
Dim majo As Single
Dim i As Integer
Dim RH As String
Dim R As String
Dim G As String
Dim A As String
Dim M As String
Dim N As String
Dim JRTT As String
Dim DEMIJRTT As String
Dim datechg As Date
Dim datedeb As Date
Dim datefin As Date
Dim date1 As Date
i = 21
Do While date1 - 1 < datefin
hanc = Worksheets("Bilan").Cells(i, 6).Value
hnouv = Worksheets("Bilan").Cells(i, 9).Value
datechg = Worksheets("Bilan").Cells(7, 7).Value
datedeb = Worksheets("Bilan").Cells(13, 7).Value
datefin = Worksheets("Bilan").Cells(15, 7).Value
date1 = Worksheets("Bilan").Cells(i, 4).Value
If hnouv = "RH" Or hnouv = "G" Or hnouv = "R" Or hnouv = "JRTT" Then
majo = 0
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "J" And hanc = "RH" Or hnouv = "J" And hanc = "R" Or hnouv = "J" And hanc = "G" Or hnouv = "J" And hanc = "JRTT" Then
majo = 12.25
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "J" And hanc = "DEMIJRTT" Then
majo = 7
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "M" And hanc = "RH" Or hnouv = "M" And hanc = "R" Or hnouv = "M" And hanc = "G" Or hnouv = "M" And hanc = "JRTT" Then
majo = 14.25
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "M" And hanc = "DEMIJRTT" Then
majo = 9
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "M" And hanc = "J" Then
majo = 1.67
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "A" And hanc = "RH" Or hnouv = "A" And hanc = "R" Or hnouv = "A" And hanc = "G" Or hnouv = "A" And hanc = "JRTT" Then
majo = 14.88
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "A" And hanc = "DEMIJRTT" Then
majo = 8.75
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "DEMIJRTT" And hanc = "RH" Or hnouv = "DEMIJRTT" And hanc = "R" Or hnouv = "DEMIJRTT" And hanc = "G" Or hnouv = "DEMIJRTT" And hanc = "JRTT" Then
majo = 5.25
Worksheets("Bilan").Cells(i, 18).Value = majo
End If
If hnouv = "N" Then
Userform1.Show
End If
i = i + 1
date1 = date1 + 1
Loop
End Sub
Le formulaire UserForm :
Private Sub Annuler_Click()
Unload Me
End Sub
Private Sub OK_Click()
Dim date1 As Date
Dim hnouv As String
Dim majo As Single
Dim datechang As Date
Dim i As Integer
mavariable = ListBox1.Value
datechang = Worksheets("Bilan").Cells(7, 7).Value
date1 = Worksheets("Bilan").Cells(i, 4).Value
hnouv = Worksheets("Bilan").Cells(i, 9).Value
If hnouv = "N" Then
Label2.Caption = date1
If mavariable = "N sur Repos - Repos" Then
majo = 23.62
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur Repos - Semaine" Then
majo = 20.58
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur Semaine - Repos" Then
majo = 21.96
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur 1/2JRTT - Repos" Then
majo = 16.25
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur 1/2JRTT - Semaine" Then
majo = 14.67
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur J" Then
majo = 5
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur M - A" Then
majo = 3
Worksheets("Bilan").Cells(i, 18).Value = majo
End If
End If
Unload Me
End Sub
Private Sub Userform_Initialize()
i = 21
date1 = Worksheets("Bilan").Cells(i, 4).Value
Label2.Caption = date1
End Sub
C'est un peu confus je vous l'accorde, j'essaye d'apprendre de manière autodidacte mais je pense que je dois confondre et passer à côté de certains principes malgré mes recherches.
Un autre problème, ma variable "i" correspond à la ligne, j'aimerai la déclarer publique pour que mon UserForm utilise la valeur de "i" dans le module "Calcul" mais je n'ai pas eu de résultat satisfaisant.
Je vous remercie d'avance pour vos réponses,
Laura
Je suis en train de mettre en place un programme afin d'automatiser un calcul avec plusieurs conditions. Je suis déjà venue vers vous plusieurs fois d'ailleurs et je fais encore appelle à votre aide.
J'ai mis en place un UserForm qui est lancé par un Module "Calcul" si une condition est respectée. Le formulaire se lance : une liste déroulante apparaît, je sélectionne ma réponse et je clique sur "OK". J'ai ensuite voulu initialiser un Worksheet afin d'afficher ma réponse, mais rien ne se passe, et je passe à la suite du Code. Voici mes écritures :
Module Calcul :
Sub Calcul()
Dim hanc As String
Dim hnouv As String
Dim majo As Single
Dim i As Integer
Dim RH As String
Dim R As String
Dim G As String
Dim A As String
Dim M As String
Dim N As String
Dim JRTT As String
Dim DEMIJRTT As String
Dim datechg As Date
Dim datedeb As Date
Dim datefin As Date
Dim date1 As Date
i = 21
Do While date1 - 1 < datefin
hanc = Worksheets("Bilan").Cells(i, 6).Value
hnouv = Worksheets("Bilan").Cells(i, 9).Value
datechg = Worksheets("Bilan").Cells(7, 7).Value
datedeb = Worksheets("Bilan").Cells(13, 7).Value
datefin = Worksheets("Bilan").Cells(15, 7).Value
date1 = Worksheets("Bilan").Cells(i, 4).Value
If hnouv = "RH" Or hnouv = "G" Or hnouv = "R" Or hnouv = "JRTT" Then
majo = 0
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "J" And hanc = "RH" Or hnouv = "J" And hanc = "R" Or hnouv = "J" And hanc = "G" Or hnouv = "J" And hanc = "JRTT" Then
majo = 12.25
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "J" And hanc = "DEMIJRTT" Then
majo = 7
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "M" And hanc = "RH" Or hnouv = "M" And hanc = "R" Or hnouv = "M" And hanc = "G" Or hnouv = "M" And hanc = "JRTT" Then
majo = 14.25
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "M" And hanc = "DEMIJRTT" Then
majo = 9
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "M" And hanc = "J" Then
majo = 1.67
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "A" And hanc = "RH" Or hnouv = "A" And hanc = "R" Or hnouv = "A" And hanc = "G" Or hnouv = "A" And hanc = "JRTT" Then
majo = 14.88
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "A" And hanc = "DEMIJRTT" Then
majo = 8.75
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf hnouv = "DEMIJRTT" And hanc = "RH" Or hnouv = "DEMIJRTT" And hanc = "R" Or hnouv = "DEMIJRTT" And hanc = "G" Or hnouv = "DEMIJRTT" And hanc = "JRTT" Then
majo = 5.25
Worksheets("Bilan").Cells(i, 18).Value = majo
End If
If hnouv = "N" Then
Userform1.Show
End If
i = i + 1
date1 = date1 + 1
Loop
End Sub
Le formulaire UserForm :
Private Sub Annuler_Click()
Unload Me
End Sub
Private Sub OK_Click()
Dim date1 As Date
Dim hnouv As String
Dim majo As Single
Dim datechang As Date
Dim i As Integer
mavariable = ListBox1.Value
datechang = Worksheets("Bilan").Cells(7, 7).Value
date1 = Worksheets("Bilan").Cells(i, 4).Value
hnouv = Worksheets("Bilan").Cells(i, 9).Value
If hnouv = "N" Then
Label2.Caption = date1
If mavariable = "N sur Repos - Repos" Then
majo = 23.62
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur Repos - Semaine" Then
majo = 20.58
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur Semaine - Repos" Then
majo = 21.96
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur 1/2JRTT - Repos" Then
majo = 16.25
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur 1/2JRTT - Semaine" Then
majo = 14.67
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur J" Then
majo = 5
Worksheets("Bilan").Cells(i, 18).Value = majo
ElseIf mavariable = "N sur M - A" Then
majo = 3
Worksheets("Bilan").Cells(i, 18).Value = majo
End If
End If
Unload Me
End Sub
Private Sub Userform_Initialize()
i = 21
date1 = Worksheets("Bilan").Cells(i, 4).Value
Label2.Caption = date1
End Sub
C'est un peu confus je vous l'accorde, j'essaye d'apprendre de manière autodidacte mais je pense que je dois confondre et passer à côté de certains principes malgré mes recherches.
Un autre problème, ma variable "i" correspond à la ligne, j'aimerai la déclarer publique pour que mon UserForm utilise la valeur de "i" dans le module "Calcul" mais je n'ai pas eu de résultat satisfaisant.
Je vous remercie d'avance pour vos réponses,
Laura
A voir également:
- Problème avec UserForm avec liste déroulante
- Liste déroulante excel - Guide
- Liste déroulante en cascade - Guide
- Liste code ascii - Guide
- Liste déroulante google sheet - Accueil - Guide bureautique
- Site dangereux liste - Guide
1 réponse
Bonjour,
code un peu optimise, a vous de tester
'dans un module
'userform
code un peu optimise, a vous de tester
'dans un module
Public i As Integer
Sub Calcul()
Dim hanc As String
Dim hnouv As String
Dim datechg As Date
Dim datedeb As Date
Dim datefin As Date
Dim date1 As Date
i = 21
Do While date1 - 1 < datefin
With Worksheets("Bilan")
hanc = .Cells(i, 6).Value
hnouv = .Cells(i, 9).Value
datechg = .Cells(7, 7).Value
datedeb = .Cells(13, 7).Value
datefin = .Cells(15, 7).Value
date1 = .Cells(i, 4).Value
If hnouv = "RH" Or hnouv = "G" Or hnouv = "R" Or hnouv = "JRTT" Then
.Cells(i, 18).Value = 0
ElseIf hnouv = "J" And hanc = "RH" Or hnouv = "J" And hanc = "R" Or hnouv = "J" And hanc = "G" Or hnouv = "J" And hanc = "JRTT" Then
.Cells(i, 18).Value = 12.25
ElseIf hnouv = "J" And hanc = "DEMIJRTT" Then
.Cells(i, 18).Value = 7
ElseIf hnouv = "M" And hanc = "RH" Or hnouv = "M" And hanc = "R" Or hnouv = "M" And hanc = "G" Or hnouv = "M" And hanc = "JRTT" Then
.Cells(i, 18).Value = 14.25
ElseIf hnouv = "M" And hanc = "DEMIJRTT" Then
.Cells(i, 18).Value = 9
ElseIf hnouv = "M" And hanc = "J" Then
.Cells(i, 18).Value = 1.67
ElseIf hnouv = "A" And hanc = "RH" Or hnouv = "A" And hanc = "R" Or hnouv = "A" And hanc = "G" Or hnouv = "A" And hanc = "JRTT" Then
.Cells(i, 18).Value = 14.88
ElseIf hnouv = "A" And hanc = "DEMIJRTT" Then
.Cells(i, 18).Value = 8.75
ElseIf hnouv = "DEMIJRTT" And hanc = "RH" Or hnouv = "DEMIJRTT" And hanc = "R" Or hnouv = "DEMIJRTT" And hanc = "G" Or hnouv = "DEMIJRTT" And hanc = "JRTT" Then
.Cells(i, 18).Value = 5.25
End If
If hnouv = "N" Then
Userform1.Show
End If
End With
i = i + 1
'????????
date1 = date1 + 1
Loop
End Sub
'userform
Private Sub OK_Click()
Dim date1 As Date
Dim hnouv As String
Dim datechang As Date
mavariable = ListBox1.Value
With Worksheets("Bilan")
datechang = .Cells(7, 7).Value
date1 = .Cells(i, 4).Value
hnouv = .Cells(i, 9).Value
If hnouv = "N" Then
Label2.Caption = date1
If mavariable = "N sur Repos - Repos" Then
.Cells(i, 18).Value = 23.62
ElseIf mavariable = "N sur Repos - Semaine" Then
.Cells(i, 18).Value = 20.58
ElseIf mavariable = "N sur Semaine - Repos" Then
.Cells(i, 18).Value = 21.96
ElseIf mavariable = "N sur 1/2JRTT - Repos" Then
.Cells(i, 18).Value = 16.25
ElseIf mavariable = "N sur 1/2JRTT - Semaine" Then
.Cells(i, 18).Value = 14.67
ElseIf mavariable = "N sur J" Then
.Cells(i, 18).Value = 5
ElseIf mavariable = "N sur M - A" Then
.Cells(i, 18).Value = 3
End If
End If
End With
Unload Me
End Sub
Private Sub Userform_Initialize()
date1 = Worksheets("Bilan").Cells(i, 4).Value
Label2.Caption = date1
End Sub
Merci f894009 pour ta réponse. J'ai testé sur mon programme et j'ai simplement du modifier tous les "i" qui apparaissaient, en "calcul.i" pour que la portée soit publique.
Et super, ça fonctionne ! Merci beaucoup !
Je vais continuer à tester les calculs pour vérifier si tout correspond bien.
Encore merci !