Microsoft 365 Vba affichage Numérique TextBox

Option Explicit

Public WithEvents TxtB As MSForms.TextBox

Dim Ws As Worksheet
Dim SelectedIndex As Integer
Dim cls(1 To 15) As New Modifier

Const fmt1 = "#,##0.00;-#,##0.00"
Const fmt2 = "#,##0.00 €;-#,##0.00 €"

Private Sub Quitter_Click()
  Unload Me 'Ferme le Formulaire
  Accueil.Show 0
End Sub

Private Sub TextBox14_change()
  TextBox14 = Format(TextBox14, fmt2)
End Sub

Private Sub TextBox15_Change()
  TextBox15 = Format(TextBox15, fmt2)
End Sub

Private Sub TxtB_Change()
  Dim chn$, mem#, i As Byte
  If Right$(TxtB, 1) = "." Then TxtB = Replace$(TxtB, ".", ",")
  For i = 2 To 13
    With Modifier.Frame1.Controls("TextBox" & i)
      chn = .Value: If IsNumeric(chn) Then mem = mem + CDbl(chn)
    End With
    Frame1.Controls("TextBox" & i) = Format(chn, fmt1)
  Next i
  Modifier.Frame1.TextBox14 = mem
End Sub


Private Sub UserForm_Activate()
  Dim ctrl, MaSomme#, MC%, J&, MPL&, MDL&, a&
  ComboBox2.ColumnCount = 1 'Pour la liste Civilité
  ComboBox2.List() = Array("Civilité", "Mr", "Mme")
  Set Ws = Sheets("Controle") 'Correspond au nom de l'onglet dans le fichier Excel
  With Me.ComboBox1
    For J = 2 To Ws.Range("A" & Rows.Count).End(xlUp).Row
      .AddItem Ws.Range("C" & J) & " - " & Format(Ws.Range("A" & J), "0# ## ## ## ##")
    Next J
  End With
  For Each ctrl In Frame1.Controls
    If TypeName(ctrl) = "TextBox" And ctrl.Name <> "TextBox14" Then
      a = a + 1: Set cls(a).TxtB = ctrl
    End If
  Next ctrl
  Worksheets("Controle").Select
  MC = 16 'Ma Collone
  MPL = 2 'Ma Première Ligne
  MDL = Cells(Rows.Count, "P").End(xlUp).Row - 1 'Ma Dernière Ligne
  MaSomme = 0
  For J = MPL To MDL
    MaSomme = MaSomme + Cells(J, MC)
  Next J
  TextBox15 = MaSomme
End Sub


Private Sub ComboBox1_Change()
  Dim Ligne&, i As Byte
  SelectedIndex = ComboBox1.ListIndex
  If ComboBox1.ListIndex = -1 Then
    ComboBox2.ListIndex = -1
    For i = 1 To 14
      Controls("TextBox" & i) = ""
    Next i
  Else
    Ligne = ComboBox1.ListIndex + 2
    ComboBox2 = Ws.Cells(Ligne, "B")
    Textbox1 = Ws.Cells(Ligne, "C")
    For i = 2 To 14
      Controls("TextBox" & i) = Format(Ws.Cells(Ligne, i + 2), fmt1)
    Next i
  End If
  If ComboBox1.ListIndex <> -1 Then Cells(ComboBox1.ListIndex + 2, 1).Select
End Sub


Private Sub Valider_Click()
  'Correspond au programme du bouton Modifier
  Dim MaSomme#, MC%, Ligne&, MPL&, MDL&
 
  If MsgBox("Etes-vous certain de vouloir mofifier la fiche?", vbYesNo, "Demande de confirmation") = vbYes Then
    If SelectedIndex = -1 Then Exit Sub
    Ligne = SelectedIndex + 2
    Ws.Cells(Ligne, "B") = ComboBox2
    Ws.Cells(Ligne, 3) = Textbox1
    Ws.Columns("D:O").NumberFormat = fmt1
    Ws.Columns("P").NumberFormat = fmt2
    On Error Resume Next
    Ws.Range("D" & Ligne) = Trim$(TextBox2) * 1
    Ws.Range("E" & Ligne) = Trim$(TextBox3) * 1
    Ws.Range("F" & Ligne) = Trim$(TextBox4) * 1
    Ws.Range("G" & Ligne) = Trim$(TextBox5) * 1
    Ws.Range("H" & Ligne) = Trim$(TextBox6) * 1
    Ws.Range("I" & Ligne) = Trim$(TextBox7) * 1
    Ws.Range("J" & Ligne) = Trim$(TextBox8) * 1
    Ws.Range("K" & Ligne) = Trim$(TextBox9) * 1
    Ws.Range("L" & Ligne) = Trim$(TextBox10) * 1
    Ws.Range("M" & Ligne) = Trim$(TextBox11) * 1
    Ws.Range("N" & Ligne) = Trim$(TextBox12) * 1
    Ws.Range("O" & Ligne) = Trim$(TextBox13) * 1
    Ws.Range("P" & Ligne).Formula = "=SUM(D" & Ligne & ":O" & Ligne & ")"
    Err.Clear
  End If
 
  MC = 16 'Ma Colonne
  MPL = 2 'Ma Première Ligne
  MDL = Cells(Rows.Count, "P").End(xlUp).Row - 1 'Ma Dernière Ligne
 
  For Ligne = MPL To MDL
    MaSomme = MaSomme + Cells(Ligne, MC)
  Next Ligne
  TextBox15 = MaSomme
    
  Worksheets("Top 10 Conso").Select: Tri
  Worksheets("Controle").Select
 
End Sub

Option Explicit

Sub Tri()
  Dim plage As Range
  Worksheets("Top10Conso").Select: Application.ScreenUpdating = 0
  Set plage = Range("B2:P" & Range("P" & Rows.Count).End(xlUp).Row - 1)
  plage.Sort key1:=[P2], Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, Orientation:=xlTopToBottom
End Sub

Private Sub CommandButton1_Click()
    arr = Array(TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, TextBox7, TextBox8, TextBox9, TextBox10, TextBox11, TextBox12, TextBox13)
    For i = LBound(arr) To UBound(arr): arr(i) = Val(Replace(arr(i), ",", ".")): Next
    ligne = 1
    With Range("D" & ligne).Resize(, UBound(arr) + 1): .Value = arr: .NumberFormat = "#0.00": End With
End Sub