Microsoft 365 vba format date et N° automatique

[Piment]

Bonsoir le forum,
Une fois de plus je fais appel à vous.
Je rencontre 2 problèmes que je n'arrive pas à résoudre:
1° Sur mes USF Visualiser et Modifier, mes textBox Heures ne s'affiche pas au format hh:mm bien que j'ai précisé ce format.
2° je n'arrive pas à l'ouverture de l'USF Devis à ce qu'il me donne automatiquement le N° Suivant qui devrait être 2021-002 puis au prochain devis 2021-003...
Auriez-vous la gentillesse de bien vouloir m'aider s'il vous plaît.
Je sais pouvoir compter sur votre aide.
Merci d'avance!

 

[Piment]

Bonjour 974,
Dans l'USF Devis, la date s'affiche correctement chez moi :
Regarde la pièce jointe 1096375
Pour le point 2, la feuille n'est pas précisée, j'ai supposé que c'était "Archive Devis", ensuite Ligne pointe la première ligne vide donc contient toujours 0, il faut utiliser le N° de la Ligne-1. J'ai modifié ainsi :

Set Ws = Sheets("Prestation")
Set F = Sheets("Archive Devis")
Me.Désignation.List = Ws.Range("ListForfaits").Value

If Ligne = 2 Then
    Me.TextBox6 = Year(Date) & "-001"
Else
    Me.TextBox6 = Year(Date) & "-" & Right("00" & Val(Right(F.Cells(Ligne - 1, 1), 3)) + 1, 3)
End If

Sylvanu bonjour,
Contrairement à ce que tu as pu penser, je n'ai pas "zappé" ton post.
Je l'ai testé, il m'indique: Erreur de compilation variable non définie (Set F = Sheets("Archive Devis")
Merci de l'attention que tu porte à m'aider.

 

[soan]

Bonsoir 974RE,

désolé de ne pas t'avoir répondu plus tôt ! 😳 (c'est par manque de temps, et pas un oubli)

tu as écrit : « Pour les problèmes d'affichage des heures, c'est réglé. Ouf! » ; que voilà une bonne nouvelle ! 😊 comme tu dis : « Ouf! » ; c'est toujours ça de gagné ! 😄​

---

je commence par le plus simple ; pour ton bouton Quitter, essaye cette nouvelle sub, qui est dans le module du UserForm Devis :​

Private Sub QUITTER_Click()
  Dim sh As Worksheet: Set sh = Worksheets("Devis")
  With sh.ListObjects("Devis")
    If Not .DataBodyRange Is Nothing Then
      If MsgBox("Êtes-vous certain de vouloir vider le tableau ?", _
      vbYesNo, "Demande de confirmation") = vbYes Then
        .DataBodyRange.Delete
        sh.Range("B3, E4, E5, E6, B9, A15, A17, A19, A21") = ""
      End If
    End If
  End With
  Unload Me: Accueil.Show 0
End Sub

tu t'apercevras que ça pose la question de vider le tableau uniquement quand il n'est PAS vide ; il suffit d'UNE donnée pour que la MsgBox s'affiche, même si y'a qu'UNE seule ligne, et même si cette 1ère ligne est INCOMPLÈTE ; donc tableau vide ➯ PAS de MsgBox ; saisis juste "a" en A12 ➯ MsgBox, et si tu confirmes : effacement des données du tableau ; et dans tous les cas : cette sub ne plantera plus ! 😜​

---

à propos de ton post #16, tu dois déclarer la variable F ainsi : Dim F As Worksheet ➯ tu n'auras plus l'erreur de compilation « variable non définie » ; mais je te réponds là-dessus juste pour que tu puisses éviter l'erreur de compil : j'ai pas regardé dans quelle sub c'est placé, et je l'ai pas utilisé dans mon code VBA ci-dessus, ni dans celui de mon post #18 ci-après.​

soan

 

[soan]

Bonjour 974RE,

Lis d'abord mon post #17 précédent.

😳 oooopppppsssss ! j'suis vraiment désolé, mais j'm'étais bien trompé pour le code VBA précédent de la sub UserForm_Initialize() du UserForm Devis ! en remplacement, voici le nouveau code VBA :​

