Trouble executing task of command button in a module when clicked 1

[jhogie]

I posted a similar/same thread yesterdaym, however it has seemed to have dissapeared.

I have created a command button with caption "update" using the following code (wkb = ThisWorkbook).

wkb.Worksheets(1).OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=30, Top:=270, Height:=30, Width:=100).Select
Worksheets(1).OLEObjects("CommandButton1").Object.Caption = "Update"

When the button is 'clicked' I would like it to run the procedure titled "PopulateSummary". Here is my code for that;

Private Sub CommandButton1_Click()
  PopulateSummary
End Sub

The button is created, but nothing happens when it is clicked. I've wasted several hours on this and any help/suggestions would be appreciated.

- Hogie

 

[Bong]

Is the subroutine, "PopulateSummary", on the same sheet as the button, or in a module? If neither, it won't be found I think.

_________________
Bob Rashkin

 

[jhogie]

Thanks for the quick reply, I'm fairly new to programing in excel, so I'm not sure if this is answering your question.

The "populateSummary" procedure is written in the same module as all my other coding.

As a second question on that note... I used to be able to see all my different procedures on one page. But now I have to toggle between them in the drop down menu. Do you know how I go back to seeing them all on one page?

 

[jhogie]

Bong - I think you were on the right track. I need to add the 'PopulateSummary' procedure in the code for the specific sheet ("Summary"). Do you have any idea how I could do this from my main code module?

Thanks again

 

[DaveInIowa]

How about a different approach to creating the button?

Sub AddUpdateButton()
    Dim xlBtn As Excel.Button
    
    Set xlBtn = ActiveSheet.Buttons.Add(270, 30, 100, 30)
    With xlBtn
        .Caption = "Update"
        .OnAction = "PopulateSummary"
    End With
    
End Sub

Sub PopulateSummary()
    MsgBox "PopulateSummary executed"
End Sub

 

[jhogie]

SUCCESS... THANKS A BUNCH

I didn't know of any other way to make the button, but that did the trick... and so much easier.

I also figured out how to see the whole module on one screen. For anyone curious - there is a little button on the bottom left of the module page; "Full Module View".

- Hogie