Create a button through VBA

[dino4u86]

Hi,

Im trying to create a button through VBA code and following that I will assign a macro to save the file in a specific folder

'Creating a button to save workbook
ActiveCell.Offset(2, 0).Select
Dim newButton As OLEObject
With ActiveCell
Set newButton = ActiveSheet.OLEObjects.Add
ClassType = "Forms.CommandButton.1",_
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, _
Top:=.Top, _
Height:=.Height, _
Width:=.Width)
newButton.Object.Caption = .Row
newButton.Object.Font.Size = 6
newButton.Object.Font.Bold = True
newButton.Object.WordWrap = True
newButton.Name = "Save File" & .Row
newButton.Object.Font.Name = "MS Gothic"

End With

Regards,
Dean

 

[GWteB]

Hi dino4u86, welcome to Mr Excel.

How about ...

Sub dino4u86()

    ActiveCell.Offset(2, 0).Select
    Dim newButton As OLEObject

    With ActiveCell
        Set newButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
                                                   Link:=False, DisplayAsIcon:=False, _
                                                   Left:=.Left, Top:=.Top, _
                                                   Height:=.Height, Width:=.Width)
        newButton.Object.Caption = .Row
        newButton.Object.Font.Size = 6
        newButton.Object.Font.Bold = True
        newButton.Object.WordWrap = True
        newButton.Name = "Save File" & .Row
        newButton.Object.Font.Name = "MS Gothic"
    End With
End Sub

 

[RoryA]

Do you really need an activex button rather than a Form button? The latter are much more stable, and easier to assign a macro to in code.

 

[dino4u86]

A form button is what I prefer. Please help with the code

 

[GWteB]

How about ...

Sub dino4u86_r2()

    Dim c As Range

    Set c = ActiveCell.Offset(2, 0)
    With ActiveSheet.Buttons.Add(c.Left, c.Top, c.Width, c.Height)
        .OnAction = "BtnMacro"
        .Caption = c.Row
        .Font.Bold = True
        .Font.Name = "MS Gothic"
        .Font.Size = 6
        .Name = "Save File " & c.Row
    End With
End Sub

Sub BtnMacro()
     MsgBox Application.Caller
End Sub

 

[dino4u86]

Hi dino4u86, welcome to Mr Excel.

How about ...

Sub dino4u86()

    ActiveCell.Offset(2, 0).Select
    Dim newButton As OLEObject

    With ActiveCell
        Set newButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
                                                   Link:=False, DisplayAsIcon:=False, _
                                                   Left:=.Left, Top:=.Top, _
                                                   Height:=.Height, Width:=.Width)
        newButton.Object.Caption = .Row
        newButton.Object.Font.Size = 6
        newButton.Object.Font.Bold = True
        newButton.Object.WordWrap = True
        newButton.Name = "Save File" & .Row
        newButton.Object.Font.Name = "MS Gothic"
    End With
End Sub

Hi GWteb,

Thank you for the above fix. However 2 issues I am fixing. Im trying to name the button as "Save File" whereas the name being returned is the rowq number. How can I fix that? If you can also help with the remaining part of the code of how to save the file in a specific folder. Il change the folder link later. How do I also increase the size of the button as it too small?

Thanks in advance.

 

[GWteB]

The code is derived from your post #1 code. Therefore, the button's appearance is about the same as an Active-X command button would be with your code.

Im trying to name the button as "Save File" whereas the name being returned is the rowq number.

For the record, there is a difference in a button's name and what is visible as text on the button's surface, the caption. Both can be the same. In case of multiple buttons on your worksheet all of them can have the same name and caption. It should be possible however (one way or the other) to distinguish between the buttons if you want to use code that can be used multiple times, such as the BtnMacro procedure in the example.

How do I also increase the size of the button as it too small?

The button takes its position on the screen and its dimensions from the cell two rows below the active cell, as from your original code. Changing row height and column width of that particular cell before running the code will change the dimensions of the button as well. Another way could be to enter location and dimensions as numeric values directly on this line:

With ActiveSheet.Buttons.Add(c.Left, c.Top, c.Width, c.Height)

If you can also help with the remaining part of the code of how to save the file in a specific folder.

That would be no problem.