Saisir valeurs formulaires sur deux feuilles différentes

Le 06/06/2023 à 18:19

Bonjour à tous,

Je suis nouveau sur le forum et novice en VBA.

J'ai créer un fichier excel pour la gestion de comptes. A partir d'un formulaire (UF_Virement), je souhaite saisir un virement interne entre comptes. Chaque compte est une feuille distincte sélectionnable à partir de deux ComboBox et je veux que le débit du compte 1 apparaisse en crédit sur le compte 2.

Malgré divers essais et corrections, je n'arrive qu'à des messages d'erreurs, "Erreur d'exécution 9 : lindice n'appartient pas à la sélection" ou "Sub ou variable non définie"...

Ci-dessous le code et je joins également le fichier.

Merci d'avance pou l'aide que vous pourrez m'apporter.

17gestcomptes.xlsm (208.82 Ko)
Sub BTN_SauveCont_Click()
    'Déclaration des variables
    Dim Source As Object
    Dim Cible As Object

    'Définir les comptes source et cible
    Set Source = Sheets(CB_Comptes.Value)
    Set Cible = Sheets(CB_CompteCible.Value)

    'Recherche de la première ligne vide
    i = 11

    While Not IsEmpty(Cells(i, 2))
    i = i + 1
    Wend

    'Saisie les informations sur la feuille source
        Source.Cells(i, 2) = CDate(TB_Date.Value)
        Source.Cells(i, 3) = CB_ModPaie.Value
        Source.Cells(i, 5) = CB_Cheque.Value
        Source.Cells(i, 6) = CB_Tiers.Value
        Source.Cells(i, 7) = CB_RubriqueDebit.Value
        Source.Cells(i, 9) = TB_Comment.Value
        Source.Cells(i, 10) = TB_Somme.Value

    Cible.Activate
    'Saisie les informations sur la feuille source
        Cible.Cells(i, 2) = CDate(TB_Date.Value)
        Cible.Cells(i, 3) = CB_ModPaie.Value
        Cible.Cells(i, 5) = CB_Cheque.Value
        Cible.Cells(i, 6) = CB_Tiers.Value
        Cible.Cells(i, 7) = CB_RubriqueDebit.Value
        Cible.Cells(i, 9) = TB_Comment.Value
        Cible.Cells(i, 11) = TB_Somme.Value

    'Laisse le formulaire apparent pour une nouvelle saisie
    Load UF_Ecritures

End Sub
Le 07/06/2023 à 08:13

Bonjour et bienvenu sur le forum

1) aucune ligne vide dans un tableau structuré

2) userform.hide le masque (aucun interet) unload me: le ferme...

3)cbcompte et cbcomptecible n'etait pas égale au nom de la feuille (AsVie et non Assurance Vie)

Ci joint ma solution

14gestcomptes.xlsm (186.94 Ko)

A+ François

Le 07/06/2023 à 17:53

Bonjour François,

D'abord merci pour ta réactivité et ton travail.

Ta solution fonctionne bien, mais qand je la transpose dans le fichier original, ou j'ai plus de feuilles, les lignes s'ajoutent en fin de tableau et non sur la première ligne vide du tableau.
J'ai bien compris que c'était à cause de ta remarque 1) "aucune ligne vide dans un tableau structuré". Mais en suprimant les ligne vide, je perds le formatage (colonne D plus de police Winding, ) et des formules dans les colonnes L et M.
Avec le code ci-dessous, de 'UF_Ecriture, je n'ai pas ce problème, mais je n'arrive pas à l'adapter à l'UF_Virement.

Sub enreg()
    Dim Table As ListObject
    Set Table = ActiveSheet.ListObjects(1)

    'Recherche de la première ligne vide
    i = 11
    While Not IsEmpty(Cells(i, 2))
    i = i + 1
    Wend

    'Saisir les informations
    Cells(i, 2) = CDate(TB_Date.Value)
    Cells(i, 3) = CB_ModPaie.Value
    Cells(i, 5) = CB_Cheque.Value
    Cells(i, 6) = CB_Tiers.Value
    Cells(i, 7) = CB_Rubrique.Value
    Cells(i, 8) = CB_Classe.Value
    Cells(i, 9) = TB_Comment.Value
    Cells(i, 10) = TB_Debit.Value
    Cells(i, 11) = TB_Credit.Value

End Sub

Autre soucis, dans mon fichier original, ta solution (hormis le problème de ligne) fonctionne avec tous les onglets sauf "LEP Pierre". Il renvoi "Erreur d'exécution 9 : lindice n'appartient pas à la sélection". Il est pourtant intégré dans le Tableau10. Je sèche...