Private Sub UserForm_Initialize()
  Dim chn$, lig&, n&: Jour = Date: lig = 2
  Do
    chn = Worksheets("Client").Cells(lig, 3): If chn = "" Then Exit Do
    ComboBox1.AddItem chn: lig = lig + 1
  Loop
  Désignation.List = Worksheets("Prestation").Range("ListForfaits").Value
  n = Worksheets("Archive Devis").ListObjects("ArchiveDevis").ListRows.Count + 1
  TextBox6 = Year(Date) & "-" & Format(n, "000")
End Sub

va sur ta feuille "Archive Devis" ; tu peux tout d'suite voir que ton tableau n'a plus que 2 lignes, car j'ai supprimé ta dernière ligne vide : la 3ème ; en effet, dans un Tableau structuré, il ne doit pas y avoir de ligne vide ! donc maintenant : .ListRows.Count = 2 : c'est bien le nombre de lignes correct de ton Tableau structuré "ArchivesDevis" ➯ .ListRows.Count + 1 = 3 : voici donc quel sera le nouveau numéro de ton prochain devis, et j'ai même pas eu besoin de me servir de ta feuille "numerotation" (donc j'ai utilisé ni A1, ni A2) ; quand tu ajouteras des lignes de devis au Tableau structuré "ArchivesDevis", le numéro devis du UserForm Devis devrait normalement s'incrémenter automatiquement ; je te laisse essayer. 😉​

si besoin, tu peux demander une adaptation.
à te lire pour avoir ton avis.

soan​

 

[Piment]

Bonjour Soan,
Très heureux de te lire de nouveau.
Très heureux que tu te sois penché de nouveau sur mes problèmes, et cela avec beaucoup d'attention et de gentillesse. J'apprécie énormément. On se sent tellement dépourvu parfois.
Merci pour les solutions et les explications qui vont avec.
Je vais reprendre tout ça tranquillement et essayer d'appréhender pas à pas le process afin de comprendre le pourquoi des erreurs.
Puisque j'y suis, j'ai une autre erreur et bien embêtante: après avoir activé l'USF pour entrer les données "KM ..." je rentre les données dans le tableau (Désignation, Qté Prix Unitaire), le prix unitaire qui est rapatrié n'a rien à voir avec les données saisies avec l'USF Estimatif qui ont été intégrées dans le tableau "ListForfaits" de la feuille Prestation.
Après réflexion, je pense que le problème pourrait venir de l'interaction entre les deux USF. Il faudrait peut être réinitialiser l'USF Devis avant de pouvoir saisir les nouvelles données du tableau" ListForfaits"? Comment faire?
Merci infiniment.
Très bonne journée à toi.

 

[patricktoulon]

bonjour
j'arrive a peine sur le sujet et je vois une erreur monumentale

If Ligne = 2 Then
    Me.TextBox6 = Year(Date) & "-001"
Else
    'Me.TextBox6 = Year(Date) & "-" & Right("00" & Val(Right(Cells(Ligne, 1), 3)) + 1, 3)
  Me.TextBox6 = Year(Date) & "-" & Format(Val(Split(Cells(Ligne - 1, 1).Value, "-")(1)) + 1, "000")

End If

'Me.TextBox6 = Year(Date) & "-" & Right("00" & Val(Right(Cells(Ligne, 1), 3)) + 1, 3)
ma fois quand on va avoir 10 de devis donc "F-10" dans la cellule que va t il se passer d’après toi pour le suivant
ben tout simplement le right(1) va prendre le zero et donc +1 = 1
LOL!!!!!!! 😉

je parle même pas du sheets parent qui n'est pas précise pour cells et pour peux que tu soit pas sur le bon sheets actif ben c'est la bérézina

et tout le code est bourré d'erreur comme ça
et de maladdresse du genre

While (ThisWorkbook.Worksheets("Client").Cells(Ligne, 3).Value <> "")
ComboBox1.AddItem (ThisWorkbook.Worksheets("Client").Cells(Ligne, 3).Value)
Ligne = Ligne + 1
Wend

