VBA : Indirects et détermination de nom d'onglet

Bonsoir à tous et à toutes !

En tant que petit nouveau sur VBA, une petite difficulté me bloque dans l'avancement de mon modèle excel.

Dans mes macros, j'utilise le code suivant pour faire référence à mes cellules :

Worksheets("Nom d'onglet").Range("nom de cellule"). etc.

Il se trouve que dans mon modèle, les noms d'onglets vont changer régulièrement. Or mes macros n'adaptent pas le nom d'onglet en cas de changement, et donc plus rien ne marche.

Aussi, j'ai créé une cellule dans excel qui me donne le nom de l'onglet où elle se situe, et ceci pour chaque onglet.

Mais je ne parviens pas à écrire la fonction qui remplacera "Nom d'onglet" par le contenu de la cellule où le nom se situe...

J'ai tenté de nommer la cellule puis de l'appeler dans Worksheets() mais ça ne marche pas, et en fouillant sur le web, la formule Indirect est apparue. Autant je la connais sur excel, mais pas sur vba, et les sites donnant des exemples n'ont pas fourni de solution.

Savez-vous quelle est la syntaxe à utiliser pour avoir Worksheets(formule pour prendre la valeur d'une cellule fixée).Range. etc. ?

Le but est de pouvoir changer à souhait le nom des onglets, sans que ça ne fasse sauter les macros

Merci beaucoup pour vos conseils, et bonne soirée !

Bonsoir, Sheets(1) désigne la première feuille du classeur en partant de l'onglet le plus à gauche.

Si comme vous dites vous avez une feuille qui vous donne le nom des feuilles et leur position peut-être cela peut vous servir, mais sans fichier ni code...

vbMBHB

Bonsoir,

On va essayer d'éclaircir ton fouillis ! Il aurait été bon que tu illustres tes problèmes avec un classeur, c'était de nature à éviter des confusions.

Tu utilises :

    Worksheets("NomFeuil")

pour renvoyer une feuille. NomFeuil est le nom qui qui répond à la propriété Name de l'objet Worksheet.

Evidemment quand on en change périodiquement cela peut poser problème avec le code...

Mais toute feuille dispose aussi d'un nom de code (CodeName), qui peut être utilisée à la place et ne subira pas les mêmes variations que le nom.

Tu pourras constater dans l'éditeur VBA que toutes tes feuilles ont un nom suivi d'un second entre parenthèses. Ce dernier est le Name qui se modifiera selon le nom que tu attribues, le premier est le CodeName par définition plus stable.

Sa syntaxe d'utilisation est un peu différente :

    Feuil1.Range(...

C'est déjà une solution pour ton code qui t'évitera une gymnastique compliquée, car tu n'as aucune raison de changer ce nom de code qui n'apparaît pas publiquement.

Pour les plages (Range) quand tu parles de "nom de cellule" il y a ambiguïté. Si par là tu veux désigner par exemple :

    ....Range("G8")

G8 n'est pas un nom, c'est une référence de cellule, ou adresse (qui répond d'ailleurs à la propriété Address de l'objet Range). Cette référence n'est précise que reliée au nom de la feuille qui la contient :

    Worksheets("NomFeuil").Range("G8")...
'ou
    Worksheets("NomFeuil").[G8]  'notation compacte de la référence de cellule
'etc.

Mais si tu nommes ta cellule, par exemple MaCel. Le nom apparaîtra dans le Gestionnaire de nom qui indiquera que MaCel réfère à : =NomFeuil!$G$8

Si tu changes le nom de feuille, le Gestionnaire le modifiera également dans sa référence. Le nom donné reste toujours défini tant que tu ne supprimes pas des éléments qui le mettraient en cause, et il est unique dans le classeur.

C'est à dire qu'en utilisant :

    Range("MaCel")...
'ou
    [MaCel]...

dans ton code tu cibleras toujours la plage G8 de la feuille NomFeuil, et tu peux utiliser ce nom sans te référer à la feuille.

Ce qui constitue également une solution pour pallier aux changements de noms de feuilles.

Cordialement.

Un grand merci à vous MFerrand et vbMsgBoxHelpButton pour votre aide précieuse !

J'ai employé ta méthode : nous parlions bien de la même chose, à savoir de cellules nommées "MaCel" et non pas par leur références. Je voulais procéder comme ça, mais la syntaxe n'allait pas. Merci donc de m'avoir montré l'usage de ces [ ], ainsi que pour les explications

Pour celles et ceux que ça intéresse, le code à faire dans ce cas était juste Worksheets([MaCel].Value).Range, etc.

Je mets ce message en résolu

EDIT : voilà l'erreur corrigée

Attention en citant :

si tu écris Worksheet au lieu de Worksheets... tu auras une erreur !