lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

[emwaj]

Bonjour

J'ai fait un fichier de terminologies pour mes études de traductologie.
Il contient un onglet pour chaque thème et dans le futur il aura bcp bcp d'onglets (des dizaines)

dans le premier onglet (disons nommé "general") il y a une liste des thèmes.

Je souhaite faire deux choses (qui je pense sont distinctes l'une de l'autre)

Premièrement:
lorsque je clique sur un des thèmes situé dans la colonne A, ça me mène à l'onglet désiré.

J'ai une macro qui fonctionne, bien qu'un peu tordue. Cependant je suis obligé de la recopier pour chaque thème et l'adapter à chaque ligne (A7 , A8, A9, A10 A11 A12, etc) et étant donné que j'ai des dizaines de thèmes, presque 60, je ne vais pas m'amuser à recopier cette macro 60 fois....
Comment puis je la corriger afin qu'elle s'adapte automatiquement à chaque ligne, et que si par exemple je clique sur A87 automatiquement elle comprend que je veux aller vers l'onglet qui est nommé selon le nom inscrit dans la cellule A87?

La voici :

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$7" Then GOTOSHEET
End Sub

Private Sub GOTOSHEET()
Worksheets(Range("A7").Value).Visible = True
Worksheets(Range("A7").Value).Activate
Worksheets("shaar").Visible = False
End Sub

Deuxièmement:

chaque onglet est nommé d'après une valeur inscrite dans les cellules de la colonne A de l'onglet "general"

En plus des thèmes déjà existants, je voudrais faire qu'à partir par exemple de la cellule A22 vers le bas, à chaque fois que j’écris un nouveau mot dans une cellule à la prochaine ligne, cela crée automatiquement un nouvel onglet selon un onglet modèle existant.
Mais pas seulement le créer: en fait le copier avec les titres du tableau qui est dans le modèle.
et le plus important, faire que ce nouvel onglet créé porte le nom de la valeur qu'on a rentré dans la liste de l'onglet "general" et que la cellule D5 de ce nouvel onglet soit nommée selon cette même valeur.

exemple:
dans la cellule A74 de l'onglet "general", j'écris le mot "médecine", et immédiatement se crée un nouvel onglet, avec dans sa cellule D5 le mot "médecine" et son nom est écrit pareil , cad "médecine".

avec processus inverse, si j'efface dans la colonne A de l'onglet "general" un terme, s'effacera aussi l'onglet du même nom.

Je sais que c'est compliqué mais j'ai cru voir que c'est possible. Je n'ai seulement pas réussis à trouver comment exactement

merci d'avance ....

 

[Grand Chaman Excel]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

Bonsoir emwaj,

Vois si ça te convient dans le fichier joint :

(le code est dans la Feuil1)

Option Explicit
Dim sNomFeuilleMemoire As String

'Se rend à l'onglet quand on double clique sur cellule de la colonne A
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim i As Integer
    On Error Resume Next
    If Target.Column = 1 Then   'uniquement si on est dans la colonne
        HideAll   'on cache toutes les feuilles sauf si nommé Général
        Sheets(Target.Value).Visible = True
        Sheets(Target.Value).Activate
    End If
    On Error GoTo 0
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NomFeuille As String
    
    Application.ScreenUpdating = False
    On Error Resume Next
    'si on se trouve dans la colonne A, ligne 22 et plus et que la cellule n'est pas vide
    If Target.Column = 1 And Target.Row > 22 Then
        NomFeuille = Target.Value
        
        If Target.Value <> "" Then 'Ajoute une feuille
            If WorksheetExists(NomFeuille) Then
                MsgBox "Cet onglet existe déjà !"
                Exit Sub 'on ne fait rien si la feuille existe déjà
                
            End If
            ShowAll 'il faut montrer toute les feuille sinon la mauvaise est renommée
            Sheets("Modèle").Copy After:=Worksheets(Worksheets.Count)
            Sheets(Worksheets.Count).Name = NomFeuille
            Sheets(NomFeuille).Range("D5") = NomFeuille
            
        ElseIf Target.Value = "" Then
            Application.DisplayAlerts = False
            Sheets(sNomFeuilleMemoire).Delete
            Application.DisplayAlerts = True
        End If
    End If
    On Error GoTo 0
    HideAll
    Application.ScreenUpdating = True
End Sub

'Affiche toutes les feuilles
Sub ShowAll()
    Dim i As Integer
    For i = 1 To Worksheets.Count
        Sheets(i).Visible = True
    Next i
End Sub

