VBA Excel 2010 - How to assign a macro to a command button that was made with a macro

Question

I have a macro that creates a command button however I am unable to assign any macro to the button in the VBA

have looked at this link but its for a userform (but I'm not good enough to be able to change it to suit what I need)

The code I am currently tring is below, I'm guessing I need to add something to the With Statement but I dont know what it would be

Dim MyR As Range, MyB As OLEObject
Dim MyR_T As Long, MyR_L As Long


    Set MyR = Range("I3") 'just an example - you get that from your own script
    MyR_T = MyR.Top         'capture positions
    MyR_L = MyR.Left        '...
    'create button
    Set MyB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
    Link:=False, DisplayAsIcon:=False)

    'set main button properties
    With MyB
        .Name = "MyPrecodedButton"          'important - code must exist ... see below
        .Object.Caption = "Load UseForm"
        .Top = MyR_T
        .Left = MyR_L
        .Width = 130
        .Height = 30
        .Placement = xlMoveAndSize
        .PrintObject = True                 'or false as per your taste
    End With

actually it would be interesting to know why you Need dynamic Buttons, maybe it would be better to have a listbox and one button. But i am just curios, i don't wanna question your desgin ;) — Doktor OSwaldo, Apr 14 '16 at 11:58
@Doktor OSwaldo , I dont mind if it gets questioned, if there is another way to do it I look into it, but the reason I need dynamic button is (people here are [insult]) so that when new data sets are made there is a button for people to use right away, many of our Excel docs have over 50 sheets. I have been able to do this with a form button however people here have been known to sabotage macros — Mr.Burns, Apr 14 '16 at 12:06
have you thought about moving a button with the mouse select, or what i have done for example, a mouse double click handler ? — Doktor OSwaldo, Apr 14 '16 at 12:08
Don't have access to a computer atm, if you can create an array of buttons, the index of that button is passed to the event handler. So you can uniquely identify each button from the same event handler — NickSlash, Apr 14 '16 at 12:08

Doktor OSwaldo · Answer 1 · 2016-04-14T12:16:05.340

3

So from your own link you have posted, your code would look like this:

Set UF = ActiveWorkbook.VBProject.VBComponents("Name_of_the_userform")

With UF.CodeModule
    .InsertLines 2, _
                 "Private Sub " & MyB.Name & "_Click()" & Chr(13) & _
                 "****HERE COMES YOUR FUNCTION CALL FOR THE BUTTON****" & Chr(13) & _
                 "End Sub"
End With

But this only works with activeX Buttons. What it does is quite a hack... so if you have a better solution i would not recommend this one. What it does is this: Every ActiveX Button has a onclick function with the following Syntax: "ButtonName_Click()" If you somewhere in your code put this line, it will be executed on click. now what the code does (as in the link which you have posted), is it writes These functions into the userform code.

edited Apr 14 '16 at 12:16

answered Apr 14 '16 at 11:46

Doktor OSwaldo

5,732
20
41

2

Im not sure, but by this line Set MyB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _ Link:=False, DisplayAsIcon:=False) i think he is adding Form button, which is not ActiveX, so it not explicitly calls function name_Click(). Or am i wrong? – Luboš Suk Apr 14 '16 at 11:48
1