serieux pour remplir ta combo
non de dieu!! c'est un listobject ton tableau le numéro de devis sera tout simplement listobjects("nom de ton tableau").listrows.count+1
c'est pas compliqué
donc si tu veux bien (par ce que j'oblige personne)
essaie de travailler proprement et coder de façon pérenne
donc pour le démarrage de ton usf devis ( le initialise)
voila ton code

Private Sub UserForm_Initialize()
Dim Lst
Jour = Date '?????????????

Set Lst = ThisWorkbook.Worksheets("Client").ListObjects("ListClients") ' ça c'est ton tableau structuré client

ComboBox1.List = Lst.DataBodyRange.Columns(3).Value ' la combo.list  prend la colonnes 3 du tableau

Me.Désignation.List = Sheets("Prestation").Range("ListForfaits").Value

If Lst.ListRows.Count = 1 Then 'si le tableau est vide
    Me.TextBox6 = Year(Date) & "-001"
Else
     Me.TextBox6 = Year(Date) & "-" & Format(Lst.ListRows.Count + 1, "000") 'si le tableau n'est pas vide
End If
End Sub

Terminé c'est propre net et clair

 

[soan]

@974RE (salut patrick)

Voici tout ce que j'ai constaté :

A) feuille "Budget"

1) Prévisionnel Km, en K2 = 3 000

2) Charges Variables, en D4 = 8 465,00 €
3) Frais de Personnel, en D13 = 4 108,00 €
4) différence = D4 - D13 = 8 465,00 - 4 108,00 = 4 357,00 €
5) Coût au Km Charges Variables, en B43 = différence / K2
= 4 357,00 / 3 000 = 1,452 333 333 ; affiché : 1,45€

6) Charges Fixes, en D19 = 3 391,71 €
7) Coût au Km Charges Fixes, en B44 = D19 / K2
= 3 391,71 / 3 000 = 1,130 570 000 ; affiché : 1,13 €
​

---

B) Feuille "Prestation"

1) Coût au Km, Peugeot 504
a) Charges Fixes, en G3 = Budget!B44 = 1,13
b) Charges Variables, en G4 = Budget!B43 = 1,45
c) Total, en G6 = SOMME(G3:G5) = 1,13 + 1,45 = 2,58 €

attention : ces 3 résultats sont affichés avec 2 décimales,
mais en interne, il y en a plus ! à toi de voir si tu veux ou
non faire un arrondi à 2 décimales exactement.

2) Devis, PU, Estimatif Km, en C3 = G6 = 2,58 €
même remarque : affiché avec 2 décimales ;
y'en a plus en interne.​

3) Les 4 données que tu saisis dans le USF Estimatif_Coût
vont respectivement en B3 ; B4 ; B6 et B7 ; pour la suite,
je vais prendre un exemple où tu aurais saisi 10 partout,
pour chacune des 4 valeurs ; il y a donc 10 en B3, B4, B7, et
10,00% en B6 ; d'où les autres valeurs que tu vois à droite,
en colonnes C3: D8 et D9: D10.

4) Total 504, en D9 = D6+D7+D8 = 203 € + 500 € + 61 € = 764 €
5) Total MG, en D10 = D9 = 764 €

6) Montant Forfaits Location avec Chauffeur
a) Forfait 504 Peugeot, en B13 = D9 = 764 €
b) Forfait MG, en B14 = D10 = 764 €

7) Nom défini ListForfaits : Fait référence à : =Prestations!$A$13:$B$14
donc c'est, entre autres, les 2 montants de B13 et B14 de 764 €
​

---

je n'ai fait que détailler ce qui existe déjà, sans avoir rien modifié ; je ne sais pas au juste si tes calculs sont exacts ou non, c'est à toi de voir, en tenant compte aussi du nombre de décimales que tu veux vraiment pour les calculs ; c'est-à-dire soit en utilisant toutes les décimales en interne, soit en utilisant un arrondi à 2 décimales ; pour le reste, je n'ai pas bien compris, mais j'espère que tu trouveras une solution. 🍀
​

soan

 

[soan]

@974RE

Lis d'abord mon post #21 précédent.

ah, si, je crois avoir un peu mieux compris ; essaye avec cette sub :

Private Sub Désignation_Change()
  With Worksheets("Prestation")
    Qté = .[B3]
    PrixUnité = Round(.[C3], 2)
    Montant_HT = Round(.[D3], 2)
  End With
End Sub

