XL 2013 RollOver sur cell sans boucle peut on modifier le lien avec une liste(array en VBA)

[patricktoulon]

Bonjour a tous
suite a une réponse qui a été donnée sur le forum avec un lien vers youtube
j'ai testé l'astuce et elle fonctionne
cependant je me demande si l'on peut pas remplacer la liste de ref par un array dans la fonction
en effet
voila l'astuce

1. je fait une liste en A2:A8( j'en ai fait un tableau structuré mais c'est pas obligé)
2. je vais prendre une cellule ailleurs et je vais lui donner le nom de Ref_cel
3. je vais prendre une autre plage de même dimension que ma liste ayant les même valeurs
4. dans la première cellule de cette plage je vais y mettre cette formule =SIERREUR(LIEN_HYPERTEXTE(getvalCel(B2));B2) et je l’étends sur les 7 lignes de cette plage
5. dans un module standard je lui met cette fonction

Public Function getvalCel(nom_cellule As Range)
Range("Ref_Cel").Value = nom_cellule.Value
End Function

j'ajoute une mise en forme conditionelle à cette plage

et voila le resultat

donc sa fonctionne
j'ai bien l'impression que même si dans cette plage de reception ce n'est pas des liens , c'est bel et bien le fait qu'il y est la fonction lienhypertext qui fait le repeat et donc fait office d'event mouse move

deux questions

1° peut on dans ce contexte dans la fonction par exemple remplacer la plage liste originale par un array dans la fonctions
moyennant des modifs dans la formule utilisant l'indexage par la fonction ligne() par exemple sur un array

2° et remplacer la cellule D1 par un name

 

[Lolote83]

Bonjour @patricktoulon ,
Content de te retrouver après ces vacances ou je vois que de ton coté tu ne chômes pas.
Je suis désolé mais je n'ai pas tout compris dans ta demande.
Tu voudrais t'affranchir de la liste supplémentaire et n'avoir que le tableau structuré ?????
Dans l'attente de ton retour
@+ Lolote83

 

[patricktoulon]

re
Bonjour lolote83
content de te retrouver
non c'est le contraire je voudrais me passer du tableau structuré qui est ma liste de base
pour cela j'ai dans l'idée de faire
dans la plage D13😀19 du fichier joint
à la place de
=SIERREUR(LIEN_HYPERTEXTE(getvalCel(B2));B2)
je ferais un truc du genre
=SIERREUR(LIEN_HYPERTEXTE(getvalCel(ligne(A1)));valoch(ligne(A1))

et dans le module standar on modifie la fonction et on crée la valosh

Public Function getvalCel(index)
x = Array("titi", "riri", "fifi", "loulou", "toto", "filou", "tata")
Range("Ref_Cel").Value = x(index - 1)
End Function

Function valosh(index)
x = Array("titi", "riri", "fifi", "loulou", "toto", "filou", "tata")
valosh = x(index - 1)
End Function

voilà et ça fonctionne
reste plus qu'a voir si on peut remplacer la cellule D1 nommée "Ref_Cel" par un name tout court
pour la mise en forme conditionnelle
j'ai bien essayé mais ca ne fonctionne plus j'ai peut être mal fait

 

[Lolote83]

Re bonjour,
Effectivement, on peut s'affranchir du tableau structuré comme dans ton 2ème exemple.
Chez moi, tout fonctionne, même la MFC avec le nom défini "Ref_Cel"
L’inconvénient que j'y trouve est que les valeurs sont inscrites en dur dans le code et deplus, doublées (une fois dans la fonction GetValCel et une fois dans ValOsh) mais sinon, tout est OK pour moi
J'ai essayé de déclarer un seul array mais je n'y suis pas arrivé (je maitrise mal ce sujet).
@+ Lolote83

 

[patricktoulon]

ben tu peux faire

Public Const x = "titi,riri,fifi,loulou,toto,filou,tata"
Public Function getvalCel(index)
Range("Ref_Cel").Value = Split(x, ",")(index - 1)
End Function

Function valosh(index)
valosh = Split(x, ",")(index - 1)
End Function

mais j'aimerais bien faire un truc du genre

Public Const x = "titi,riri,fifi,loulou,toto,filou,tata"
Public Function getvalCel(index)
names("Ref_Cel").refersto = Split(x, ",")(index - 1)
End Function

et dans la MFC
=[Ref_Cel]

mais je dois rater quelque chose un truc qui m'échappe

 

[laurent950]

Bonsoir @patricktoulon

J'ai modifié le code :

l'idée est de stocker dans une variable collection, l'Item qui était stocké dans le tableau structuré :
soit si dans ce tableau structuré en Cellule D13 il y avait la valeur de titi
Alors l'adresse D13 sera la clé de cette collection qui ramène à la valeur titi
plus besoin de tableau structuré.

Dim LiensDict As New Collection
Sub InitialiserCollection()
    ' Ajoutez vos liens et leurs correspondances ici
    Set LiensDict = Nothing
    LiensDict.Add "titi", key:="D13"
    LiensDict.Add "riri", key:="D14"
    LiensDict.Add "fifi", key:="D15"
    LiensDict.Add "loulou", key:="D16"
    LiensDict.Add "toto", key:="D17"
    LiensDict.Add "filou", key:="D18"
    LiensDict.Add "tata", key:="D19"
End Sub

Ensuite il faut Modifier la fonction,
Il faut Noté dans cette cellule active
Exemple titi (en y incluant cette fonction que tu as faite)
=SIERREUR(LIEN_HYPERTEXTE(getvalCel(D13); "Cliquez ici") & " titi"; A1 & " titi")
la modification
en D13, c'est bien la cellule active (est donc la clé pour cette collection, qui renverra à l'item)
ensuite en A1 (la je suis obligé de pointer vers une cellule, mais une seule !)
Pour écrire titi, l'astuce est dans la fonction que j'ai modifiée.

