Capter la valeur d'une list box pour trouver le nom d'un onglet

Bonjour à tous,

Je suis débutant sur VBA et je cherche désespérément une solution à mon problème... J'ai parcouru différents forums mais je n'ai pas réussi à trouver une solution qui me convienne.

Je me tourne donc vers vous en espérant que vous pourrez m'aider

Mon problème est très simple : J'ai 10 fichiers dans lesquels 12 onglets sont intégrés. Ces 12 onglets font référence au 12 mois d'une année.

Ex: mon workbooks("TEST1") contient worksheets("Janvier 2018"), la deuxième worksheets("Février 2018") et ainsi de suite jusqu'à Décembre 2018.

Les noms des onglets de mes 10 workbooks sont strictement identiques.

Ma macro effectue des copier/coller et des calculs pour rapatrier les données des 10 workbooks du mois en cours dans un autre classeur qui consolide les données des 10 fichiers. La macro fonctionne très bien, aucun problème de ce côté là.

Cependant, je n'ai pas trouvé un moyen de demander à la macro d'aller chercher les données dans l'onglet du mois en cours.

Ex: nous sommes en septembre 2018, j'ouvre mes 10 workbooks et je renomme l'onglet "Septembre 2018" en "MOIS". La macro recherche donc dans les 10 workbooks l'onglet "MOIS" et copie/colle les données :

Workbooks("REPORTmacro.xlsm").Worksheets("BUDGET").Range("B52").Value

_=Workbooks("TEST1.xlsx").Worksheets("MOIS").Range("C10").Value

Workbooks("REPORTmacro.xlsm").Worksheets("BUDGET").Range("B53").Value

_=Workbooks("TEST2.xlsx").Worksheets("MOIS").Range("C10").Value

J'aimerais donc avoir une listbox (via userform?) qui invite l'utilisateur à sélectionner le mois en cours et ainsi remplacer dans mon code le "MOIS" par la valeur sélectionnée dans la list box.

EX: l'utilisateur saisit via la listbox "Septembre 2018", alors le code de la macro s'adapte comme suit :

Workbooks("REPORTmacro.xlsm").Worksheets("BUDGET").Range("B52").Value

_=Workbooks("TEST1.xlsx").Worksheets("Septembre 2018").Range("C10").Value

Workbooks("REPORTmacro.xlsm").Worksheets("BUDGET").Range("B53").Value

_=Workbooks("TEST2.xlsx").Worksheets("Septembre 2018").Range("C10").Value

En bref, cela m'éviterait d'ouvrir chaque mois les 10 classeurs et de renommer l'onglet du mois en cours par "MOIS".

Je vous remercie par avance pour vos conseils et vous souhaite une très bonne journée

Romain

Bonjour,

Ton système me paraît bien compliqué... Enfin !

Tu mets en place ton Userform, et tu définis une fonction de ce type :

Function MoisEnCours()
    Dim mois$
    With UserForm1
        .Show
        mois = .listBox1.Value
    End With
    Unload UserForm1
    MoisEnCours = mois
End Function

Dans ta proécédure de consolidation, tu l'appelles pour recueillir le mois dans une variable String (mois par exemple !)

    mois = MoisEnCours()

Et dans ton code : ...Worksheets(mois)...

NB- Dans le code de ton Userform, tu ne décharges pas ce dernier, tu le masques. C'est la fonction qui le déchargera après récupération de la valeur de la ListBox.

Cordialement.

Bonjour MFerrand,

Merci pour ta réponse rapide En effet un système bien compliqué que personne ne veut changer pour le moment donc adaptation via macro

Merci pour ces conseils, c'est exactement ce que je souhaitais, tu viens de me faire économiser 10 minutes de mon temps chaque mois

Je passe le sujet en résolu

Bien cordialement

Bonsoir à tous,

Je ré-ouvre le sujet car je pense que je dois mal m'y prendre

J'ai bien créé le userform, et dans le userform, en cliquant sur le bouton "OK", j'ai rentré le code suivant :

Sub CommandButton1_Click()
    Dim mois$
    With UserForm4
        mois = .ListBox1.Value
    End With
    Unload UserForm4
    MoisEnCours = mois
End Sub

En cliquant sur "OK" le code est donc censé charger la valeur choisie. J'ai mis en PJ la screenshot de mon userform.

