Macro to Add CommandButton & Assign Macro Code

guyfos · Sep 11th 2006

I written VBA code to create a new sheet and embed a command button on it. I want to assign a macro to this button but when I click on the button the assign macro button is greyed out. I have plenty of macros saved and the VBA code has finished running so why should this be?:confused:

johnccc · Sep 11th 2006

Re: Command buttons

Hi,

Can you post your code.

John

guyfos · Sep 11th 2006

Re: Command buttons

As requested:

Code

Sheets.Add
    Worksheets(1).Name = "Output Sheet"
    ActiveCell.FormulaR1C1 = "Incomplete records"
    Columns("A:A").EntireColumn.AutoFit
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Incomplete,"">0"")"
    Range("A4").Select
    Selection.EntireColumn.Insert
    Columns("A:A").Select
    Selection.ColumnWidth = 3
    Range("C1").Select
    Selection.EntireRow.Insert
    Application.CommandBars("Control Toolbox").Visible = True
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=20, Top:=38.25, Width:=96, Height:= _
        40.5).Select

Display More

Badger101 · Sep 11th 2006

Re: Command buttons

why not just add a normal button and assign to the onaction property.

Like.

Code

ActiveSheet.Buttons.Add(298.5, 114, 72, 72).Select
    Selection.OnAction = "'Accounts Tools.xls'!addmissing"

johnccc · Sep 11th 2006

Re: Command buttons

Hi,

There seems to be something in the background that enables the attachment of macro codes. I have the same problem with ComboBoxes that don't have the tab to assign cells as data sources etc.

I'd get one that works and duplicate it on your new sheet or add the macro to a menu or as noted above.

John

Badger101 · Sep 11th 2006

Re: Command buttons

I would just stick to putting a simple button on your sheet and not going down the OLEObjects route. To tidy up my code a bit.

Code

Sub sf()
Dim butTemp
Set butTemp = ActiveSheet.Buttons.Add(298.5, 114, 72, 72)
With butTemp
    .OnAction = "'Accounts Tools.xls'!addmissing"
    .Caption = "Press ME"
End With
Set butTemp = Nothing
End Sub

guyfos · Sep 11th 2006

Re: Command buttons

Thanks guys. That's really helpful.

Dave Hawley · Sep 12th 2006

Re: Command buttons

guyfos, please pay close attention to our rules on Thread Titles. You current one (I will change) is of no use at all as it's too broad. Why not something like Macro to Add CommandButton & Assign Macro Code as that is your problem.

Anymore violations will result in your post being deleted.

Macro to Add CommandButton & Assign Macro Code

Participate now!

Share