Function getvalCel(nom_cellule As Range) As String
    InitialiserCollection
    ' Renvoyez la valeur de la cellule
    Range("Ref_Cel").Value = LiensDict.Item(CStr(nom_cellule.Address(0, 0)))
End Function

C'est un début de solution, car je pense que ce code peut être encore amélioré.
C'est possible aussi de créer cette fonction avec VBA est de la coller dans la cellule
avec l'item prédéfinit.

 

[patricktoulon]

Bonsoir laurent
tiens donc qu'est ce que c'est que ce machin ?
je vais examiner ça
edit
ok j'ai bien le rollover actif mais pas la couleur du rollover
et c'est lourd si je vais trop vite avec la souris il y a des ratés
j'ai remis la MFC mais la couleur ne viens toujours pas au survol de la souris

j'avoue je pige pas ta formule
=SIERREUR(LIEN_HYPERTEXTE(getvalCel(D13); "Cliquez ici") & " titi"; A1 & " titi")

c'est quoi ce "cliquez ici..."
et pourquoi l'argument recherché est en dur dans la formule ?

de plus tu initialise ta collection a chaque déclenchement de lien (bouffe de la mémoire et du proc)

intéressant ton ébauche
déjà parce que le rollover fonctionne mais j'ai pas la couleur MFC même après l'avoir mis ça ne fonctionne pas

mais j'avoue que tu me fait toujours du grand art dans la démesure (j'adore)
l'exercice de style est à la hauteur de ta marque de fabrique
mais je n'en pige pas une miette tout du moins l'intérêt de passer de cette facon

bon dans tout les cas quand même c'est pas bon
1 les données écrites en dur dans la formule
2 dans la partie en rouge tu m'a perdu
("=SIERREUR(LIEN_HYPERTEXTE(getvalCel(D13); "Cliquez ici") & " titi"; A1 & " titi")")
3 l'exercice est justement de se passer de cellule

mais tu a droit à mon respect tu me met toujours le cerveau à l'envers

essaie de reprendre ma version 2 et fait la à ta façon (avec ta collection)

edit 2
ca y est je pige un epu ( à peine)
en fait tu te sert des adresses de cell pour tes item de cle dans ta collection
ca ok d'accords à la limite oui

suite à la prochaine chose que je comprends

 

[patricktoulon]

re
à écrire en dur je simplifie
=SIERREUR(LIEN_HYPERTEXTE(getvalCel(D13) );"titi")
et je fait pareille pour toute les lignes

 

[laurent950]

re @patricktoulon

Il apparait #NOM dans les cellule D13😀19 (Faire un double clique dans chaque cellule) pour que la formule apparaisse correctement.

Dim LiensDict As New Collection
Sub InitialiserCollection()
    ' Ajoutez vos liens et leurs correspondances ici
    Set LiensDict = Nothing
    LiensDict.Add "titi", key:="D13"
    LiensDict.Add "riri", key:="D14"
    LiensDict.Add "fifi", key:="D15"
    LiensDict.Add "loulou", key:="D16"
    LiensDict.Add "toto", key:="D17"
    LiensDict.Add "filou", key:="D18"
    LiensDict.Add "tata", key:="D19"
End Sub
Function getvalCel(nom_cellule As Range) As String
    InitialiserCollection
    ' Renvoyez la valeur de la cellule
    Range("Ref_Cel").Value = LiensDict.Item(CStr(nom_cellule.Address(0, 0)))
