[Résolu]Format de données dans une étiquette

S

Sanegone

XLDnaute Nouveau
Bonjour,

Je tente d'insérer en VBA le contenant ce certaines cellules dans une étiquette. Ces cellules ont leur propre format personnalisé cependant une fois dans les étiquettes les données sont au format scientifique, quelqu’un sait palier à ça ?

Cordialement
 
Dernière édition:
Trier par date Trier par votes
S

Sanegone

XLDnaute Nouveau
Re : Format de données dans une étiquette

Voici le code, malheureusement je peux pas fournir le fichier source qui contient des données sensibles sur certains clients. ( numéro cb, solde de comtpe etc )

Mon problème se situe dans l'étiquette lorsque :

Cells(i, 3).Value = "1"

Cette cellule est au format "0000000000000000" cependant elle repasse en format scientifique dans l'étiquette sans que la cellule ne soit affectée.


Code: 
Private Sub CommandButton1_Click()
VAR_NBR_AUTOMATE = Range("B65536").End(xlUp).Row
ActiveSheet.Hyperlinks.Delete
ActiveSheet.Pictures.Delete

    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    
'//*******************************************************************************************

For i1 = 1 To 280
    For i2 = 1 To 195
        If Cells(i1, i2).Value = "num_gab" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "00000000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 195
        If Cells(i1, i2).Value = "type_action_explt" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "00"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 195
        If Cells(i1, i2).Value = "CODE REPONSE" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 195
        If Cells(i1, i2).Value = "etat_ressource_actuel" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "000000000000000000000000000000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 195
        If Cells(i1, i2).Value = "etat_ressource_prec" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "000000000000000000000000000000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "IDENTIFIANT DU TERMINAL" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "00000000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "num_carte" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "0000000000000000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "NUMERO CARTE" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "0000000000000000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "identifiant_accepteur" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "0000000000000000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "IDENTIFIANT ACCEPTEUR" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "0000000000000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "IDENT UNIQUE TRANSACTION" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
                Selection.NumberFormat = "00000000000000000"
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "valeur_coupure" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "EMV AMOUNT ARQC" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "MONTANT LOCALE" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "enc_global" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "encaisse_ap_forcage" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "encaisse_av_forcage" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "encaisse" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "encaisse_dispo" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "encaisse_theo" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "montant" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "montant_dispo_k7" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "montant_dist" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1



For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "montant_auto" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = ActiveCell.Value / 100
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "date_ope" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Right(VAR_TRAITEMENT, 2) & "/" & Mid(VAR_TRAITEMENT, 3, 2) & "/" & Left(VAR_TRAITEMENT, 2)
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "date_gmt_transac" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Right(VAR_TRAITEMENT, 2) & "/" & Mid(VAR_TRAITEMENT, 3, 2) & "/" & Left(VAR_TRAITEMENT, 2)
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "date_transac" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Right(VAR_TRAITEMENT, 2) & "/" & Mid(VAR_TRAITEMENT, 3, 2) & "/" & Left(VAR_TRAITEMENT, 2)
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "date_transac" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Right(VAR_TRAITEMENT, 2) & "/" & Mid(VAR_TRAITEMENT, 3, 2) & "/" & Left(VAR_TRAITEMENT, 2)
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "heure_ope" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Left(VAR_TRAITEMENT, 2) & " : " & Mid(VAR_TRAITEMENT, 3, 2) & " : " & Right(VAR_TRAITEMENT, 2)
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "heure_gmt_transac" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Left(VAR_TRAITEMENT, 2) & " : " & Mid(VAR_TRAITEMENT, 3, 2) & " : " & Right(VAR_TRAITEMENT, 2)
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "heure_trt_demande" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Left(VAR_TRAITEMENT, 2) & " : " & Mid(VAR_TRAITEMENT, 3, 2) & " : " & Right(VAR_TRAITEMENT, 2)
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "date_trt_demande" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Right(VAR_TRAITEMENT, 2) & " / " & Mid(VAR_TRAITEMENT, 5, 2) & " / " & Left(VAR_TRAITEMENT, 4)
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "heure_transac" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Left(VAR_TRAITEMENT, 2) & " : " & Mid(VAR_TRAITEMENT, 3, 2) & " : " & Right(VAR_TRAITEMENT, 2)
        End If
    Next i2
