Recreating an ActiveX control with VBA events, using VBA

Question

I have a combobox that has multiple events tied to it (eg, ComboBox1_Change, Click, GotFocus, LostFocus). For my purposes, I need to use an ActiveX control.

For some background:

I ran into an issue where the ActiveX control glitches when the machine is attached to a projector. Other's have had the same problem, but no solution. The best way I found to solve this issue is to create an additional button the user can click which deletes the ActiveX control and recreate it.

The problem is (and my question)... When I do this, the VBA events no longer execute. The control is named the exact same. Is there a simple fix (such as property in the ActiveX control I can set), so that I do not need to write VBA code to delete and rewrite existing VBA code (which I'm not even 100% sure will fix the issue)?

Answer 1

This seems to work for me, in a worksheet:

Start with 2 CommandButtons on a worksheet: CommandButton1 and CommandButton2.

You can't step through the code, but it will run.

Option Explicit

Private Sub CommandButton1_Click()
  Dim oldObj As OLEObject
  Dim left As Double, top As Double, width As Double, height As Double
  Set oldObj = Me.OLEObjects("CommandButton2")

  With oldObj
    'Rename the control to detach events
    .Name = "CommandTEMP"
    left = .left
    top = .top
    width = .width
    height = .height
    .Delete
  End With

  Dim newObj As Object

  Set newObj = Me.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, left:=left, top:=top, width:=width, height:=height)

  newObj.Name = "CommandButton2"

End Sub

Private Sub CommandButton2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  Me.CommandButton2.Object.Caption = Timer
End Sub