Блог

Я создаю набор выпадающих списков динамически на листе Excel, но мне нужен только один общий метод «Действия» для них всех. Я получаю данные с одного рабочего листа для заполнения комбинаций на основном рабочем листе

Мой код приведен ниже, но я не могу получить доступ к каждому отдельному выпадающему списку при срабатывании события (оно срабатывает). Есть ли способ узнать, какой выпадающий список был запущен? Даже если это просто индекс, или имя, или что-то в этом роде, поэтому я могу пойти и найти соответствующий выпадающий список. (Всего количество комбинаций будет 200, и форма — это не то, что мы хотим здесь по другим причинам, поэтому она находится на листе.)

 Option Explicit

Dim i As Integer
' This is used to programme the comboboxes
Private Sub GenerateComboboxes()

    On Error GoTo ErrorHandler

    Dim DestinationDataTypeCombo As Object, DestinationDataTextCombo As Object, oObject As Object
    Dim ws As Worksheet, sString As String, rng As Range
    
    Dim nCombos, nStartLine As Integer
    Dim i2, iHeight, iLeft, iTop, iWidth As Integer
    
    nCombos = 5
    nStartLine = 2
    
    Set ws = Worksheets("User Entry")
    ws.Activate
    
    'Clear what was there before
    For Each oObject In ws.Shapes
        oObject.Delete
    Next
    
    ' add each combo to the worksheet
    For i = 0 To nCombos - 1
        sString = "D" amp; (i   nStartLine)
        Set rng = ws.Range(sString)
        
        ' Create a Combo instance
        Set DestinationDataTypeCombo = ws.Shapes.AddFormControl(xlDropDown, _
                                      Left:=rng.Left, _
                                      Top:=rng.Top, _
                                      Width:=rng.Width, _
                                      Height:=rng.Height)
                                      
        ' Set up the properties
        With DestinationDataTypeCombo
            .ControlFormat.DropDownLines = 5
            .Name = "cbDataType" amp; i
            For i2 = 2 To 17
                sString = Worksheets("Static Data").Cells(i2, 1)
               .ControlFormat.AddItem sString
                
            Next
            ' Set a generic OnAction for ALL combos to use
            .OnAction = "cbDataType_Change"
        End With
       
    Next i
    
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description
     
End Sub
Public Sub cbDataType_Change()

    On Error GoTo ErrorHandler
    
    Dim sValue As String
    'This works so I know the change event fires
    MsgBox "Test"
    ' Want to get the value selected, this line errors
    sValue = cbDataType.Value
    MsgBox sValue
    
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description
     
End Sub
  

Ваш код создает раскрывающийся список формы. Тип элемента управления формой листа не предоставляет никаких событий…

Вы можете назначить ему макрос с любым именем, которое вы хотите (четным cbDataType_Change ), используя .OnAction , как вы это делали.

Теперь используемый объект может быть возвращен, начиная с Application.Caller , который возвращает имя элемента управления. На его основе можно задать объект управления, используя Sheet.Shapes . И, наконец, значение такого элемента управления можно получить немного более сложным способом, используя объект OLEFormat.Object.list . Итак, ваше Sub назначение всем элементам управления должно быть таким:

 Sub cbDataType_Change() 'this is not an event!
    Dim cb As Object
    
    Set cb = ActiveSheet.Shapes(Application.Caller) 'the object which called this Sub
    Debug.Print cb.Name, cb.ControlFormat.Value     'returns the control name and its index
    MsgBox cb.OLEFormat.Object.list(cb.ControlFormat.Value) 'the control value
End Sub