Next i1

For i1 = 1 To 280
    For i2 = 1 To 255
        If Cells(i1, i2).Value = "date_heure_serv" Then
            Cells(i1, i2).Select
            VAR_ADRESS = ActiveCell.Address
            ActiveCell.Offset(1, 0).Select
            VAR_TRAITEMENT = ActiveCell.Value
            ActiveCell.Value = Mid(VAR_TRAITEMENT, 7, 2) & " / " & Mid(VAR_TRAITEMENT, 5, 2) & " / " & Left(VAR_TRAITEMENT, 2) & "   " & Mid(VAR_TRAITEMENT, 9, 2) & " : " & Mid(VAR_TRAITEMENT, 11, 2) & " : " & Right(VAR_TRAITEMENT, 2)
        End If
    Next i2
Next i1
End Sub
  

Private Sub CommandButton2_Click()
VAR_NBR_AUTOMATE = Range("B65536").End(xlUp).Row
For i = VAR_NBR_AUTOMATE To 1 Step -1
    If Cells(i, 3).Value = "0" Then
        Cells(i, 1).Value = "Entête de fichier"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "1" Then
        Cells(i, 1).Value = "Retrait"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
        
        Cells(i, 3).Select
        ActiveCell.AddComment
        ActiveCell.Comment.Visible = False
        ActiveCell.Comment.Text Text:="Opération : " & Cells(i, 1).Value & Chr(10) & Chr(10) & "Date / Heure : " & Cells(i, 2).Value & Chr(10) & Chr(10) & "Numéro de carte : " & Cells(i, 8).Value & Chr(10) & Chr(10) & "Code service : " & Cells(i, 9).Value & Chr(10) & Chr(10) & "Numéro d'enregistrement : " & Cells(i, 16).Value & Chr(10) & Chr(10) & "Statut GAB :  " & Cells(i, 17).Value & Chr(10) & Chr(10) & "Retour Logigab : " & Cells(i, 21).Value & Chr(10) & Chr(10) & "Code Réponse  : " & Cells(i, 23).Value & Chr(10) & Chr(10) & "Montant autoristation : " & Cells(i, 25).Value & Chr(10) & Chr(10) & "Dénomination : " & Cells(i, 53).Value & "   Nombre : " & Cells(i, 54).Value & Chr(10) & Chr(10) & "Dénomination : " & Cells(i, 55).Value & "   Nombre : " & Cells(i, 56).Value & Chr(10) & Chr(10) & "Dénomination : " & Cells(i, 57).Value & "   Nombre : " & Cells(i, 58).Value & Chr(10) & Chr(10) & "Dénomination : " & Cells(i, 59).Value & "   Nombre : " & Cells(i, 60).Value & Chr(10) & Chr(10) _
        & "Encaisse théorique : " & Cells(i, 61).Value & Chr(10) & Chr(10) & "Encaisse disponible : " & Cells(i, 62).Value & Chr(10) & Chr(10)
        Selection.Comment.Shape.ScaleWidth 1.87, msoFalse, msoScaleFromTopLeft
        Selection.Comment.Shape.ScaleHeight 1.59, msoFalse, msoScaleFromTopLeft
        
    End If
    
    If Cells(i, 3).Value = "2" Then
        Cells(i, 1).Value = "Consultation de solde"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "3" Then
        Cells(i, 1).Value = "Virement"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "4" Then
        Cells(i, 1).Value = "Dépôt de chèques"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "5" Then
        Cells(i, 1).Value = "Dépôt d 'espèces"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "6" Then
        Cells(i, 1).Value = "Dépôt mixte"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "7" Then
        Cells(i, 1).Value = "Historique de compte"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "8" Then
        Cells(i, 1).Value = "Commande de chéquier"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "9" Then
        Cells(i, 1).Value = "Demande de rdv"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "10" Then
        Cells(i, 1).Value = "Extrait de compte"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "11" Then
        Cells(i, 1).Value = "Demande de rib"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "12" Then
        Cells(i, 1).Value = "Fin de transaction"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "13" Then
        Cells(i, 1).Value = "Débit Différé"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "16" Then
        Cells(i, 1).Value = "Unitaire"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "18" Then
        Cells(i, 1).Value = "Fin de consultation"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "19" Then
        Cells(i, 1).Value = "Carte temporisée"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "20" Then
        Cells(i, 1).Value = "Evénement/action Gab"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "21" Then
        Cells(i, 1).Value = "Interrogation des Eléments de Décision"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "22" Then
        Cells(i, 1).Value = "Demande de carte de dépannage"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "23" Then
        Cells(i, 1).Value = "Activation carte"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "25" Then
        Cells(i, 1).Value = "Capture de carte"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "26" Then
        Cells(i, 1).Value = "Demande d 'autorisation"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "27" Then
        Cells(i, 1).Value = "Gestion des filtres de fraude paramétrables"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "29" Then
        Cells(i, 1).Value = "Message d 'Etat"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "30" Then
        Cells(i, 1).Value = "CR émis par le serveur monétique"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "31" Then
        Cells(i, 1).Value = "Connexion RHM"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "32" Then
        Cells(i, 1).Value = "Déconnexion RHM"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "40" Then
        Cells(i, 1).Value = "Arrêt comptable GAB"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "41" Then
        Cells(i, 1).Value = "Arrêt comptable du serveur monétique"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "42" Then
        Cells(i, 1).Value = "Arrêté de dépôt d'argent ou arrêté de coffre"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "50" Then
        Cells(i, 1).Value = "Incident GAB"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "60" Then
        Cells(i, 1).Value = "Autorisation de retrait"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "61" Then
        Cells(i, 1).Value = "Autorisation de virement"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "62" Then
        Cells(i, 1).Value = "Autorisation de dépôt"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "63" Then
        Cells(i, 1).Value = "Autorisation de paiement"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "64" Then
        Cells(i, 1).Value = "Avis de capture"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "65" Then
        Cells(i, 1).Value = "Autorisation de paiement sur GAB"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "67" Then
        Cells(i, 1).Value = "Autorisation de paiement CVD"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "70" Then
        Cells(i, 1).Value = "Redressement retrait"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "71" Then
        Cells(i, 1).Value = "Redressement Paiement"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "73" Then
        Cells(i, 1).Value = "Redressement Paiement CVD"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "75" Then
        Cells(i, 1).Value = "Redressement de Paiement sur GAB"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "81" Then
        Cells(i, 1).Value = "Forcage d 'encaisse"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "82" Then
        Cells(i, 1).Value = "Ajout exploitant"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
        
    End If
    
    If Cells(i, 3).Value = "83" Then
        Cells(i, 1).Value = "Retrait exploitant"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "84" Then
        Cells(i, 1).Value = "Contrôle de solde"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "85" Then
        Cells(i, 1).Value = "Chargement de caisse"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "86" Then
        Cells(i, 1).Value = "Dechargement de caisse"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "88" Then
        Cells(i, 1).Value = "Exception porteur"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "89" Then
        Cells(i, 1).Value = "Plafond exceptionnel"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "90" Then
        Cells(i, 1).Value = "Mise/Levée opposition"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "93" Then
        Cells(i, 1).Value = "Mise/Levée surveillance"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "95" Then
        Cells(i, 1).Value = "Paiement"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "96" Then
        Cells(i, 1).Value = "Fin de remise"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "98" Then
        Cells(i, 1).Value = "Transaction en timeout"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "99" Then
        Cells(i, 1).Value = "Fin de fichier"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If

    If Cells(i, 3).Value = "1A" Then
        Cells(i, 1).Value = "Exploitation et incident saisi par l'exploitant"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2

        Cells(i, 3).Select
        ActiveCell.AddComment
        ActiveCell.Comment.Visible = False
        ActiveCell.Comment.Text Text:="SAB :" & Chr(10) & Cells(i, 1).Value & Chr(10) & Chr(10) & Cells(i, 2).Value & Chr(10) & Chr(10) & Cells(i, 4).Value
        Selection.Comment.Shape.ScaleWidth 1.87, msoFalse, msoScaleFromTopLeft
        Selection.Comment.Shape.ScaleHeight 1.59, msoFalse, msoScaleFromTopLeft

    End If
    
    If Cells(i, 3).Value = "1B" Then
        Cells(i, 1).Value = "Exploitation Télécommandée"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "1C" Then
        Cells(i, 1).Value = "Message U"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
    
    If Cells(i, 3).Value = "3A" Then
        Cells(i, 1).Value = "Changement de statut d'un GAB"
        Cells(i, 1).Select
        Selection.Font.ColorIndex = 1
        Selection.Interior.ColorIndex = 2
        Cells(i, 3).Select
        Selection.Interior.ColorIndex = 2
    End If
