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

dino4u86

New Member
Joined
Nov 11, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA Code:
'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,
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,211
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.

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,273
Messages
5,571,248
Members
412,374
Latest member
Nagelgal
Top