Dans mon code de consolidation, voici le code que j'ai mis au début:

Private Sub Macro_Reporting()
Dim mois As String
mois = CommandButton1_Click()

et dans mon code, j'appelle la feuille :

 Workbooks("FIESSO.xlsx").Worksheets(mois).Range("C10").Value

Je charge mon formulaire dans une procédure Sub juste avant avec le code suivant :

Sub Run_the_Macro()
Load UserForm4
UserForm4.Show

Et cette même procédure call Private Sub Macro_Reporting()

Quand je lance la macro, une erreur de compilation s'affiche et indique "Sub ou Function non définie". En PJ le screenshot du débogage

Savez-vous ce que j'ai loupé ?

Merci par avance pour aide précieuse

Je ne vois pas de fonction... Relis mon post et tiens-toi à ce que j'ai défini pour avoir un dispositif qui fonctionne.

J'ai bien rentré la function dans mon code de consolidation mais lorsque le userform s'affiche et que je clique sur "OK" rien ne se passe...

Voci le début de mon code dans lequel j'appelle ma function :

Sub Run_the_Macro()
Dim debut As Date, temps As Date, Fin As Date
debut = Time 
Call MoisEnCours
UserForm4.Hide
DoEvents
Application.Calculation = xlCalculationManual

et j'ai repris le code de la function que j'ai placé dans le module 1 et non dans le userform

Function MoisEnCours()
    Dim mois$
    With UserForm4
        .Show
        mois = .ListBox1.Value
    End With
    Unload UserForm4
    MoisEnCours = mois
End Function

Ce que je n'arrive pas à saisir c'est qu'il faut bien que je lui dise "si click sur OK, alors exécution de la function"

C'est cette ligne qui appellera la fonction :

    mois = MoisEnCours()

Et ton code Userform doit se limiter à :

Sub CommandButton1_Click()
    Me.Hide
End Sub

Cordialement.

Merci MFerrand

Mais ça ne fonctionne toujours pas... Je dois vraiment louper quelque chose

J'ai une sub principale qui appelle mes autres Sub :

Sub Launch_the_Macro()
Dim mois As String
mois = MoisEnCours()
DoEvents
Application.Calculation = xlCalculationManual
    Load UserForm1 'message d'attente
    UserForm1.Caption = "Please wait!"
    UserForm1.Show False
    UserForm1.Repaint
    DoEvents
    'Application.Cursor = xlWait 
    Application.ScreenUpdating = False l
    Call Clearcells
    Call OuvrirClasseurs
    Call Macro_Reporting
    Call FermerClasseurs

Dans ma sub "Macro_Reporting" se trouve les feuilles auxquelles je dois faire référence.

Private Sub Macro_Reporting()
Dim mois As String
mois = UserForm4.ListBox1.Value
    Workbooks("REPORTmacro.xlsm").Worksheets("BUDGETS").Range("B52").Value = Workbooks("FIESSO.xlsx").Worksheets(mois).Range("C10").Value

Et pourtant la macro débogue et souligne mois = UserForm4.ListBox1.Value dans ma Sub Macro_Reporting()....

Dans mon module j'ai bien inséré ta fonction :

Function MoisEnCours()
    Dim mois$
    With UserForm4
        .Show
        mois = UserForm4.ListBox1.Value
    End With
    Unload UserForm4
    MoisEnCours = mois
End Function

Essaie de comprendre :

ton Userform4 est dédié pour le renvoi de mois selon ta sélection,

la seule procédure qui doit l'appeler est la fonction !

la fonction étant en place de même que le Userform4, partout ailleurs, ayant besoin de définir le mois, tu le prélève dans une variable ainsi :

mois = MoisEnCours()

et tu t'en sers.

Mais il y a toujours quelque chose que je ne comprends pas... J'ai l'impression que la valeur la liste box n'est pas chargée... voici en PJ le message d'erreur : la macro débogue et m'indique qu'elle ne trouve pas "mois"... Je ne comprends vraiment pas ce qui manque...

Je n'ai pas changé le code de ma sub principale :

