Problème avec UserForm avec liste déroulante

Résolu
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

1 réponse

  1.
    f894009 Messages postés 17417 Date d'inscription   Statut Membre Dernière intervention   1 717
     
    Bonjour,

    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
    0
    1. laura54960 Messages postés 17 Statut Membre
       
      Bonjour,

      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 !
      0

Discussions similaires

[EXEL] Comment créer une liste déroulante?
TORTULE -
Hermes -

3 réponses