VBA Create Buttons

[somenoob]

Hi everyone, can anyone help me?

i have to create a button in vba.

in my first spreadsheet, eg.'List', there will be a button to click to create a new spreadsheet. In the new spreadsheet created, i want a button to be auto created with my macro in the cell C1.

How do i code it?

 

[Misca]

I like to use the following sub when creating buttons:

Sub CreateButton(Rng As Range, Txt As String, Optional Macro As String)

Dim MyButton As Object

Application.ScreenUpdating = False

With Rng.Parent
    Set MyButton = .Buttons.Add(10, 10, 10, 10) 'Adds the button
End With

With MyButton
'Places the button:
    .Left = Rng.Left
    .Top = Rng.Top
    .Width = Rng.Width
    .Height = Rng.Height
    
    .Characters.Text = Txt  'Button text
    If Len(Macro) > 0 Then  'If you gave it the optional macro parameter
        .OnAction = Macro
    End If
End With

End Sub

To use it just add a line in your main code:

Call CreateButton(Range("C1:D2"), "This is my text", "Macro1")

 

[somenoob]

Thanks for the reply!

It Works THANKS!!!

 

[Misca]

Try running the code in an empty workbook (or empty sheet) to see what it does:

The .Top=Rng.Top etc. places the button where you want it: the macro can't run unless you've given it the required parameters, ie. the range to cover + the text on the button.

I left the Macro parameter optional just in case so you can create buttons with or without macros attached to it.

 

[somenoob]

How do i create a button to be there on start up.

For example, when i run the excel, the spreadsheet 'List' will have 2 buttons there already.

Because i do not want keep on creating a button whenever i create a new excel.

i just want to copy in the codes and everything would be there.

 

[Misca]

I'm not sure what do you mean exactly but if you want the buttons etc. to be there when you open a new workbook you might want to save the basic workbook as a template and create the new workbooks using that.

And if your "create a new excel" means you want to have the buttons exc. on a new worksheet then you need to write a macro that does whatever you want to have on the new worksheet (or use a template sheet - hidden or visible - that you copy as new worksheet) and run a macro to add the new worksheet.

You could also use events to trigger the macro as well but it's better if you don't: Usually EventMacros tend to cause a lot more problems than they solve.

 

[somenoob]

I see. Thanks for your comments

 

[somenoob]

I have noticed a problem.

When i am using this on Excel 2003, i am able to create a new spreadsheet and have the button name and macro working.

But when i usd Excel 2007, the new spreadsheet created successfully displayed the button but it is labeled button 1 instead of the name i have set and also the macro does not work.

Please help me

 

[Misca]

Could it be it's your other code that's not working? The code above should work on 2007 as well as on 2003.

If you want to name the buttons created with the macro all it takes is one more parameter to the CreateButton macro:

Sub CreateButton(Rng As Range, Txt As String, Optional Macro As String, Optional BName As String)

Dim MyButton As Object

Application.ScreenUpdating = False

With Rng.Parent
    Set MyButton = .Buttons.Add(10, 10, 10, 10) 'Adds the button
End With

With MyButton
'Places the button:
    .Left = Rng.Left
    .Top = Rng.Top
    .Width = Rng.Width
    .Height = Rng.Height
    
    If Len(BName) > 0 Then
        .Name = BName
    End If
    
    .Characters.Text = Txt  'Button text
    If Len(Macro) > 0 Then  'If you gave it the optional macro parameter
        .OnAction = Macro
    End If
End With

End Sub

Here's one way to use it when creating new sheets:

With Sheets.Add
    Call CreateButton(.Range("D2"), "Click Me!", "Macro1", "MyNewButton")
End With

 

[somenoob]

The codes im using is..

Sub CreateButton(Rng As Range, Txt As String, Optional Macro As String)
Dim MyButton As Object
Application.ScreenUpdating = False
With Rng.Parent
Set MyButton = .Buttons.Add(10, 10, 10, 10) 'Adds the button
End With
With MyButton 'Places the button:
.Left = Rng.Left
.Top = Rng.Top
.Width = Rng.Width
.Height = Rng.Height

.Characters.Text = Txt 'Button text
If Len(Macro) > 0 Then 'If you gave it the optional macro parameter
.OnAction = Macro
End If
End With
End Sub
---------

Call CreateButton(Range("C2:E3"), "Delete Redundant fields (Fields with 0 in column I)", "ThisWorkbook.DeleteZeroRows")


is there anything wrong?
---------

Call CreateButton(.Range("D2"), "Click Me!", "Macro1", "MyNewButton")

what is "click Me!" and "myNewButton"?