Création d'une liste dans excel avec VBA

Version imprimable

Bonjour,

Je voudrais faire une liste depuis des cellules d'Excel, mais cette plage de cellules est variable.
Si je sélectionne ma plage maximale, j'ai dans la liste des sélections vides,malgré le "ignore blank = true"

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
Const lst As String = "=$C4:$C10"
Range("G5").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=lst
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Date"
        .ErrorTitle = "Erreur"
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

06/06/2016, 11h30
Philippe Tulliez

Bonjour,
Pour rendre dynamique une liste, il suffit de convertir la plage de cellules la contenant en tableau.
Après avoir sélectionné cette plage, il faut cliquer sur la commande Tableau du groupe Tableaux de l'onglet [Insertion] ou la commande Mettre sous forme de tableau du groupe Style de l'onglet [Accueil]

Bonjour,

En fait je ne peux pas simplement faire ça puisque je me sers ensuite de la valeur saisie dans la liste pour faire une recherche dans le fichier.
En fait, la simple utilité de cette liste est de pouvoir naviguer dans le classeur Excel en cherchant uniquement les noms des onglets. Le morceau de code que j'ai mis vient compléter celui-ci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Dim Feuille As Worksheet Dim cherche As String Private Sub CommandButton1_Click() cherche = Range("G5").Value For Each Feuille In Worksheets If InStr(1, Feuille.Name, cherche) > 0 Then Sheets(cherche).Activate End If Next End End Sub

06/06/2016, 11h56
Philippe Tulliez

Bonjour,
Je ne comprends pas la raison qui t'empêche de créer une liste dynamique.
De plus le code que tu viens de publier me semble contenir une boucle inutile et pourquoi utiliser Instr alors que la variable Cherche semble avoir le nom complet de la feuille au vu de la ligne

Code:

Sheets(cherche).Activate

Cette ligne suffit amplement

Code:

Sheets(Range("A5").Value).Activate
06/06/2016, 12h07
MrcRafale

1 pièce(s) jointe(s)

Je veux créer ma liste dynamique en G5, et les données sont en C3. Je ne comprends pas comment je fais, une fois que j'ai créé le tableau, pour dire à G5 "prends toutes les valeurs du tableau d'à côté sauf les cases vides", puisque ce tableau n'a aucune référence
Pièce jointe 211921
06/06/2016, 12h29
Philippe Tulliez

