How do I link a button created with VBA with a procedure in another module?

[Glory]

Code creates button. How do I link the button with some code?

ThisWorkbook.ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", _
                                        Height:=32, _
                                        Object:="Update", _
                                        Left:=292, _
                                        Top:=34, _
                                        Width:=102

 

[Glory]

Found what looks like an answer to question 1 here:

http://www.excelforum.com/excel-programming/679211-cant-enter-break-mode-at-this-time-error.html

Leith Ross wrote: "When OLE objects are created, they are created outside the Excel process using the OLE server. VBA must yield until the object has been created and included in Excel. Once this happens, the OLE server returns back to the VBA process. VBA can only enter break mode while the Excel process is active."

Because I can't step through I can't figure out what might be going wrong with my attempt at reworking the code.

Private WithEvents oCmb As CommandButton
Private Sub oCmb_Click2()
    MsgBox "Hello from  " & "'" & oCmb.Caption & "'"
End Sub

Public Function MakeTheButton()
    Set Cmb = ThisWorkbook.ActiveSheet.OLEObjects.Add _
    (ClassType:="Forms.CommandButton.1", _
           Height:=32, _
           Width:=200, _
           Left:=292, _
           Top:=34, _
           Width:=102)
    Set oCmb = Cmb
End Function

Private oCmbInstance As InventoryUpdater_Class

Sub AddButton2()
    Set oCmbInstance = New InventoryUpdater_Class
     oCmbInstance.MakeTheButton
End Sub

This also fails.

 

[Jaafar Tribak]

Sektor: I'm going to stick with what I know for now, but thanks for the input.

Jaafar: There's four or five things going on in your code that are turning me for a loop. I'm working through most of it, but I'm left wondering about a couple of things.

1) I get an error when I step through the "AddButton" procedure. This makes it really hard to debug. Any idea why this is happening?

2) I've never worked with a shape range obejct before and I would prefer to work with the buttons' names if at all possible. But I can't seem to get the code to recognize the name of the button alone. Is it necessary to use a named range to refer to the button in your code?


Runs successfully, but fails to hook button:

Option Explicit
 
Private WithEvents oCmb As CommandButton
 
Private Sub oCmb_Click()
    MsgBox "Hello from  " & "'" & oCmb.Caption & "'"
End Sub
 
Public Property Set GetCommandButton(ByVal Cmb As CommandButton)
    Set oCmb = Cmb
End Property

Private oCmbInstance As CmbClass
 
Sub AddButton()
    Dim Cmb  As OLEObject
 
    Set Cmb = ThisWorkbook.ActiveSheet.OLEObjects.Add _
    (ClassType:="Forms.CommandButton.1", _
           Height:=32, _
           Width:=200, _
           Left:=292, _
           Top:=34, _
           Width:=102)
 
    Cmb.Name = "MyCommandButton"
 
    Call HookButton
 
End Sub
 
Private Sub HookButton()
    Set oCmbInstance = New CmbClass
    Set oCmbInstance.GetCommandButton = ActiveSheet.OLEObjects("MyCommandButton").Object
 
End Sub

You need to execute the HooButton Procedure after the AddButton routine exits . This is because adding a ControlX button at runtime on a worksheet resets the VB Project and resets all the variables.

To avoid this issue, you will need to use the OnTime trick as follows :

Change this :

Call HookButton

to this :

Application.OnTime Now + TimeSerial(0, 0, 1), "HookButton"

As for why I used the ShapeRange.ID of the OleObject is to uniqually identify it. To see why not uniqually identifying the Button can be a problem, try running the AddButton Procedure (including the .OnTime code bit ) twice and you will see that the new Button will fail to get hooked.

Edit:

I used an Excel Name to temporarly store the the button name as opposed to a variable for the reason I mentioned above and that is the VB Project is reset when adding an ActiveX Control at runtime and therefore all variable values are lost.

 

[Glory]

Wouldn't any old buttons be unhooked when any new button was added then? Because the class module object variables would be reset.

Isn't a unique name enough of a unique identifier? Each time this code runs, the buttons appear on a new sheet that replaces the old one, so there should never be any conflict.

 

[Jaafar Tribak]

Wouldn't any old buttons be unhooked when any new button was added then? Because the class module object variables would be reset.

Isn't a unique name enough of a unique identifier? Each time this code runs, the buttons appear on a new sheet that replaces the old one, so there should never be any conflict.

No. if you don't uniqually identify the name of the button, the button that gets hooked is the first one because they will all have the same name ie : MyCommandButton.

If you are adding many buttons at runtime using the same approach and want to have them all hooked it is going to be more difficult as the Project will be reset each time you add a new Button.

 

[Glory]

Yeah, I'll eventually be adding three buttons.

I think there's a miscommunication here. The code I posted above was just my attempt to refer to a single button by its object name.