End Function

Sub CreerFormules()
    Dim ws As Worksheet
    Dim cell As Range
    Dim key As Variant
    Set ws = ThisWorkbook.Sheets("Feuil1")
    For Each cell In ws.Range("D13:D" & ws.Cells(LiensDict.Count, "D").End(xlUp).Row + (12 + LiensDict.Count - 1))
        key = " " & LiensDict.Item(CStr(cell.Address(0, 0)))
        cell.Formula = "=SIERREUR(LIEN_HYPERTEXTE(getvalCel(" & cell.Address(0, 0) & "), ""Cliquez ici"") & """ & key & """, A1 & """ & key & """)"
    Next cell
End Sub

 

[RyuAutodidacte]

Coucou mon petit Patrick 😉

j'essaie de résoudre ton problème …
Est ce que cela te conviendrait (voir Feuil2)

 

[patricktoulon]

re
bonjour a tous
@RyuAutodidacte
tu fait a peu près la même chose que moi mais avec une formule plus compliqué
et le soucis c'est que il y a toujours la ref qui est une cellule
et je voudrais que ce soit un names
si c'est possible car j'ai toujours pas la réponse

@laurent950
alors toi tu te déchaine 🤣
maintenant tu me fait les formule dynamico

alors le projet est :

Pour afficher ce contenu, nous aurons besoin de votre consentement pour définir des cookies tiers.
Pour plus d'informations, consultez notre page sur les cookies.

Accepter les cookies de tiers

 

[patricktoulon]

bon ben je vous ai perdu
bon dans tout les cas la référence a la plage elle même comme je m'en doutais crée une circulaire
apres finalement quand on va au plus simple
on ecrit la valeur si le link est erreur(ce qui est forcement le cas de toute facon) c'est le but de la manoeuvre

donc voila ma formule
=SIERREUR(LIEN_HYPERTEXTE(rollover(1));"titi")
on voit bien ici "titi" écrit en dur dans la formule
et la fonction c'est tout simplement ca

Public Function RollOver(x As Long)
Range("Ref_Cel").Value = Application.Caller
End Function

et pour le model avec le name susnommé"Ref_2"
=SIERREUR(LIEN_HYPERTEXTE(rollover2(1));"titi")

Public Function RollOver2(x As Long)
Names("Ref_2").Value = Application.Caller.Value
End Function

mon problème maintenant c'est que je n'arrive pas a étendre la mise en forme a la ligne(ou sur plusieurs colonnes ou alors sur les autres colonne mais la colonnes des noms j'ai un effet bizarre

 

[RyuAutodidacte]

bon ben je vous ai perdu

🤣
Je suis au taf 🤣
Crois tu vraiment que je vais faire du passe temps devant mes collègues en open space 🤣
😉

 

[RyuAutodidacte]

Re @patricktoulon et tous

Déjà merci de m'avoir fait découvrir Application.Caller que je n'avais jamais utilisé auparavant
(tu sais ou je l'ai vu la 1ère fois … ca arrive bientôt la suite 😉)

Bon arrête de dire que les formules que j'ai donné son compliquées 🤣

Tu connais INDEX EQUIV des incontournables, ici INDEX en utilisation simple :

' la base
INDEX(PLAGE;LIGNE;COLONNE)

'ce qui donnne en dur :
INDEX({"titi";"riri";"fifi";"loulou";"toto";"filou";"tata"};LIGNES(D$9:D9);1)

' ici point de départ de ta ligne et tu étire la formule vers le bas :
LIGNES(D$9:D9) = 1 ' LIGNES(D$9:D10) = 2 … etc
' Le 1 pour la colonne indexé

' tu pourrais remplacé ceci {"titi";"riri";"fifi";"loulou";"toto";"filou";"tata"}
 => par une plage

Je ne suis pas pour noter titi …etc en dur car c'est pas malléable
Car si tu les mets dans ta formule autant de faire une liste (même sans TS)
- en définissant la plage … ou
- via une plage dynamique (en formule) … ou
- via un champ nommé (en plage dynamique, donc formule sur le champ nommé)
ce que fait ma formule

tu pourras alors mettre denise …tartampion …etc et même changer les données ta plage
Ex:

Donc voilà sur quoi est ma préférence (pour l'instant) cf fichier

plus de miss clic … à vos lecture

 

[RyuAutodidacte]

@patricktoulon
PS : Dans le cas ou tu le fais en dur comme cici :

{"titi";"riri";"fifi";"loulou";"toto";"filou";"tata"}

et pas plus de 20 "noms" (toto etc)

tu peux le mettre dans la formule est l'étiré

PS 2 : au cas où … dans les option d'excel :