Sub Launch_the_Macro()
Dim mois As String
mois = MoisEnCours()
DoEvents
Application.Calculation = xlCalculationManual
    Load UserForm1 'message d'attente
    UserForm1.Caption = "Please wait!"
    UserForm1.Show False
    UserForm1.Repaint
    DoEvents
    'Application.Cursor = xlWait 
    Application.ScreenUpdating = False l
    Call Clearcells
    Call OuvrirClasseurs
    Call Macro_Reporting
    Call FermerClasseurs

Peut-être dois-je activer le workbook? Workbooks("FIESSO.xlsx").activate? Le fichier est bien ouvert, la macro ouvre automatiquement les fichiers

Tu définis bien ta variable mois dans la proc. Launch_the_Macro avec : mois = MoisEnCours(), mais rien de tel dans ta proc. Macro_Reporting : tu utilises une variable mois sans l'avoir initialisée (dans ta dernière citation) et dans la citation précédente tu le faisais par : mois = UserForm4.ListBox1.Value.

Dans les deux cas la variable mois ne pourra contenir qu'un chaîne vide ! Soit la valeur "" D'où une erreur tout à fait logique.

Peut-être dois-je activer le workbook?

JAMAIS ! N'ajoute pas des lignes de code inutiles à celles que tu as déjà ! J'ai pour l'instant laissé cet aspect de côté pour ne pas interférer avec le sujet traité...

Il convient dans l'immédiat de te rendre compte que initialisant une variable dans une procédure, elle l'est et est utilisable dans cette procédure, mais n'est pas définie dans les procédures que tu appelles à partir de cette dernière.

Il te faut donc soit l'initialiser à nouveau dans chacune des porcédures appelées où tu comptes l'utiliser, soit la passer en argument aux procédures appelées à partir de ta procédure appelante.

Mais son initialisation repose toujours sur la fonction définie à cet effet, uniquement.

Cordialement.

Merci MFerrand, tes explications sont très claires et en effet je comprends la logique

Cependant, je ne peux pas initialiser ma variable avec

mois = MoisEnCours()

dans ma sub Macro_Reporting car, si j'ai bien compris, le userform s'ouvrirait de nouveau pour demander de sélectionner la même information qui a déjà été demandée par la sub principale Launch_the_Macro. De plus, j'ai trois autres sub qui font appel à "mois". Cela signifierait que le formulaire s’ouvrirait 4 fois au cours du déroulement de la macro pour demander exactement la même information... Et en effet, j'ai testé, cela fonctionne très bien, mais le formulaire s'ouvre 4 fois

La solution réside donc dans ce que tu appelle les arguments. Cependant, je n'ai aucune idée de ce que c'est j'ai essayé de fouiller sur le net mais je ne vois pas... Je suppose que c'est un paramètre que je dois rajouter à la procédure "Call" ? Mais je n'ai aucune idée de comment m'y prendre...

Quelque chose comme ceci? Placé dans ma sub principale :

Call Macro_Reporting & mois=UserForm4.ListBox1.Value

Exemple :

Déclaration de procédure :

Sub Macro_Reporting(mois As String)

Appel de la procédure :

    Macro_Reporting mois

NB- le nom de la variable et le nom de l'argument sont ici identiques, mais ils pourraient être différents, cela reviendrait au même.

NB2- Si tu utilises Call (c'est que tu aimes écrire plus que nécessaire ! ), la syntaxe oblige à mettre les arguments entre parenthèses :

    Call Macro_Reporting(mois)

Cordialement.

Merci beaucoup cela fonctionne parfaitement et j'ai à peu près compris le cheminement logique mais je vais me replonger dedans à tête posée

J'aurais une tête dernière question, pour ma compréhension, concernant la fonction :

Function MoisEnCours()
    Dim mois As String
    With Pickuplist
        .Show
        mois = Pickuplist.ListBox1.Value
    End With
    Unload Pickuplist
    MoisEnCours = mois
End Function

A quoi sert la dernière ligne "MoisEnCours =mois"?

C'est la ligne-clé qui caractérise une fonction, celle qui renvoie le résultat : NomFonction = résultat, et qui fait que lorsque tu appelles une fonction pour recueillir un résultat, tu puisses effectivement l'obtenir.

Cordialement.

Merci beaucoup pour ton temps, tes conseils, tes explications et ta patience

Tout fonctionne très bien et le plus important : je comprends le cheminement

Je te souhaite une agréable soirée et sûrement à bientôt

Romain

Bonne continuation.