'Cache toutes les feuilles sauf la 1re
Sub HideAll()
    Dim i As Integer
    For i = 1 To Worksheets.Count   'on cache toutes les feuilles sauf si nommé Général
        If Sheets(i).Name <> "Général" Then
            Sheets(i).Visible = False
        End If
    Next i
End Sub

' Vérifier si feuille existe
Private Function WorksheetExists(sNom As String) As Boolean
    Dim i As Integer
    WorksheetExists = False
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = sNom Then WorksheetExists = True: Exit Function
    Next i
End Function

' Garde en mémoire le nom de la feuille (pour effacer onglet)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 And Target.Row > 22 Then
        sNomFeuilleMemoire = Target.Value
    End If
End Sub

A+

 

[emwaj]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

voici le fichier

 

[emwaj]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

Tout d'abord un GRAND MERCI !!! vous m'avez vraiment vraiement bcp aidé!! et merci pour votre réponse si rapide!!!

J'ai adapté un peu le code à mon document (que je joins). Mais à chaque fois que je crée un nouveau thème dans la liste, ça m'ouvre la page modèle.
Je voudrais, si c'est possible, qu'après avoir écris un thème dans la liste de l'onglet "shaar", ça m'ouvre directement la page crée (afin que je puisse commencer à rentrer des infos dedans).
et aussi que lorsque l'onglet désiré est ouvert, il n'y a que lui de visible (pour le moment si vous double-cliquer sur un des thèmes de la liste, ce même thème s'affiche dans son onglet, mais l'onglet principal "shaar" avec la liste reste visible, ce que je ne veux pas qu'il soit).

Ensuite je souhaiterais savoir si c'est possible que la case D5 de chaque onglet créé corresponde à ce qui est écris dans la ligne correspondante dans la colonne D de l'onglet "shaar". que si j'écris par exemple à la ligne 10 le mot "education", se crée le nouvel onglet avec dans sa cellule C5 le mot inscrit dans la case A10 et avec dans sa cellule D5 le mot inscrit dans la cellule D10, et ainsi de suite pour chaque ligne de la liste. Si plus tard je veux changer le nom du thème dans la liste (colonnes A et/ou D) , que cela change automatiquement les cellules C5 et D5 de l'onglet concerné (bien entendu s'il le faut que ça mette aussi à jour le nom de l'onglet)

Enfin, important, placer si possible un bouton sur lequel j'appuie, il me demande quel onglet je désire effacer, j'indique en réponse dans la fenetre de message le numero de la ligne qui correspond au theme dans la liste (par exemple, si je veux effacer le theme "education" j'ecrirais "10" en reponse). Puis je reçois une seconde fenêtre de vérification qui me demande si je suis certain de vouloir effacer ce thème (pour ne pas risquer d'effacer tout un onglet avec pleins de termes sans le vouloir).
Puis en cliquant sur OK, s'effectuent 2 actions: primo- la ligne en elle même dans la liste de l'onglet "shaar" s'efface (delete et non pas clear pour ne pas rester avec une ligne vierge dans la liste), et bien sur l'onglet correspondant à cette même ligne/thème disparait et s'efface, (comme si j'ai fait "delete" avec la souris sur le bas de l'onglet).


Voila en gros, ce sont certes des détails car le gros de l'idée est déjà fait mais ils sont assez importants pour moi, cela serait gentil si vous réussissiez à m'aider avec cela. J'espère que c'est possible..

Eric.

 

[Grand Chaman Excel]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

Bonjour emwaj,

J'espère que c'est possible..

... avec Excel, tout est possible... ou presque

Alors, voici ton fichier en retour, j'ai fait quelques tests et je crois que tout fonctionne correctement.


A+

 

[emwaj]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

Oh merci bcp!!!! Vous êtes vraiment très gentil de m'aider!! Merci!

Le seul et dernier problème visiblement c'est que maintenant, pour une raison que j'ignore, la macro de recherche de termes ne fonctionne plus et je reçois un bug
Si vous cliquez sur une des options de recherche c'est le même problème. Pourquoi cela arrive-t-il ?

 

[Grand Chaman Excel]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

Bonsoir,

C'est parce que j'ai mis "Option Explicit" dans le 1re ligne du code.
Cette instruction oblige à déclarer toutes les variables dans les macros (Dim X as string, Dim I as Integer, ...). Il s'agit d'une bonne pratique de programmation en VBA.
Dans ce cas-ci, la variable "recherche" n'est pas définie.

2 options :
a) effacer la ligne Option Explicit (pas une bonne pratique)
b) définir recherche dans la macro (Dim recherche as string)

A+

 

[emwaj]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

