Come aggiungere eventi ai controlli creati in fase di runtime in Excel con VBA

Vorrei aggiungere un controllo e un evento associato in fase di esecuzione in Excel utilizzando VBA ma non so come aggiungere gli eventi.

Ho provato il codice qui sotto e il pulsante è stato creato correttamente nel mio userform, ma l’evento click associato che dovrebbe visualizzare il messaggio ciao non funziona.

Qualsiasi consiglio / correzione sarebbe il benvenuto.

Dim Butn As CommandButton Set Butn = UserForm1.Controls.Add("Forms.CommandButton.1") With Butn .Name = "CommandButton1" .Caption = "Click me to get the Hello Message" .Width = 100 .Top = 10 End With With ThisWorkbook.VBProject.VBComponents("UserForm1.CommandButton1").CodeModule Line = .CountOfLines .InsertLines Line + 1, "Sub CommandButton1_Click()" .InsertLines Line + 2, "MsgBox ""Hello!""" .InsertLines Line + 3, "End Sub" End With UserForm1.Show 

Il codice per aggiungere un pulsante in fase di esecuzione e quindi per aggiungere eventi è davvero semplice come è difficile da scoprire … Posso dire di aver passato più tempo a questa perplessità e di essermi irritato più che in qualsiasi altra cosa io abbia mai programmato ..

Crea un Userform e inserisci il seguente codice:

 Option Explicit Dim ButArray() As New Class2 Private Sub UserForm_Initialize() Dim ctlbut As MSForms.CommandButton Dim butTop As Long, i As Long '~~> Decide on the .Top for the 1st TextBox butTop = 30 For i = 1 To 10 Set ctlbut = Me.Controls.Add("Forms.CommandButton.1", "butTest" & i) '~~> Define the TextBox .Top and the .Left property here ctlbut.Top = butTop: ctlbut.Left = 50 ctlbut.Caption = Cells(i, 7).Value '~~> Increment the .Top for the next TextBox butTop = butTop + 20 ReDim Preserve ButArray(1 To i) Set ButArray(i).butEvents = ctlbut Next End Sub 

Ora devi aggiungere un modulo di class al tuo codice per il progetto. Ricorda il suo modulo di class non Module.E inserisci il seguente codice semplice (nel mio caso il nome della class è Class2) –


 Public WithEvents butEvents As MSForms.CommandButton Private Sub butEvents_click() MsgBox "Hi Shrey" End Sub 

Questo è tutto. Ora eseguilo

DaveShaw, grazie per questo codice uomo!

L’ho usato per un array togglebutton (metti un’immagine ‘thumbnail-size’ chiamata trainer.jpg nella stessa cartella del file excel per un togglebutton con un’immagine in esso). Nell’evento ‘click’ è disponibile anche l’invoker (dal nome dell’object come stringa)

Nella forma:

 Dim CreateTrainerToggleButtonArray() As New ToggleButtonClass Private Sub CreateTrainerToggleButton(top As Integer, id As Integer) Dim pathToPicture As String pathToPicture = ThisWorkbook.Path & "\trainer.jpg" Dim idString As String idString = "TrainerToggleButton" & id Dim cCont As MSForms.ToggleButton Set cCont = Me.Controls.Add _ ("Forms.ToggleButton.1") With cCont .Name = idString .Width = 20 .Height = 20 .Left = 6 .top = top .picture = LoadPicture(pathToPicture) End With ReDim Preserve CreateTrainerToggleButtonArray(1 To id) Set CreateTrainerToggleButtonArray(id).ToggleButtonEvents = cCont CreateTrainerToggleButtonArray(id).ObjectName = idString End Sub 

e una class “ToggleButtonClass”

  Public WithEvents ToggleButtonEvents As MSForms.ToggleButton Public ObjectName As String Private Sub ToggleButtonEvents_click() MsgBox "DaveShaw is the man... <3 from your friend: " & ObjectName End Sub 

Ora basta una semplice chiamata da UserForm_Initialize

  Private Sub UserForm_Initialize() Dim index As Integer For index = 1 To 10 Call CreateTrainerToggleButton(100 + (25 * index), index) Next index End Sub 

Prova questo:

 Sub AddButtonAndShow() Dim Butn As CommandButton Dim Line As Long Dim objForm As Object Set objForm = ThisWorkbook.VBProject.VBComponents("UserForm1") Set Butn = objForm.Designer.Controls.Add("Forms.CommandButton.1") With Butn .Name = "CommandButton1" .Caption = "Click me to get the Hello Message" .Width = 100 .Top = 10 End With With objForm.CodeModule Line = .CountOfLines .InsertLines Line + 1, "Sub CommandButton1_Click()" .InsertLines Line + 2, "MsgBox ""Hello!""" .InsertLines Line + 3, "End Sub" End With VBA.UserForms.Add(objForm.Name).Show End Sub 

Questo modifica in modo permanente UserForm1 (presupponendo che si salva la cartella di lavoro). Se si desidera un modulo utente temporaneo, aggiungere un nuovo modulo utente invece di impostarlo su UserForm1. È quindi ansible eliminare il modulo una volta che hai finito con esso.

Chip Pearson ha alcune ottime informazioni sulla codifica del VBE.

Questa era la mia soluzione per aggiungere un comando e un codice senza usare le classi Aggiunge un riferimento per consentire l’accesso a vbide Aggiunge il pulsante

Quindi scrive una funzione per gestire l’evento click nel foglio di lavoro

 Sub AddButton() Call addref Set rng = DestSh.Range("B" & x + 3) 'Set btn = DestSh.Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height) Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=rng.Left, Top:=rng.Top, Height:=rng.Height * 3, Width:=rng.Width * 3) DoEvents With myButton '.Placement = XlPlacement.xlFreeFloating .Object.Caption = "Export" .Name = "BtnExport" .Object.PicturePosition = 1 .Object.Font.Size = 14 End With Stop myButton.Object.Picture = LoadPicture("F:\Finalised reports\Templates\Macros\evolution48.bmp") Call CreateButtonEvent End Sub Sub addref() On Error Resume Next Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB" Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB" End Sub Private Sub CreateButtonEvent() On Error GoTo errtrap Dim oXl As Application: Set oXl = Application oXl.EnableEvents = False oXl.DisplayAlerts = False oXl.ScreenUpdating = False oXl.VBE.MainWindow.Visible = False Dim oWs As Worksheet Dim oVBproj As VBIDE.VBProject Dim oVBcomp As VBIDE.VBComponent Dim oVBmod As VBIDE.CodeModule ' Dim lLine As Single Const QUOTE As String = """" Set oWs = Sheets("Contingency") Set oVBproj = ThisWorkbook.VBProject Set oVBcomp = oVBproj.VBComponents(oWs.CodeName) Set oVBmod = oVBcomp.CodeModule With oVBmod lLine = .CreateEventProc("Click", "BtnExport") + 1 .InsertLines lLine, "Call CSVFile" End With oXl.EnableEvents = True oXl.DisplayAlerts = True Exit Sub errtrap: End Sub 

Penso che il codice debba essere aggiunto a Userform, non al pulsante stesso.

Quindi qualcosa di simile

 With UserForm1.CodeModule 'Insert code here End With 

Al posto del tuo With ThisWorkbook