MAIS je n'ai pas fait la distinction entre les 2 forfaits 504 Peugeot et MG, car tu n'as pas bien détaillé ta demande, et j'ai pas bien compris comment tu fais cette différence ; à supposer que c'est bien en B3:D3 les données du 1er forfait 504 Peugeot, dans quelles cellules sont donc les données pour le forfait MG ? en principe, je pense que ça serait aussi sur la même feuille "Prestation", mais au cas où ça serait sur une autre feuille, merci de préciser laquelle ! 😁 😉​

tu auras noté que dans le USF Devis, j'ai fait un arrondi à 2 décimales. 🙂

soan

 

[Piment]

bonjour
j'arrive a peine sur le sujet et je vois une erreur monumentale

If Ligne = 2 Then
    Me.TextBox6 = Year(Date) & "-001"
Else
    'Me.TextBox6 = Year(Date) & "-" & Right("00" & Val(Right(Cells(Ligne, 1), 3)) + 1, 3)
  Me.TextBox6 = Year(Date) & "-" & Format(Val(Split(Cells(Ligne - 1, 1).Value, "-")(1)) + 1, "000")

End If

'Me.TextBox6 = Year(Date) & "-" & Right("00" & Val(Right(Cells(Ligne, 1), 3)) + 1, 3)
ma fois quand on va avoir 10 de devis donc "F-10" dans la cellule que va t il se passer d’après toi pour le suivant
ben tout simplement le right(1) va prendre le zero et donc +1 = 1
LOL!!!!!!! 😉

je parle même pas du sheets parent qui n'est pas précise pour cells et pour peux que tu soit pas sur le bon sheets actif ben c'est la bérézina

et tout le code est bourré d'erreur comme ça
et de maladdresse du genre

While (ThisWorkbook.Worksheets("Client").Cells(Ligne, 3).Value <> "")
ComboBox1.AddItem (ThisWorkbook.Worksheets("Client").Cells(Ligne, 3).Value)
Ligne = Ligne + 1
Wend