D'accord, j'ai corrige ceci, merci bcp!!

J'eesaie de rajouter que dans la fenêtre de résultats de la recherche de termes, en plus du mot et de sa tradiction, apparait entre crochets aussi le nom de l'onglet dans lequel chaque terme se trouve. Et si plusieurs termes apparaissent dans la fenêtre de résultats, et qu'ils proviennent d'onglets différents, alors à côté de chaque ligne apparaitra le nom de l'onglet duquel le terme a été tiré.
Le nom de l'onglet se trouve pour l'exemple dans la cellule D5 de chaque onglet.
J'ai essayé de rajouter range("D5").value mais ca ne donne rien, pourquoi? comment puis-je demander à excel de rajouter entre les crochets le mot qui est inscrit dans la cellule D5?

voila la macro en entier:

Sub ChercherFrench()

Dim sh As Worksheet, recherche As String, c As Range, r As Range, Adresse$, flag As Boolean, Texte$
recherche = InputBox("Entrez le terme en Francais dont vous souhaitez rechercher la signification en Hebreu")
If recherche = "" Then Exit Sub
For Each sh In Sheets
Set c = sh.Columns(3)
With c
Set r = .Find(recherche)
If Not r Is Nothing Then
Adresse = r.Address
flag = True
Do
Texte = Texte & r & " --> " & r.Offset(0, 2) & ")" & " " & r.Offset(0, -2) & ")" & " " & "[" & " " & (Range("D5").Value) & "]" & vbCrLf
Set r = .FindNext(r)
Loop While Not r Is Nothing And r.Address <> Adresse
End If
End With
Next
If Not flag Then
MsgBox "rien trouvé !"
Else
MsgBox Texte
End If
OptionButton1.Value = False
End Sub

 

[Grand Chaman Excel]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

Bonjour,

C'est parce que la macro retourne la valeur de la cellule D5 de l'onglet actif qui appelle la macro, c'est-à-dire ton onglet "shaar". En l'occurrence, D5 est vide dans cet onglet.

Il faut donc spécifier dans quel onglet prendre la valeur de la cellule D5 :

Ainsi :

sh.Range("D5").Value

Et en simplifiant un peu ta ligne de code, ça devient :

Texte = Texte & r & " --> " & r.Offset(0, 2) & ") " & r.Offset(0, -2) & ") [ " & (sh.Range("D5").Value) & "]" & vbCrLf

A+

 

[emwaj]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

merci bcp!!

 

[emwaj]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

Bonjour!

Encore 2 petits trucs en plus , car en travaillant dans la pratique avec le fichier je découvre d'autres besoins.
Si vous pouvez m'aider aussi dans cela ça serait génial, sinon, pas grave du tout, car l'essentiel fonctionne:

1/ dans la macro suivante de recherche qui me cherche la traduction d'un mot, j'ai fait qu'elle puisse me montrer un mot et encore une autre mot, tiré d'une autre cellule que j ai désigné, et ce second mot apparaît entre parenthèses.
Cependant, pas toujours la seconde cellule est remplie, et donc quand c'est une cellule vide, il y a juste deux parenthèses ) ) inversées partiellement sans rien.
Est ce possible d'arranger cela, afin que si la cellule - dans ce cas : r.Offset(0, 1) - est vide alors les parenthèses n’apparaîtront pas dans la fenêtre de résulats?




Sub ChercherHebrew()

Dim sh As Worksheet, recherche As String, c As Range, r As Range, Adresse$, flag As Boolean, Texte$
recherche = InputBox("which word do you want to search?")
If recherche = "" Then Exit Sub
For Each sh In Sheets
Set c = sh.Columns(1)
With c
Set r = .Find(recherche)
If Not r Is Nothing Then
Adresse = r.Address
flag = True
Do
Texte = Texte & r.Offset(0, 1) & ") " & r & ") " & " ----> " & r.Offset(0, 2) & " [ " & (sh.Range("D5").Value) & " ]" & vbCrLf

Set r = .FindNext(r)
Loop While Not r Is Nothing And r.Address <> Adresse
End If
End With
Next
If Not flag Then
MsgBox "Word not found!"
Else
MsgBox Texte
End If
OptionButton2.Value = False
End Sub


2/ dans cette même fenêtre de résultats, je reçois la traduction du terme recherché (ici: r.Offset(0, 2) ) , ainsi qu'entre crochets le nom de l'onglet dans lequel ce terme se trouve ,ici: sh.Range("D5").Value

Est-ce possible de faire que le nom de l'onglet qui apparaît entre crochets, soit lié avec un lien hypertexte par exemple directement vers l'onglet en question?

