Microsoft 365 VBA : Création d'une liste déroulante

1. une validation ?
2. un combobox de feuille ?
3. un combobox activex ?

fanch55 à dit:

Bonjour,
la liste déroulante, c'est pour :

1. une validation ?
2. un combobox de feuille ?
3. un combobox activex ?

Regarde la pièce jointe 1104790

Cliquez pour agrandir...

merinos à dit:

J'ai inclu dans cet exemple un range calculé de facon dynamique...

regarde ma_liste_dyna

J'ai une macro qui les crée.

VB:

Sub dynarangeV()
' Writen by Bernard Heymans , June 16th , 2015
' The objective of the macro is to create a named range from a list.
' First item of the list gives the name of the range (and is not included in range)
' it does put the column where the list is in color
' an hyperlink to the range is created on top of the list
' the range will be automaticaly addapted to allow user to add data & without re-running the macro

Dim debut As Range
Dim nomrange As String
Dim nomrangelong As String


Set debut = ActiveCell.Cells(1, 1)

debut.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
nomrange = debut.Value
debut.Name = "zz" + nomrange + "start" 'define the start point of the range
debut.EntireColumn.Name = "zz" + nomrange + "col" 'name the column of the range
debut.EntireColumn.Interior.ColorIndex = xlNone
nomrangelong = "zz" & nomrange & "long"
nomrangecol = "zz" & nomrange & "col"

ActiveWorkbook.Names.Add "zz" & nomrange & "long", "=MAX(IF(ISNUMBER(MATCH(9^9," & nomrangecol & ")),MATCH(9^9," & nomrangecol & ")" _
               & "),IF(ISNUMBER(MATCH(""zzz""," & nomrangecol & ")),MATCH(""zzz""," & nomrangecol & ")))"
             
             
ActiveWorkbook.Names.Add nomrange, "=OFFSET(zz" & nomrange & "start,1,,zz" & nomrange & "long -ROW(zz" & nomrange & "start))"
ActiveSheet.Hyperlinks.Add debut, "", nomrange

Range("zz" & nomrange & "col").Interior.ColorIndex = 40
debut.Interior.ColorIndex = 46
End Sub 'dynarangeV

Cliquez pour agrandir...