Est-il possible de conserver les lignes structurées du tableau, ou faut-il prévoir le foramtage et les formules dans le code ?
Je joins les deux fichiers.

Encore merci.
@+ Pierre

11gestcomptes-ori.xlsm (212.17 Ko)
10gestcomptes.xlsm (189.60 Ko)
Le 08/06/2023 à 07:15

Bonjour

SVP si problème ne supprime pas tout....

C'est justement l'avantage des tableaux structurés

- Copie des formules automatique (mais attention! si tu fais appelle à une cellule fixe il faut l'adresse absolue donc $L$6)

- copie des formats (ils doivent tous être identique dans la colonne)

- si tu ajoutes des données dans un tableau structuré les formules s'adaptent (ex:ET_modpaie=J2:j15 à refaire si tu ajoutes une données)

Bref! tu fais l'essai et tu me tiens au courant

13gestcomptes.xlsm (227.37 Ko)

A+ François

Ps: Si tu selectionnes une cellule dans la colonne M le V se mets (ou s'enlève)

Le 08/06/2023 à 19:21
14gestcomptes.xlsm (240.65 Ko)

Bonjour François,
Mon retour à tes dernières modifs.

J'ai fait une modif sur

Private Sub UserForm_Initialize()
  CB_Comptes.List = [Tableau10].Value
  CB_Tiers.List = [T_tiers].Value
  CB_ModPaie.List = [T_mode].Value
  CB_Classe.List = [T_classe].Value
  CB_Rubrique.List = [T_rubrique].Value
End Sub

Il manquait .Value sur deux lignes. Tu voulais tester si je suivais...

UF_Ecriture :
1- la saisie d’un débit renvoi "erreur 13 incompatibilité de type" et le débogage surligne la ligne "Range(Table).Item(i, 10) = CDbl(TB_Credit.Value)" dans la Sub enreg()
- et inversement la saisie d’un crédit renvoi "erreur 13 incompatibilité de type" et le débogage surligne la ligne "Range(Table).Item(i, 9) = CDbl(TB_Debiit.Value)" dans la Sub enreg()
2- si je saisi une valeur débit et « 0 » les deux valeurs sont validées et pas de message d’erreur, mais ce n’est pas trop logique
3- si je supprime la fonction CDbl, pas d’erreur non plus, mais le nombre est stocké sous forme de texte, d’où la nécessité de CDbl
4- avec la procédure

Sub CB_Comptes_AfterUpdate()    'Affiche la feuille sélectionnée dans la liste
    Sheets(Me.CB_Comptes.Column(1)).Activate
End Sub

J’ai "erreur d’exécution 9 l’indice n’appartient pas à la sélection" pour certains choix, notamment si je fais le choix "AsVie Carme" en lancant UF depuis "compte courant"

UF_Virement

Après validation j'ai "erreur d’exécution 9 l’indice n’appartient pas à la sélection" et le débogage surligne la ligne 7 ci-dessous

Sub Enreg()
    'Déclaration des variables
    Dim Source As Object, Cible As Object
    If Me.TB_Cheque.Value <> "" Then Range("$N$13").Value = Me.TB_Cheque.Value
    'Définir les comptes source et cible
    Set Source = Sheets(CB_Comptes.Column(1)).ListObjects(1)
    Set Cible = Sheets(CB_CompteCible.Column(1)).ListObjects(1)
    'Recherche de la première ligne vide

Concernant ta question Ps: Si tu selectionnes une cellule dans la colonne M le V se mets (ou s'enlève), en fait les formules de Solde et Solde rapproché sont différentes sur la première ligne et les lignes suivantes.
M11 = SI(ET(J11="";K11="");"";L6+K11-J11)
N11 = SI(M11="";"";L6-J11+K11)

M12 et suivantes =SI(ET(J12="";K12="");"";L11+K12-J12) en incrémentant +1
N12 et suivantes =SI(M12="";"";RECHERCHE(9^9;$N$11:N11)+K12-J12) en incrémentant +1
J'ai fait le choix de RECHERCHE(9^9 car au pointage des opérations, les lignes pointées ne sont pas forcément contigues. A ce sujet l'insertion de la coche par clic de la cellule, c'est génial !

Le fait de ne pas avoir de ligne vide dans un tableau structuré fausse, bien sur, les formules de la seconde ligne.
J'ai vu que l'on pouvait insérer les formules directement dans le code, avec Formula et FormulaR1C1, mais je n'ai pas eu le temps d'essayer. A ton avis il vaut mieux le faire dans un module ou directement sur la worksheet ?

Encore merci pour ta patience et tes comptétences. Si j'abuse n'hésite pas à me le dire.

@+ Pierre

Le 09/06/2023 à 07:40

Bonjour

Sur le mien de compte la valeur rapproché n'est pas par ligne...

Je te propose en L8 la valeur rapproché et supprimer la colonne N...

Pour le reste: je devais ajouté If IsNumeric(TB_Debit.Value) Then ... idem pour credit (fait)

Erreur 9 dû à des espaces dans les noms de feuille ... (fait)

A+ François

Le 09/06/2023 à 10:47

Bonjour,

J'ai ajouté If IsNumeric et ça fonctionne parfaitement. Merci

Par contre pour l'erreur 9 je ne vois pas le problème d'espace, si c'est dans le code ou les feuilles dans le classeur.
Peux-tu me transmettre tonfichier modifié ?

@+ Pierre

Le 09/06/2023 à 11:42

J'attendais ta réponse pour la valeur rapprochée

Fichier ci joint ta formule mais tu verras que si on supprime un rapprochement c'est pas top

Pour Avi Carme il y avait un espace après et pour un de tes comptes il y avait 2 espaces au lieu d'un entre LEP ou Avi et Pierre... dans le nom de l'onglet

13gestcomptes-v2.xlsm (223.61 Ko)

A+ François

Le 09/06/2023 à 14:54

Effectivement, les espaces supprimés le problème est résolu.

Concernant la formule, en effet c'est pas top. Donc je vais faire des essais pour intégrer les formules dans le code et je reviendrai vers toi si nécessaire (fort probablement).
En attendant, je clôture ce post pour éviter de polluer le forum. Je pense que ma prochaine demande sera un autre sujet.

Encore merci.

@+ Pierre

Le 09/06/2023 à 15:16

Tu as vu qu'en L8 le solde rapproché est bon

A+ et bon courage

François

Le 09/06/2023 à 16:31

Ci joint une macro pour supprimer les liaisons et ne plus avoir ce message au démarrage

(elles sont, pour beaucoup, dans certaines validations de données)

Sub SupprimerLiaisons() 'par Excel-Malin.com ( https://excel-malin.com/ )
  Dim Liaisons As Variant
  Liaisons = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
  If IsEmpty(Liaisons) = True Then Exit Sub
  For LiaisonsTrouvee = 1 To UBound(Liaisons)
   ActiveWorkbook.BreakLink Name:=Liaisons(LiaisonsTrouvee), Type:=xlLinkTypeExcelLinks
  Next LiaisonsTrouvee
End Sub

A+ François

Le 09/06/2023 à 17:02

Je l'avais fait manuellement sur la dernière version, mais je vais intégrer ton code pour les versions à venir.

Merci François

@+ Pierre

Le 13/06/2023 à 18:49

Bonjour François,

J'ai trouvé une solution pour les colonnes "solde" et "solde rapproché".

J'ai converti les deux colonnes en plage de données et séparées du tableau par une colonne vide (pour eviter qu'elles soient captées par le tableau).
Ensuite avec une procédure Sub Recopie que j'ai ajouté à la suite de ta fonction Sub Enreg dans les formulaires, les colonnes se mettent à jour.

Ce n'est peut-être pas idéal, mais j'ai pas trouvé mieux.

Le fichier joint si tu veux tester.

@+ Piere

11gestcomptes.xlsm (227.49 Ko)
Le 14/06/2023 à 08:09

Bonjour

Félicitation...

A+ François

Le 14/06/2023 à 11:36

Bonjour,

Oups !
J'ai crié victoire trop vite. La manip fonctionne très bien dans l'UF_Ecritures, mais dans l'UF_Virement ça marche pour le compte Source et pas pour le compte Cible.

J'ai mis la macro dans un module en intégrant une boucle For Next sur les feuilles, mais Cible ne s'actualise toujours pas.

Sub Recopie() 'Recopie vers le bas les colonnes Solde et Solde Rapproché
    Dim Ws As Worksheet
    Dim DerCel As Range: Set DerCel = Cells.Find("*", , -4123, , 1, 2)
    For Each Ws In Worksheets
    Ws.Select
        If DerCel Is Nothing Then Exit Sub
        Range("N12:O12").Select 'Là où se trouve la cellule à recopier jusqu'à la fin du tableau
            With Selection
            If .Row < DerCel.Row Then
            .Resize(DerCel.Row - .Row + 1).FillDown
            End If
            End With
    Next Ws
End Sub

Une idée ?

@+ Pierre

9gestcomptes.xlsm (253.95 Ko)
Rechercher des sujets similaires à "saisir valeurs formulaires deux feuilles differentes"