creer liste déroulante via macro

[dmc]

Bonsoir à tous, grands manitous du VBA i=ou obscurs amateurs comme moi (tout petit tout petit d'oser poser ma question).
La question semble simple, les détails suivants me poussent à demander votre aide.
Ma macro crée une liste déroulante, que je dois "souder" à un emplacement précis, par exemple cadrage à droite de la colonne D et au centre de la ligne 4, et qui restera à cette position (ligne colonne) même si je redimensionne mes colonnes et mes lignes.
J'ai eu beau chercher des heures sur ce forum et d'autres, je dois mal formuler mes mots clés de recherche, je ne trouve rien.
Merci d'avance.😱

 

[Excel-lent]

Re : creer liste déroulante via macro

Bonsoir Dmc,

Une liste déroulante?????

Comment?
-> Via "Données" / "Validation"?
-> via "UserForm"?
-> via la "boîte à outils contrôles" ? Si oui :
=> zone de liste?
=> zone de liste déroulante?
-> via ???

En attendant plus de précision, bonne fin de soirée

 

[Paritec]

Re : creer liste déroulante via macro

Bonjour Excelent Dmc, le forum,
Dmc un petit fichier vaut mieux qu'une mauvaise explication!!! je me moque,
non ton fichier sans info confidentielles et ce que tu veux obtenir et tu auras des réponses et ta solution
a+
papou

 

[job75]

Re : creer liste déroulante via macro

Bonjour à tous,

Il y a en effet plusieurs possibilités comme le souligne Excel-lent, mais le plus simple est de créer une liste de validation avec par exemple le code suivant (tiré de l'enregistreur de macros...) :

ActiveSheet.Range("B1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$A$1:$A$5"

La cellule concernée est B1 avec une liste définie par la plage A1:A5.

A+

Edit : salut papou

 

[dmc]

Re : creer liste déroulante via macro

Bonjour à Excel-lent, Paritec, job75, et merci de regarder mon pb.
A me relire 24h après, c'est moins complet que je ne pensais!!
Donc :
- pour répondre à Excel-lent:
via la "boîte à outils contrôles" puis => zone de liste (non déroulante)
- pour répondre à Job75: ton script donne une liste de validation qui ne me convient pas car j'ai besoin de renvoyer dans une autre cellule le n° de la selection faite par l'utilisateur, ce n° étant utilisé par différentes formules de ma feuille (formules du type INDEX).
- pour répondre à Paritec:
je veux bien t'envoyer un fichier dans lequel se trouvera cette zone de liste, mais cela ne t'apportera rien que tu ne puisses faire en 5 secondes toi-même.
Mon objectif est que ce contrôle soit systématiquement placé (par une macro, s'entend) dans une cellule précise, sans que des redimensionnements ultérieurs de colonnes ne risquent de la voir apparaitre dans une autre cellule que celle initiale.
Je t'envoie ce fichier, tu ne seras pas déçu par sa simplicité! Utilises la feuille Devis et "amuses-toi" à redimensionner la largeur de colonnes. La liste sort de la cellule d'origine ! Comment contrer cela?
Amicalement à tous, et dans l'attente de la suite de vos propositions...

 

[dmc]

Re : créer liste déroulante via macro

bonjour à tous - ici tempête grand format !!!
je me suis trompé dans ma manip hier et je n'ai pas joint le fichier préparé spécialement.
Je régularise maintenant, et bien sur, je compte toujours autant sur la communauté .
Merci d'avance.🙄

 

[PMO2]

Re : creer liste déroulante via macro

Bonjour,

Plutôt que modifier votre feuille "Devis", j'ai préféré fabriquer une nouvelle feuille qui servira d'exemple.
Voici donc une solution que je décris ci-après ainsi que la marche à suivre.

1) Créez une nouvelle feuille et nommez la du nom que vous voulez
2) En ligne 1 et à partir de A1 tapez vos titres
colisage réf désignation prix qté total
3) Dans la fenêtre de code de CETTE feuille copiez le code suivant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call DelListe
With Target
  If .Column <> COLONNE_LISTBOX Then Exit Sub
  If .Rows.Count > 1 Or .Columns.Count > 1 Then Exit Sub
  If .Row = 1 Then Exit Sub
  If ActiveSheet.Columns(COLONNE_LISTBOX).ColumnWidth < 30 Then
    MsgBox "Veuillez élargir la colonne."
    Exit Sub
  End If
End With
Call AddListe(Target)
End Sub

4) Dans un module standard copiez le code suivant

