repport de combobox

Version imprimable

repport de combobox

Bonjour au forum
J'ai un problème depuis 1 jour que je n'arrive pas à résoudre. Dans un USF, j'ai un combobox pouvant prendre 2 valeurs (Ajout et Suppr). Une fois que la valeur est prise, des textbox sont renvoyées sur une feuille.
Voici mon code:

Code:

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
If ComboBox1.Value = "AJOUT" Then
    Sheets("duo").Select
    Range("c65536").End(xlUp).Offset(0, 0).Select
    ActiveCell.Offset(0, 1).Value = "AJOUT"
    ActiveCell.Offset(0, 2).Value = "EFF"
    ActiveCell.Offset(0, 5).Value = UserForm2.TextBox3.Value
    ActiveCell.Offset(0, 6).Value = UserForm2.TextBox4.Value
    ActiveCell.Offset(0, 7).Value = UserForm2.TextBox5.Value
    ActiveCell.Offset(0, 8).Value = UserForm2.TextBox10.Value
    With ActiveCell.Offset(0, 1)
        .Borders.Weight = xlMedium
    End With
    With ActiveCell.Offset(0, 2)
        .Borders.Weight = xlMedium
    End With
    End If
    If ActiveCell.Offset(0, 6).Value = "6b" Or ActiveCell.Offset(0, 6).Value = "6a" Or ActiveCell.Offset(0, 6).Value = "5c" _
    Or ActiveCell.Offset(0, 6).Value = "5b" Or ActiveCell.Offset(0, 6).Value = "5a" Or ActiveCell.Offset(0, 6).Value = "4" Then
    ActiveCell.Offset(0, 14).Value = "1"
    With ActiveCell.Offset(0, 14)
        .Borders.Weight = xlMedium
    End With
    End If

If ComboBox1.Value = "SUPPR" Then
    Sheets("duo").Select
    Range("c65536").End(xlUp).Offset(0, 0).Select
    ActiveCell.Offset(0, 1).Value = "SUPPR"
    ActiveCell.Offset(0, 2).Value = "EFF"
    ActiveCell.Offset(0, 5).Value = UserForm2.TextBox3.Value
    ActiveCell.Offset(0, 6).Value = UserForm2.TextBox4.Value
    ActiveCell.Offset(0, 7).Value = UserForm2.TextBox5.Value
    ActiveCell.Offset(0, 8).Value = UserForm2.TextBox10.Value
    With ActiveCell.Offset(0, 1)
        .Borders.Weight = xlMedium
    End With
    With ActiveCell.Offset(0, 2)
        .Borders.Weight = xlMedium
    End With
    End If
    If ActiveCell.Offset(0, 6).Value = "6b" Or ActiveCell.Offset(0, 6).Value = "6a" Or ActiveCell.Offset(0, 6).Value = "5c" _
    Or ActiveCell.Offset(0, 6).Value = "5b" Or ActiveCell.Offset(0, 6).Value = "5a" Or ActiveCell.Offset(0, 6).Value = "4" Then
    ActiveCell.Offset(0, 14).Value = "0"
    With ActiveCell.Offset(0, 14)
        .Borders.Weight = xlMedium
    End With
    End If

Or quelque soit la valeur de ma combobox, j'ai toujours la valeur 0
Je ne sais pas où est le problème
Merci de m'aider

Bonjour,

essaye comme sa, je n'ai pas tester si ton code marche, mais n'est il pas plus simple de tout intégrer dans un If ?

tout d'abord savoir si la valeur est égal a AJOUT, et sinon égale a SUPPR

Code:

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
If ComboBox1.Value = "AJOUT" Then
    Sheets("duo").Select
    Range("c65536").End(xlUp).Offset(0, 0).Select
    ActiveCell.Offset(0, 1).Value = "AJOUT"
    ActiveCell.Offset(0, 2).Value = "EFF"
    ActiveCell.Offset(0, 5).Value = UserForm2.TextBox3.Value
    ActiveCell.Offset(0, 6).Value = UserForm2.TextBox4.Value
    ActiveCell.Offset(0, 7).Value = UserForm2.TextBox5.Value
    ActiveCell.Offset(0, 8).Value = UserForm2.TextBox10.Value
    With ActiveCell.Offset(0, 1)
        .Borders.Weight = xlMedium
    End With
    With ActiveCell.Offset(0, 2)
        .Borders.Weight = xlMedium
    End With
    If ActiveCell.Offset(0, 6).Value = "6b" Or ActiveCell.Offset(0, 6).Value = "6a" Or ActiveCell.Offset(0, 6).Value = "5c" _
    Or ActiveCell.Offset(0, 6).Value = "5b" Or ActiveCell.Offset(0, 6).Value = "5a" Or ActiveCell.Offset(0, 6).Value = "4" Then
    ActiveCell.Offset(0, 14).Value = "1"
    With ActiveCell.Offset(0, 14)
        .Borders.Weight = xlMedium
    End With

