Create a button through VBA

dino4u86

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

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Hi dino4u86, welcome to Mr Excel.

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Nov 11, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
A form button is what I prefer. Please help with the code
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

dino4u86

New Member
Joined
Nov 11, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Hi dino4u86, welcome to Mr Excel.

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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top