'### Constantes à adapter à votre usage ###
Const CATALOGUE As String = "catalogue"
Public Const COLONNE_LISTBOX As Long = 3
'##########################################

Sub AddListe(R As Range)
Dim Sh As Worksheet
Dim Sh2 As Worksheet
Dim R2 As Range
Dim S As Shape
Set Sh = R.Parent
Set Sh2 = ThisWorkbook.Sheets(CATALOGUE)
  '/// Les données de la feuille catalogue doivent commencer ///
  '/// en C2. Pour l'instant elles se terminent en colonne E ///
  '/// Pour une extension de colonne, modifiez le 5 final    ///
Set R2 = Sh2.Range(Sh2.Cells(2, 3), Sh2.Cells(Sh2.[c65536].End(xlUp).Row, 5))
  '////////////////////////////////////////////////////////////
Set S = Sh.Shapes.AddFormControl(xlDropDown, _
  R.Width / 5 * 4 + R.Left, R.Top + 1.5, (R.Width / 4), R.Height - 1.5)
S.ControlFormat.ListFillRange = Sh2.Name & "!" & R2.Address
S.OnAction = "DropDownSurClic"
S.Select
Selection.DropDownLines = 12
R.Select
End Sub

Sub DropDownSurClic()
Dim S As Shape
Dim DD As DropDown
Dim R As Range
Dim A$
Dim B$
Dim var
Dim Ref$
Dim T()
Dim i&
For Each S In ActiveSheet.Shapes
  If S.FormControlType = xlDropDown Then
    Set DD = S.OLEFormat.Object
    Exit For
  End If
Next S
Ref$ = DD.ListFillRange
A$ = Mid(Ref$, 1, InStr(1, Ref$, "!") - 1)
B$ = Mid(Ref$, Len(A$) + 2)
Set R = Sheets(A$).Range(B$)
var = R
ReDim T(1 To 1, 1 To UBound(var, 2))
For i& = 1 To UBound(var, 2)
  T(1, i&) = var(DD.ListIndex, i&)
Next i&
i& = DD.TopLeftCell.Row
Set R = ActiveSheet.Range(Cells(i&, 2), Cells(i&, UBound(var, 2) + 1))
R = T
End Sub

Sub DelListe(Optional dummy As Byte)
Dim S As Shape
For Each S In ActiveSheet.Shapes
  If S.FormControlType = xlDropDown Then
    S.Cut
    Exit For
  End If
Next S
End Sub

5) Adaptez éventuellement les contantes (cernées par des ###)

6) Dans la feuille catalogue, dont les données doivent commencer IMPERATIVEMENT en C2,
j'ai rempli la colonne D par des pseudo dénominations. Faites de même avec des dénominations valides.

Lorsque vous serez sur la feuille qui a été créée et que vous cliquerez en colonne C (hormis en ligne 1),
une ListBox (DropDown) apparaîtra avec les références des produits.

Bon courage.

Cordialement.

PMO
Patrick Morange

 

[dmc]

Re : creer liste déroulante via macro

🙂Merci beaucoup à PMO pour cette réponse complète.
J'ai testé, ç'est parfait.
Une fois de plus, ce forum est précieux, l'ambiance hyper-pro.
Juste un détail PMO : la charte prévoit il me semble le tutoiement spécifique.
Alors n'hésites pas, sinon ça me donne un coup de vieux dont je me passerais volontiers.
à bientôt j'espère.😉