Je m'explique: si j'ai un mot ou plusieurs mots dans la fenêtre de résultats, je voudrais qu'en cliquant directement dans la fenêtre sur le nom de l'onglet à côté de la traduction recherchée, je sois transféré vers la cellule de la colonne A de ce même onglet, dans laquelle a été trouvé le mot recherché à l'origine. En effet lorsque j'aurai une liste de 200 mots dans chaque onglet, ça ne me suffira pas d'être transfere à l'onglet car ensuite je perdrai du temps à chercher dans la liste le mot en soi.
Quant à la fenêtre de résultats, elle restera simplement affichée jusqu’à ce que je la ferme moi-même (comme elle est maintenant).

Voilà voilà.. Ce sont deux choses qui me sont apparues comme nécessaires par la pratique.. J'espère que ce n'est pas quelque chose de trop compliqué à faire... Merci d'avance!!

Eric

 

[emwaj]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

je viens aussi de faire attention que la macro d'effacement de ligne et d'onglet de fonctionne plus, c'est bizarre car hier ça marchait, mais plus aujourd'hui!!

Apres que je clique sur YES je reçois une erreur : "Subscript out of Range ". Pourquoi?

Voici la macro (la meme que celle que m'avez conseille):
Public Sub EffacerOnglet()
Dim sRep As String, sNom
sRep = InputBox("Numéro de ligne à effacer ?")

sNom = Cells(sRep, 1)
If MsgBox("Êtes vous certain de vouloir effacer l'onglet " & vbCrLf & _
sNom & vbCrLf & "ainsi que toutes les données qu'il contient ?", vbCritical + vbYesNo) = vbYes Then
'Application.DisplayAlerts = False
Sheets(sNom).Visible = True
Sheets(sNom).Delete
HideAll
Application.EnableEvents = False
Sheets(sFeuilleMenu).Rows(sRep).EntireRow.Delete
Application.EnableEvents = True
' Application.DisplayAlerts = True
End If

End Sub

 

[Grand Chaman Excel]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

Bonjour emwaj,

Pour la 1re question :

If r.Offset(0, 1) <> "" Then
    Texte = Texte & r.Offset(0, 1) & ") " & r & ") " & " ----> " & r.Offset(0, 2) & " [ " & (sh.Range("D5").Value) & " ]" & vbCrLf
Else
    Texte = Texte & " ----> " & r.Offset(0, 2) & " [ " & (sh.Range("D5").Value) & " ]" & vbCrLf
End If

Pour ce qui est de la 2e question, ce n'est pas possible d'ajouter liens hypertextes à du texte qui apparaît dans un Msgbox. Il faudrait plutôt explorer du côté des formulaires (Userform). Ce pourrait être un formulaire qui apparaît et affiche les résultats obtenus par la recherche. Un peu plus complexe à programmer toutefois.

Pour ce qui est de ta dernière question, à quelle ligne est-ce que le code arrête exactement? (elle devrait être en jaune dans la fenêtre de code). Ce genre d'erreur survient quand tu atteints une "limite" de Excel comme par exemple si tu essaies d'effacer un onglet qui n'existe pas...

A+

 

[emwaj]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

pouvez vous m'expliquer comment ça fonctionne avec les (0,1), (0,2), (0, -1) etc? J'essaie par tâtonnement pour adapter à mon code mais je n'arrive pas à comprendre comment on compte les cellules de cette façon...

en ce qui concerne le problème pour effacer, il n'y a pas de bug et donc il n'y a pas de ligne coloriée en jaune. C'est pour cela que je suis perdu avec cela. J'essaye d'effacer une ligne et un onglet existant...

 

[Grand Chaman Excel]

Re : lien entre cellule d'une liste et onglet correspondant + créer onglet avec nom

Bonjour,

La fonction .offset(x,y) permet de décaler de x lignes et y colonnes à partir d'une cellule.
Dans le code, r correspond à une cellule trouvée (disons A2). Alors r.offset(0,1) retourne la valeur de la cellule qui se trouve 1 cellule à droite de A2 donc B2.
Autres exemples :
r.offset(5,5) = cellule qui se trouve 5 lignes en bas et 5 colonnes à droite de r
r.offset(3,-2) = cellule qui se trouve 3 lignes en bas et 2 colonnes à gauche de r
r.offset(-1,-4) = cellule qui se trouve 1 ligne en haut et 4 colonnes à gauche de r

Pour ce qui est du bug... sans fichier avec l'exemple précis qui ne fonctionne pas, impossible de deviner c'est quoi le problème....

Merci.