Meinst du DropDown oder Popup?
Das zweite ist etwas kompliziert / bedarf sorgfältige Planung und Ausarbeitung. Grob gesehen könnte es so aussehen:
Dieser Code kommt in ein Modul:
Option Explicit
Public Function GetDropDown(Sh As Object, ByVal Target As Range, Optional ReInit As Boolean) As CommandBar
Const C_POPUP_NAME As String = "__myMainPopup"
Dim objPopup As CommandBar
For Each objPopup In Application.CommandBars
If objPopup.Name = C_POPUP_NAME Then Exit For
Next
If Not objPopup Is Nothing And ReInit Then
Call objPopup.Delete
Set objPopup = Nothing
End If
If objPopup Is Nothing Then
Set objPopup = Application.CommandBars.Add(C_POPUP_NAME, msoBarPopup, , True)
'1. Ebene: 1. Element
With objPopup.Controls.Add(msoControlComboBox, Temporary:=True)
.OnAction = "Popup_OnAction"
.Caption = "Item1-1"
.Tag = "cboSelection1"
Call .AddItem("SubItem1")
Call .AddItem("SubItem2")
Call .AddItem("SubItem3")
End With
'1. Ebene: 2. Element
With objPopup.Controls.Add(msoControlButton, Temporary:=True)
.OnAction = "Popup_OnAction"
.Caption = "Item1-2"
.Tag = "btnAction1"
End With
'1. Ebene: 3. Element
With objPopup.Controls.Add(msoControlPopup, Temporary:=True)
.Caption = "Item1-3"
.Tag = "mnuSub1"
'2. Ebene: 1. Element
With .Controls
With .Add(msoControlButton, Temporary:=True)
.OnAction = "Popup_OnAction"
.Caption = "Item1-3-1"
.Tag = "btnAction1-1"
End With
End With
End With
End If
Set GetDropDown = objPopup
End Function
Public Sub Popup_OnAction()
With CommandBars.ActionControl
If .Type = msoControlComboBox Then
MsgBox .Tag & " { Text = '" & .Text & "' }"
Else
MsgBox .Tag
End If
End With
End Sub
Dieser Code kommt in DieseArbeitsmape:
Option Explicit
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
GetDropDown(Sh, Target, True).ShowPopup
End Sub
Wenn du jetzt auf ein beliebiges Tabellenblatt klickst, erhält man soetwas:
Gruß
PS: Wohl am besten / saubersten wäre es, wenn man das in Klassen programmiert.
|