Next i

End Sub
 
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Format de données dans une étiquette

Re,

pas besoin du fichier original, simplement un tout petit fichier représentant le problème posé.... A tout hasard, plutôt que la propriété "value" utilise la propriété "text" :
Code: 
Cells(i, 3).Text= "1"
 
Vote positif 0 Vote négatif
S

Sanegone

XLDnaute Nouveau
Re : Format de données dans une étiquette

Je viens de tester ça n'as malheureusement pas l'air de fonctionner.

J'ai créer un fichier tronqué en pièce jointe ;) Merci pour ton aide

Le problème reste le même mais les données sensible n'y sont plus.
 

Pièces jointes

  • fichiertronk.xls
    71.5 KB · Affichages: 69
Vote positif 0 Vote négatif
S

Sanegone

XLDnaute Nouveau
Re : Format de données dans une étiquette

Je me permet un petit up personne n'as une idée ?

EDIT :

Solution trouvée :

Si l'on met Cells(i, 8).Value dans l'étiquette = Affichage scientifique
Si l'on met Cells(i, 8).Text dans l'étiquette = Affichage scientifique
Si l'on met Cells(i, 8).Value en Variable et qu'on affiche dans l'étiquette la variable = Affichage scientifique
Si l'on met Cells(i, 8).Text en Variable et qu'on l'affiche dans l'étiquette on as le bon format :)

