I am inserting a ActiveX control Label in excel sheet using VBA code. Now after inserting the button, I am trying to insert the click event code but its not working. Below is the code:
Public Function AddButton(strSheetName, counter)
Dim btn As OLEObject
Dim cLeft, cTop, cWidth, cHeight
Dim CodeModule As Object
With Worksheets(strSheetName).Range("J" & (6 + counter))
cLeft = .Left + 1
cTop = .Top + 1
cWidth = .Width - 2
cHeight = .Height - 2
End With
With Worksheets(strSheetName)
Set btn = .OLEObjects.Add(ClassType:="Forms.Label.1", Link:=True, DisplayAsIcon:=True, Left:=cLeft, Top:=cTop, Width:=cWidth, Height:=cHeight)
End With
btn.Object.Caption = "Add New"
btn.Name = Left(strSheetName, 3) & counter
Set CodeModule = ActiveWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
CodeModule.InsertLines CodeModule.CreateEventProc("Click", btn.Name) + 1, vbTab & "MsgBox ""Hello world"""
End Function
Button is getting inserted but click event code is not working. When I click nothing happens. Also this function is getting called in a loop. First time it adds button and then as soon as it tries to add click event code, loop terminates which means there is an error.
Any help?
Thanks in advance.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…