Bonjour,
Pourquoi placer la liste sur la même feuille
Tu crées une feuille où tu places test paramètres dont par exemple cette liste de dates les unes après les autres (par exemple A1:A10 où tu mets le titre en A1. Tu nommes la plage A2:A10 à l'aide du gestionnaire des noms tu transformes ensuite la plage A1:A10 en tableau (en n'oubliant pas de cocher l'option Mon tableau comporte des en-têtes)
Dans l'outil Validation de données, tu fais référence au nom que tu as donné et tu pourras ajouter et/ou supprimer des éléments de ta liste comme tu l'entends sans avoir besoin d'avoir des cellules vides)
06/06/2016, 12h37
MrcRafale

La liste est sur la même feuille parce qu'elle sert de sommaire. J'ai donc un sommaire et un outil de navigation sur ma feuille 1 : c'est pour ça que les deux sont sur la même feuille.
L'utilisateur final de ce fichier VBA ne doit pas avoir à réfléchir pour se servir de l'applicatif.
Il a donc sous les yeux un sommaire, et une liste pour accéder directement à la page qu'il souhaite
06/06/2016, 12h46
Philippe Tulliez

Bonjour,
Mais si tu as une liste, il visualise tout autant les dates non ?
Dans le cas contraire, pourquoi ne pas utiliser une procédure événementielle sur le clic de la cellule contenant la date ou utiliser la fonction LIEN_HYPERTEXTE d'excel qui permet de lier la cellule A1 de la feuille dont le nom est dans la cellule
06/06/2016, 12h55
MrcRafale

1 pièce(s) jointe(s)

Citation:

Envoyé par Philippe Tulliez

Bonjour,
Pourquoi placer la liste sur la même feuille
Tu crées une feuille où tu places test paramètres dont par exemple cette liste de dates les unes après les autres (par exemple A1:A10 où tu mets le titre en A1. Tu nommes la plage A2:A10 à l'aide du gestionnaire des noms tu transformes ensuite la plage A1:A10 en tableau (en n'oubliant pas de cocher l'option Mon tableau comporte des en-têtes)
Dans l'outil Validation de données, tu fais référence au nom que tu as donné et tu pourras ajouter et/ou supprimer des éléments de ta liste comme tu l'entends sans avoir besoin d'avoir des cellules vides)

Je ne comprends pas comment vous faites pour faire référence au nom donné à la sélection dans la fenêtre de validation des données.$
J'ai ce type de fenêtre, à aucun moment je ne peux saisir un nom de plage
Pièce jointe 211924

Citation:

Envoyé par Philippe Tulliez

Bonjour,
Mais si tu as une liste, il visualise tout autant les dates non ?
Dans le cas contraire, pourquoi ne pas utiliser une procédure événementielle sur le clic de la cellule contenant la date ou utiliser la fonction LIEN_HYPERTEXTE d'excel qui permet de lier la cellule A1 de la feuille dont le nom est dans la cellule

Simplement parce que je ne sais pas faire.
Le classeur Excel se décline comme suit : une feuille menu (avec des stats globales, le sommaire, et les différentes commandes permettant d'ajouter et de rechercher une feuille), les feuilles des différentes formations (qui sont nommées par date et classées par ordre croissant), et une feuille template, qui correspond au modèle de la feuille de formation (que je copie-colle)
Etant donné que nous avons assez souvent des formations, la liste bouge, et je lui rajoute une ligne à chaque fois. Si vous savez comment faire pour avoir un lien hypertexte vers la feuille nommée dans le sommaire, ça me simplifie la tâche et allège l'applicatif
06/06/2016, 13h42
Igloobel

Citation:

Envoyé par MrcRafale

Je ne comprends pas comment vous faites pour faire référence au nom donné à la sélection dans la fenêtre de validation des données.$
J'ai ce type de fenêtre, à aucun moment je ne peux saisir un nom de plage
Pièce jointe 211924

pour obtenir une liste de validation tu dois dans "Autoriser" cliquer sur la flèche et choisir "Liste"

et là tu pourra faire référence à un nom

A+

Bonjour,
Voici une procédure qui insère la fonction LIEN_HYPERTEXTE à partir de la cellule de la feuille déclarée dans l'argument Rng avec comme lien la cellule A1 de chaque feuilles du classeur à l'exception des feuilles nommée [Menu] et [Template]
Exemple d'utilisation cette fonction afin de sélectionner une cellule ou plage de cellules d'une feuille (dans l'exemple cellule A5 de la feuille nommée Février du classeur nommé Mon Budget.xls
Le premier argument de la fonction est le lien, le deuxième un alias (le texte qui apparaît dans la cellule)

Code:

=LIEN_HYPERTEXTE("[Mon Budget.xls]Février!A5";"Budget Février")

Si le nom de la feuille n'est pas d'un seul mot, il y a lieu de placer son nom entre apostrophes comme l'illustre l'exemple ci-dessous.

Code:

=LIEN_HYPERTEXTE("[SummaryCreating.xls]'Budget février'!$A$1"; "Budget février")

Code de la procédure à placer dans un module (Dans la procédure, j'utilise la propriété CodeName (valeur shtMenu) de la feuille nommée MENU
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Sub SummaryCreation(rng As Range) Dim Counter As Long, sht As Worksheet, myformula As String rng.CurrentRegion.Resize(columnsize:=1).ClearContents ' Efface les données précédentes For Each sht In Worksheets myformula = "=HYPERLINK(" & Chr(34) & "<Adress>" & Chr(34) & ", <Alias>)" Select Case LCase(sht.Name) Case "menu", "template" Case Else With rng ' CodeName de la feuille myformula = Replace(myformula, "<Adress>", sht.Range("A1").Address(external:=True)) If InStr(myformula, "'") Then ' Reconstruction de l'apostrophe myformula = Replace(myformula, "'", "", Count:=1) myformula = Replace(myformula, "]", "]'") End If myformula = Replace(myformula, "<Alias>", Chr(34) & sht.Name & Chr(34)) .Offset(Counter).Formula = myformula End With Counter = Counter + 1 End Select Next End Sub
Tu peux placer un bouton ActiveX sur la feuille.
Voici un exemple du code placé dans la feuille nommée MENU et dont la valeur de la propriété CodeName est shtMenu
Code:

1 2 3 Private Sub CommandButton1_Click() SummaryCreation shtMenu.Range("B3") End Sub
Tu peux également prévoir le lancement de la procédure automatiquement à la création d'une nouvelle feuille.
Code à placer dans le module ThisWorkbook
Code:

1 2 3 Private Sub Workbook_NewSheet(ByVal Sh As Object) SummaryCreation shtMenu.Range("B3") End Sub
[EDIT]
Ajouté une ligne de code en début de procédure pour effacer les données précédentes (pour éviter les liens orphelins en cas de diminution de nombre de feuilles)

06/06/2016, 17h00
MrcRafale

Bonjour,

Je vous remercie pour cette réponse, même si je ne la vois que tardivement.
Avec 2 solutions différentes à la clé, je vais pouvoir faire 2 fichiers différents, et les proposer ainsi à mon responsable qui statuera lequel répond mieux à son besoin.

Merci pour votre investissement, et encore pardon.
06/06/2016, 17h09
Philippe Tulliez

Bonjour,
Pas de souci et désolé pour le coup de gueule
Ne pas oublier de mettre le statut de la discussion comme Résolue