I don't understand why the code in post #11 doesn't hook the button, and I can't step through it to figure it out.

Can you tell me how to "hook" a single button using its object name (instead of a shaperange object)? No concern about what will happen if it's run multiple times, focusing on this one problem for now.

 

[Jaafar Tribak]

Try this - it definitly should hook the button.

Private oCmbInstance As CmbClass
 
Sub AddButton()
    Dim Cmb  As OLEObject
 
    Set Cmb = ThisWorkbook.ActiveSheet.OLEObjects.Add _
    (ClassType:="Forms.CommandButton.1", _
           Height:=32, _
           Width:=200, _
           Left:=292, _
           Top:=34, _
           Width:=102)
 
    Cmb.Name = "MyCommandButton"
    
    Application.OnTime Now + TimeSerial(0, 0, 1), "HookButton"
 
 
End Sub
 
Private Sub HookButton()

    Set oCmbInstance = New CmbClass
    Set oCmbInstance.GetCommandButton = ActiveSheet.OLEObjects("MyCommandButton").Object
 
End Sub

You can't step through the code because when the button is added the Project enters Break mode.

 

[Glory]

I'm trying to get the button properties consolidated into the class module to clean up the original sub. Your suggestion worked great... the following does not. Can you explain why?

I carefully set oCmb after creating the button, mindful that creating the button empties all variables. Still doesn't work.

Class:

Private WithEvents oCmb As CommandButton
 
Private Sub oCmb_Click2()
    MsgBox "Hello from  " & "'" & oCmb.Caption & "'"
End Sub
 
Public Function MakeTheButton()
 
    Set Cmb = ThisWorkbook.ActiveSheet.OLEObjects.Add _
    (ClassType:="Forms.CommandButton.1", _
           Height:=32, _
           Width:=200, _
           Left:=292, _
           Top:=34, _
           Width:=102)
 
    Set oCmb = ThisWorkbook.ActiveSheet.OLEObjects("CommandButton1").Object
 
End Function

Standard module:

Private oCmbInstance As InventoryUpdater_Class
Sub AddButton2()
    Set oCmbInstance = New InventoryUpdater_Class
     oCmbInstance.MakeTheButton
End Sub

I really appreciate your assistance with this. Class modules are always a pain for me.

 

[Jaafar Tribak]

Glory,

I agree, self-containing the Button creation and its Properties inside the Class module is way more efficient and elegant however it becomes more difficult to code.

You could still use the same OnTime trick but the OnTime routine still has to be outside of the Class module which kind of defeats the purpose.

Here is a little known workaround to achieve hat you want by using the CopyMemory API to trick Excel as follows :

Class module code :

Option Explicit

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
pDest As Any, pSrc As Any, ByVal ByteLen As Long)
 
 
Private WithEvents oCmb As CommandButton
 
Private Sub oCmb_Click()
    MsgBox "You clicked on : " & "'" & oCmb.Name & "'"
End Sub



Public Sub MakeTheButton()

 
    Dim oTempSht As Worksheet
    Dim lSheetPtr As Long
 
 
   'get a memory pointer to the activesheet object.
    lSheetPtr = ObjPtr(ThisWorkbook.ActiveSheet)
 
 
   'copy the sheet memory location to our temp variable
     CopyMemory oTempSht, lSheetPtr, 4
 
    'add the runtime button.
    Set oCmb = oTempSht.OLEObjects.Add _
        (ClassType:="Forms.CommandButton.1", _
               Height:=32, _
               Width:=200, _
               Left:=292, _
               Top:=34, _
               Width:=102).Object
 
    'very important to clear the oTempSht object var.
    'not doing it will crash the application !!!!!
    CopyMemory oTempSht, 0&, 4

End Sub

Caller code :

Option Explicit

Private oCmbInstance As InventoryUpdater_Class

Sub MakeButton()

    Set oCmbInstance = New InventoryUpdater_Class
    oCmbInstance.MakeTheButton

End Sub

The major limitation to which I haven't found a solution to is the fact that this will only work for one CommandButton.

 

[Glory]

I had no idea VBA would recognize an object's address if you put it into an expression. What if you tried to use its window handle?

How exactly is this workaround getting around the problem? I'm not clear on why the failure is occurring in the first place during the code on post #17.

There's no runtime error, the button click event simply does not trigger.

Would you explain why that's the case? I really want to understand this better.

 

[Jaafar Tribak]

I had no idea VBA would recognize an object's address if you put it into an expression. What if you tried to use its window handle?

How exactly is this workaround getting around the problem? I'm not clear on why the failure is occurring in the first place during the code on post #17.

There's no runtime error, the button click event simply does not trigger.

Would you explain why that's the case? I really want to understand this better.

As I said, the code enters break mode ( you tested it when stepping thru)

How many buttons you want to create ? 1 button or more ?

Edit : Which Window Handle ?