Creating a form control button using VBA and save the file in a particular folder by clicking the button

[dino4u86]

I tried to create a button in a file using VBA editor but I also want the button to action a task when clicked but that is not happening

When clicked on the button, a copy of the file should be saved in a particular folder.

Creating a button works but saving the file doesnt. Need help

Code below:

'Creating a button
ActiveCell.Offset(2, 0).Select
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 = "Save_File"
        .Font.Bold = True
        .Font.Name = "Calibri"
        .Font.Size = 10
        .Name = "Save File " & c.Row
        .Shapes("BtnMacro").OnAction = ThisWorkbook.SaveAs Filename:="\\abcd\ ".xls", FileFormat:=56
    End With

Regards,

 

[smozgur]

I can see three problems.

1. OnAction is a string property. So, you can't use it like a callback in JavaScript or PHP, but assign as the sub procedure name as string that you need to execute when the button is clicked.
2. Buttons.Add() doesn't have a property called Shapes. You should directly use Buttons.Add(...).OnAction property.
3. This is not related with the OnAction, but the FileName argument of the SaveAs method does not look correct in your code.

The following is how I will change your code: Basically created a separate sub procedure called actionSaveFile, and set button's OnAction property by using the name of this sub procedure as string. You might need to adapt the file name as you need.
I didn't make any changes in the other parts.

Sub MyMacro()
'Creating a button
ActiveCell.Offset(2, 0).Select
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 = "Save_File"
        .Font.Bold = True
        .Font.Name = "Calibri"
        .Font.Size = 10
        .Name = "Save File " & c.Row
        .OnAction = "actionSaveFile"
    End With
End Sub

Sub actionSaveFile()
    ThisWorkbook.SaveAs Filename:="abcd.xls", FileFormat:=56
End Sub

Hope this helps.