Après me demandez pas pourquoi :p
 
Dernière édition:
Vote positif 0 Vote négatif

Discussions similaires

L
  • Question
XL 2016 Création d'une étiquette CB avec Excel
Réponses
5
Affichages
204
lincoln75
L
M
  • Question
Microsoft 365 Erreur de format
Réponses
5
Affichages
133
Marvin57
M
C
  • Résolu(e)
XL 2013 importer des données sans modifier le format des cellules distinataires
Réponses
6
Affichages
270
chindou
C
G
  • Question
Microsoft 365 Choix et export de données (Résolu)
Réponses
4
Affichages
333
GClaire
G
L
  • Résolu(e)
Microsoft 365 Tri et Import
Réponses
4
Affichages
183
Le Rouky
L
M
  • Résolu(e)
XL 2019 [Résolu] VBA test sur une cellule en format [hh]:mm
Réponses
4
Affichages
295
miloo
M
V
  • Question
XL 2016 Convertir un format de date
Réponses
3
Affichages
454
vivi21
V
A
  • Question
XL 2019 format de date dans un résultat de concaténation
Réponses
8
Affichages
345
Akafal
A
A
  • Question
Microsoft 365 Personnaliser une étiquette de données de Waterfall (Value from cells)
Réponses
5
Affichages
634
Efgé
Efgé
N
  • Question
Microsoft 365 Mise à jour de données d'un fichier Excel à un autre, sous Sharepoint
Réponses
0
Affichages
128
Newbeee
N

Membres actuellement en ligne

Aucun membre en ligne actuellement.
0 membres et 23 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 294
Messages
2 086 895
Membres
103 404
dernier inscrit
sultan87