Else

     If ComboBox1.Value = "SUPPR" Then
        Sheets("duo").Select
        Range("c65536").End(xlUp).Offset(0, 0).Select
       ActiveCell.Offset(0, 1).Value = "SUPPR"
       ActiveCell.Offset(0, 2).Value = "EFF"
       ActiveCell.Offset(0, 5).Value = UserForm2.TextBox3.Value
       ActiveCell.Offset(0, 6).Value = UserForm2.TextBox4.Value
       ActiveCell.Offset(0, 7).Value = UserForm2.TextBox5.Value
       ActiveCell.Offset(0, 8).Value = UserForm2.TextBox10.Value
       With ActiveCell.Offset(0, 1)
        .Borders.Weight = xlMedium
    End With
    With ActiveCell.Offset(0, 2)
        .Borders.Weight = xlMedium
    End With
    If ActiveCell.Offset(0, 6).Value = "6b" Or ActiveCell.Offset(0, 6).Value = "6a" Or ActiveCell.Offset(0, 6).Value = "5c" _
    Or ActiveCell.Offset(0, 6).Value = "5b" Or ActiveCell.Offset(0, 6).Value = "5a" Or ActiveCell.Offset(0, 6).Value = "4" Then
    ActiveCell.Offset(0, 14).Value = "0"
    With ActiveCell.Offset(0, 14)
        .Borders.Weight = xlMedium
    End With
    End If
End If

Bonjour,

Ton code comporte pas mal de redondance
Ci dessous le code modifié a tester

Code:

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
 
 
Sheets("duo").Select
Range("c65536").End(xlUp).Offset(0, 0).Select
ActiveCell.Offset(0, 1).Value = ComboBox1.Value
ActiveCell.Offset(0, 2).Value = "EFF"
ActiveCell.Offset(0, 5).Value = UserForm2.TextBox3.Value
ActiveCell.Offset(0, 6).Value = UserForm2.TextBox4.Value
ActiveCell.Offset(0, 7).Value = UserForm2.TextBox5.Value
ActiveCell.Offset(0, 8).Value = UserForm2.TextBox10.Value
With ActiveCell.Offset(0, 1)
    .Borders.Weight = xlMedium
End With
With ActiveCell.Offset(0, 2)
    .Borders.Weight = xlMedium
End With
 
If ActiveCell.Offset(0, 6).Value = "6b" Or ActiveCell.Offset(0, 6).Value = "6a" Or ActiveCell.Offset(0, 6).Value = "5c" _
    Or ActiveCell.Offset(0, 6).Value = "5b" Or ActiveCell.Offset(0, 6).Value = "5a" Or ActiveCell.Offset(0, 6).Value = "4" Then
 
        Select Case ComboBox1.Value
 
            Case "AJOUT"
                ActiveCell.Offset(0, 14).Value = "1"
            Case "SUPPR"
                ActiveCell.Offset(0, 14).Value = "0"
 
        End Select
 
End If
 
With ActiveCell.Offset(0, 14)
    .Borders.Weight = xlMedium
End With

bonjour patrick60 Maiden002 jfontaine le forum
je verrais bien comme cela pour simplifier a condition de changer le nom de la textbox 10 par textbox 6 vu que je fais une boucle!!
Code:

1 2 3 4 5 6 7 8 9 10 Dim i As Byte, x As Long With Sheets("duo") x = .Range("c65536").End(xlUp).Row .Cells(x, 4) = ComboBox1: .Cells(x, 5) = "EFF" For i = 3 To 6 .Cells(x, i + 5).Value = Controls("Textbox" & i).Value Next i If (.Cells(x, 9) = "6b" Or .Cells(x, 9) = "6a" Or .Cells(x, 9) = "5c" Or .Cells(x, 9) = "5b" Or .Cells(x, 9) = "5a" Or _ .Cells(x, 9) = "6a" Or .Cells(x, 9) = 4) And ComboBox1 = "AJOUT" Then .Cells(x, 17) = 1 Else .Cells(x, 17) = 0 End With
aprés reflexion tu ecris toujours sur la méme ligne la derniere non vide en partant de la colonne C

Code:

Range("c65536").End(xlUp).Offset(0, 0).Select

normal pas normal????????????????
quel est l'intêret de redéfinir Borders.Weigh a chaque demande
Code:

1 2 3 With ActiveCell.Offset(0, 2) .Borders.Weight = xlMedium End With
je comprends pas bien ta démarche en régle general on definis des valeurs dans un user mais on en recopie a la suite donc

Code:

Range("c65536").End(xlUp).Offset(0, 1).Select

ou

Code:

Range("c65536").End(xlUp) (2)

15/10/2009, 18h01
patrick60

bonsoir au forum et surtout milles excuses pour le retard.
La solution de jfontaine me va très bien.
Encore merci à tout le monde