serieux pour remplir ta combo
non de dieu!! c'est un listobject ton tableau le numéro de devis sera tout simplement listobjects("nom de ton tableau").listrows.count+1
c'est pas compliqué
donc si tu veux bien (par ce que j'oblige personne)
essaie de travailler proprement et coder de façon pérenne
donc pour le démarrage de ton usf devis ( le initialise)
voila ton code

Private Sub UserForm_Initialize()
Dim Lst
Jour = Date '?????????????

Set Lst = ThisWorkbook.Worksheets("Client").ListObjects("ListClients") ' ça c'est ton tableau structuré client

ComboBox1.List = Lst.DataBodyRange.Columns(3).Value ' la combo.list  prend la colonnes 3 du tableau

Me.Désignation.List = Sheets("Prestation").Range("ListForfaits").Value

If Lst.ListRows.Count = 1 Then 'si le tableau est vide
    Me.TextBox6 = Year(Date) & "-001"
Else
     Me.TextBox6 = Year(Date) & "-" & Format(Lst.ListRows.Count + 1, "000") 'si le tableau n'est pas vide
End If
End Sub

Terminé c'est propre net et clair

Bonjour Patricktoulon,
Tout d'abord, merci de ton retour.
J'ai testé également ta proposition, mais cela ne marche pas. Il me semble, si j'ai bien compris, que tu t'appuie sur l'objet "ListClients" pour incrémenté le N° de Devis. Ne faudrait-il pas s'appuyer sur l'objet "ArchiveDevis" ?
L'objet "ListClients" permet de récupérer les info pour alimenter l'USF afin de générer le Devis. Ensuite les éléments du Devis sont archivés sur la feuille Archive Devis.
Merci te te pencher à nouveau sur mon problème.

 

[Piment]

@974RE

Lis d'abord mon post #21 précédent.

ah, si, je crois avoir un peu mieux compris ; essaye avec cette sub :

Private Sub Désignation_Change()
  With Worksheets("Prestation")
    Qté = .[B3]
    PrixUnité = Round(.[C3], 2)
    Montant_HT = Round(.[D3], 2)
  End With
End Sub

MAIS je n'ai pas fait la distinction entre les 2 forfaits 504 Peugeot et MG, car tu n'as pas bien détaillé ta demande, et j'ai pas bien compris comment tu fais cette différence ; à supposer que c'est bien en B3:D3 les données du 1er forfait 504 Peugeot, dans quelles cellules sont donc les données pour le forfait MG ? en principe, je pense que ça serait aussi sur la même feuille "Prestation", mais au cas où ça serait sur une autre feuille, merci de préciser laquelle ! 😁 😉​

tu auras noté que dans le USF Devis, j'ai fait un arrondi à 2 décimales. 🙂

soan

Soan,
Le Prix unitaire figurant dans la "Devis" de l'UserForm Devis est rapatrié de la "ListForfaits" de la feuille Prestation (A13:B14),. Le tableau au dessus permet d'entrer les estimatifs de Km... Le tableau "ListForfaits" (A13:B14) récupère les montants total issus du tableau au dessus. Lorsque qu'on choisi la désignation dans la ComboBox "Désignation", le PU de l'élément choisi s'affiche automatiquement. Puis le calcul se fait via les codes pour obtenir le Montant HT.
Qté dans la liste Devis est saisie manuellement. Il peut y avoir deux réservations pour le même évènement et pour le même véhicule.
Qté sera alors (2 ou 3 si réservation sur 3 jours).
Je pense que le problème vient du fait que lorsque j'ouvre l'USF Estimatif (Bouton Rouge du Devis), que je rentre les données (KM Temps de Travail, Remise, Panier Confort), le 1er Tableau de la feuille prestation se met à jour, fait les calculs, et le 2ème tableau ("ListForfaits") récupère bien les nouveaux montants. Mais lorsque je saisie les données dans la liste "Devis (Désignation, le PU n'est pas récupérer correctement.
Une idée qui me vient: ne faudrait-il pas, après avoir fermé l'USF réactiver la fueille Devis avant de saisir désignation dans la list "Devis"? sans perdre les éléments déjà entrés par la ComboBox1.
Je voudrais aussi attirer ton attention, que depuis les modification apportées pour le N° Devis, le N° s'incrémentent, mais lors de l'archivage, il ne rajoute pas une nouvelle ligne mais écrase la Ligne existante.
Je sais que je te demande beaucoup, mais sans ton aide je n'y arriverais pas.
Merci encore.

 

[patricktoulon]

et ben dis donc c'est pas gagné
donc soit
ben on prends les mêmes et on recommence
j'ai supprimé la ligne vide du tableau structuré
on ajoute une ligne seulement au moment d' enregistrer , on laisse pas une ligne vide dans un TS sinon ça n'a aucun sens d'utiliser un TS

Private Sub UserForm_Initialize()
Dim Lst, Lst2
Jour = Date '?????????????

Set Lst = ThisWorkbook.Worksheets("Client").ListObjects(1) ' ca c'est ton tableau structuré client
ComboBox1.List = Lst.DataBodyRange.Columns(3).Value ' la combo.list  prend la colonnes 3 du tableau

Set Lst2 = ThisWorkbook.Worksheets("Archive Devis").ListObjects(1) ' ca c'est ton tableau structuré client

Désignation.List = Sheets("Prestation").Range("ListForfaits").Value

If Lst2.ListRows.Count = 1 Then 'si le tableau est vide
    TextBox6 = Year(Date) & "-001"
Else
     TextBox6 = Year(Date) & "-" & Format(Lst2.ListRows.Count + 1, "000") 'si le tableau n'est pas vide
End If

End Sub

 

[Piment]

Pendant que j'y pense, pour le moment le forfait 504 et MG est identique effectivement. C'est pour les tests.
Les calculs pour les deux véhicules en terme de coût seront affinés en fonction des coûts de revient de chaque véhicule respectifs.

 

[Piment]

et ben dis donc c'est pas gagné
donc soit
ben on prends les mêmes et on recommence
j'ai supprimé la ligne vide du tableau structuré
on ajoute une ligne seulement au moment d' enregistrer , on laisse pas une ligne vide dans un TS sinon ça n'a aucun sens d'utiliser un TS

Private Sub UserForm_Initialize()
Dim Lst, Lst2
Jour = Date '?????????????

Set Lst = ThisWorkbook.Worksheets("Client").ListObjects(1) ' ca c'est ton tableau structuré client
ComboBox1.List = Lst.DataBodyRange.Columns(3).Value ' la combo.list  prend la colonnes 3 du tableau

Set Lst2 = ThisWorkbook.Worksheets("Archive Devis").ListObjects(1) ' ca c'est ton tableau structuré client

Désignation.List = Sheets("Prestation").Range("ListForfaits").Value

If Lst2.ListRows.Count = 1 Then 'si le tableau est vide
    TextBox6 = Year(Date) & "-001"
Else
     TextBox6 = Year(Date) & "-" & Format(Lst2.ListRows.Count + 1, "000") 'si le tableau n'est pas vide
End If

End Sub

Je suis désolé, je suis d'accord, c'est loin d'être gagné. Mais, tu sais grâce à votre aide à tous, j'ai pas mal avancé. J'ai pleinement conscience que j'ai encore beaucoup à apprendre. J'ai toujours pensé que c'est en faisant qu'on apprend. Tout seul c'est impossible, avec vous je pense pouvoir y arriver.
Pour revenir à mon problème, je pense comprendre ce que tu propose, mais lorsque je rentre un nouveau Devis, le N° reste inchangé, et lorsque je click le bouton Archiver Devis, il m'écrase la ligne existante dans la liste "ArchiveDevis" de la feuille Archive Devis"., et me rajoute une ligne vide en dessous.
Merci pour la peine que tu te donnes pour m'aider.

 

[patricktoulon]

allez on va a l'essentiel

Private Sub ArchiveDevis_Click()
    Dim A
    With Sheets("Archive Devis").ListObjects(1).ListRows.Add()
             A = Array(TextBox6.Value, Jour.Value, , NOM.Value, ADRESSE.Value, CP & " " & ComboBox3.Value, TextBox1.Value, Désignation.Value, CDbl(Qté.Text), CDbl(PrixUnité.Text), Int(CDbl(PrixUnité.Text) * CDbl(Qté.Text)), "", "", "", "")
            .Range.Resize(, UBound(A)+1).Value = A
     End With
End Sub

c'est pas compliqué soit on travaille avec un listobjects et on bénéficie des facilités dues a son utilisation soit on reste en range classique
et par pitié ne pas laisser une ligne vide en dernier dans un tableau structuré ça n'a pas de sens
voila Mr 😉faut il que je donne mon exemplaire ???

 

[Piment]

allez on va a l'essentiel

Private Sub ArchiveDevis_Click()
    Dim A
    With Sheets("Archive Devis").ListObjects(1).ListRows.Add()
             A = Array(TextBox6.Value, Jour.Value, , NOM.Value, ADRESSE.Value, CP & " " & ComboBox3.Value, TextBox1.Value, Désignation.Value, CDbl(Qté.Text), CDbl(PrixUnité.Text), Int(CDbl(PrixUnité.Text) * CDbl(Qté.Text)), "", "", "", "")
            .Range.Resize(, UBound(A)+1).Value = A
     End With
End Sub

c'est pas compliqué soit on travaille avec un listobjects et on bénéficie des facilités dues a son utilisation soit on reste en range classique
et par pitié ne pas laisser une ligne vide en dernier dans un tableau structuré ça n'a pas de sens
voila Mr 😉faut il que je donne mon exemplaire ???

J'y suis presque. J'ai bien compris qu'il ne faut pas laisser de ligne vide en dernier dans un tableau. Cette ligne vide s'est mise lorsque j'ai archivé le devis.
Suite à ton code, je vois qu'il y a plusieurs façon de procéder. J'en prends acte.
J'attire ton attention, sauf erreur de ma part, que la nouvelle ligne s'inscrit bien dans le tableau Archive Devis, mais le N° reste inchangé: il ne s'incrémente pas à l'ouverture pour la saisie d'un nouveau devis.
Je sais que je vous donne du fil à retordre avec cette affaire, mais lorsque ce sera fait, mon dieu quelle satisfaction!

 

[patricktoulon]

re

mais le N° reste inchangé

c'est tout bonnement impossible

le numero s'incrémente a l'ouverture du formulaire

Suite à ton code, je vois qu'il y a plusieurs façon de procéder. J'en prends acte.

il n'y a pas plusieurs façons de procéder il y a les bonnes et les mauvaises

demo