Ответ 1
Я знаю, что вы приняли решение, которое будет работать для вас и намного проще, чем ниже, но если вам интересно, это будет более прямой ответ на ваш вопрос.
Вам нужно создать класс для обработки щелчков кнопок, поэтому каждый раз, когда нажимается кнопка, он использует событие в классе, вам нужно сделать это только один раз, а затем создать новый экземпляр для каждой кнопки. Чтобы остановить эти классы, выходящие из сферы действия и потерянные, им необходимо хранить в декларации уровня класса. Внизу я немного изменил код.
В модуле класса (я назвал его cButtonHandler)
Public WithEvents btn As MSForms.CommandButton
Private Sub btn_Click()
MsgBox btn.Caption
End Sub
При использовании событий используется, поскольку он позволяет использовать большинство событий для элемента управления. Я переместил код генерации кнопок в форму пользователя, как показано ниже:
Dim collBtns As Collection
Private Sub UserForm_Initialize()
Dim theLabel As Object
Dim labelCounter As Long
Dim daycell As Range
Dim btn As CommandButton
Dim btnCaption As String
'Create a variable of our events class
Dim btnH As cButtonHandler
'Create a new collection to hold the classes
Set collBtns = New Collection
For Each daycell In Range("daylist")
btnCaption = daycell.Text
Set theLabel = ReadingsLauncher.Controls.Add("Forms.Label.1", btnCaption, True)
With theLabel
.Caption = btnCaption
.Left = 10
.Width = 50
.Top = 20 * labelCounter
End With
Set btn = ReadingsLauncher.Controls.Add("Forms.CommandButton.1", "runButton", True)
With btn
.Caption = "Run Macro for " & btnCaption
.Left = 80
.Width = 80
.Top = 20 * labelCounter
'Create a new instance of our events class
Set btnH = New cButtonHandler
'Set the button we have created as the button in the class
Set btnH.btn = btn
'Add the class to the collection so it is not lost
'when this procedure finishes
collBtns.Add btnH
End With
labelCounter = labelCounter + 1
Next daycell
End Sub
Затем мы можем вызвать форму использования из отдельной процедуры:
Sub addLabel()
ReadingsLauncher.Show vbModeless
End Sub
Классы в VBA не особенно хорошо освещены во многих книгах VBA (как правило, вам нужно читать книги VB6, чтобы понять), однако, как только вы их понимаете и как они работают, они становятся невероятно полезными:)
Надеюсь, что это поможет
EDIT - для запроса дополнительных запросов
Чтобы ссылаться на объекты в коллекции, это делается либо с помощью ключа, либо с помощью индекса. Чтобы использовать ключ, вам нужно добавить его при добавлении элемента в коллекцию, чтобы:
collBtns.Add btnH
Стал бы
collBtns.Add btnH, btnCaption
По этой причине ключи должны быть уникальными. Затем вы можете ссылаться следующим образом:
'We refer to objects in a collection via the collection key
'Or by it place in the collection
'So either:
MsgBox collBtns("Monday").btn.Caption
'or:
MsgBox collBtns(1).btn.Caption
'We can then access it properties and methods
'N.B you won't get any intellisense
collBtns("Monday").btn.Enabled = False
Вы также можете добавить дополнительные свойства/метод в свой класс, если это необходимо, например:
Public WithEvents btn As MSForms.CommandButton
Private Sub btn_Click()
MsgBox btn.Caption
End Sub
Public Property Let Enabled(value As Boolean)
btn.Enabled = value
End Property
Затем будет доступен доступ:
collBtns("Monday").Enabled = False
Помогает ли это? Для дальнейшего чтения я хотел бы указать вам на сайт Чипа Пирсона, у него отличные материалы по большинству тем http://www.cpearson.com/excel/Events.aspx
Просто помните, что VBA основан на VB6, поэтому он не является полностью полноценным языком OO, например, он не поддерживает наследование в обычном смысле, только наследование интерфейса
Надеюсь, что это поможет:)