@Paul Ogilvie i am just helping him to adapt his own link. I didn't say it's the best way – Doktor OSwaldo Apr 14 '16 at 11:51
@Doktor OSwaldo , I only have a vague idea of what your code is trying to do (Create a Sub within a Sub?) and I honestly would know how to adapt it to the code for the button – Mr.Burns Apr 14 '16 at 12:01
@Mr.Burns activeX buttons have default behavior, that on click they call function "buttonName"_Click9(). So he insist, that you will wrote macro, which will create another macro. – Luboš Suk Apr 14 '16 at 12:03
@Mr. Burns i edited and tried to explain what it does, Remember it is not what i recommend you, but i thought you are looking for this, since you posted the link – Doktor OSwaldo Apr 14 '16 at 12:05
@DoktorOSwaldo maybe i will prefer solution in this http://stackoverflow.com/questions/23299301/how-to-create-an-activex-button-and-add-code-to-it-tell-it-what-sub-to-run-usi question, where user textualy assign only another sub call, not whole sub. (just for interest) – Luboš Suk Apr 14 '16 at 12:09
@Lubos Suk it is basically exactly the same ? I don't see what your Point is with this, but i am willing to learn ;) – Doktor OSwaldo Apr 14 '16 at 12:11
@DoktorOSwaldo its not the same. In this link, author creates only sub for button which calls sub with whole code (which is written in advance). From your code i thought you wana to wrote whole sub into VBA string and then insert it into module. Which looks like a bit crazy to me. – Luboš Suk Apr 14 '16 at 12:14
@Lubos Suk aaaah, nono, of course it would be highly recommendable to put a function call in there, not something with logic in it, but even then it is not a really good solution =) Anyway typed in the post what i thought i typed, function call, not function code – Doktor OSwaldo Apr 14 '16 at 12:15
@DoktorOSwaldo ah so sorry then :) i just said as its call to my mind from your answer. Also i know its not good solution, but i think its only working. And VBA is all about do dirty stuff – Luboš Suk Apr 14 '16 at 12:17
@Doktor OSwaldo , now I'm lost again, is this UDF or does it goes into a (Private)sub? – Mr.Burns Apr 14 '16 at 12:21
@Mr. Burns: was it does is adding a click handler for the button to the code of the userform. In this clickhandler you can put a call to an UDF if you want ... – Doktor OSwaldo Apr 14 '16 at 12:23
I have updated my question, I hope this makes it either clearer to understand what I am after and/or easier to impliment – Mr.Burns Apr 14 '16 at 12:43
@Doktor OSwaldo , I've not accepted your answer as you yourself stated you wouldnt recommend it and after ages of looking at it and looking at related coding I undertand what it does (not 100% but enough to get by), it is a good answer and people who understand more about VBA than I do will be able to use it but the average(and below average) user wouldnt be able to make heads nor tails of it – Mr.Burns Apr 14 '16 at 13:13

score 2 · Accepted Answer · edited May 23 '17 at 12:31

2

Use .onAction method

Something like this

Sheets("someVeryFunnySheetName").buttons("someSeriousButtonName").onAction = "macroName"

Here is one example, if you wana to pass parameter to that macro (axleOutputSHeetCounter is some integer i think)

With chartSheet.Buttons("closeOutputSheet")
    .OnAction = "'Module7_Axle.closeOutputSheet """ & axleOutputSheetCounter & """'"
    .Characters.text = "Delete sheet"
    .Characters.Font.Size = 16
End With

edit: for activeX buttons here you can find question with same issue and working solution

edited May 23 '17 at 12:31

Community

1
1

answered Apr 14 '16 at 11:44

Luboš Suk

1,526
14
38

I have tried the `.OnAction` method, they dont work for command buttons, thought it does work for Form Control buttons, I am using command buttons so other people cant easily mess with my macros/worksheets – Mr.Burns Apr 14 '16 at 11:51
1

@Mr.Burns and is here reason why you need activeX buttons instead of form buttons? (i switched to form buttons long time ago, after MS glorious update which f*cked up all activeX objects) – Luboš Suk Apr 14 '16 at 12:04
yea to edit ActiveX buttons you need to load the doc with macros disabled and not many people here know that so they cant do anything with my buttons, form buttons you can right click on them and change what you need(or remove them) – Mr.Burns Apr 14 '16 at 12:12
@Mr.Burns no no :) i think issue, that after security update, all activeX objects start to throwing errors. And you need to delete some temporary files and remake all sheets. Which was very funny to explain to customers :) – Luboš Suk Apr 14 '16 at 12:19
The reason I accepted your answer is the feasibility of it, it is easier to impliment, understand and change depending on a users need – Mr.Burns Apr 14 '16 at 13:10

VBA Excel 2010 - How to assign a macro to a command button that was made with a macro

2 Answers2

Linked