Как найти кнопку в vba

This tutorial will demonstrate how to add a macro button in Excel VBA.

Once we have created macros in Excel, it is sometimes useful to create a button in the Excel worksheet to run the macro.

Also, as we  use VBA to create UserForms in Excel, we need to be able to create buttons on these UserForms to perform the OK and Cancel operations.  This tutorial will demonstrate how to add a macro button to a VBA worksheet, how to add a macro to a shape in VBA (as opposed to a button), and how to add a button onto a User Form in the VBE Editor.

Adding a Macro Button to an Excel Worksheet

Once we have created a macro in Excel VBA, we can create a button on the worksheet to run the macro.

In the Ribbon, select the Developer tab if it is visible. This tab is switched off by default so you will need to switch it on to see the tab on the ribbon.

If the Developer tab is not visible on the ribbon, click on the File tab in the Ribbon, and go down to Options. In the Customize Ribbon options, tick the Developer check box.

Click OK.

The Developer tab will appear on the main ribbon.

There are 2 types of buttons we can add to an Excel sheet – a Form Control button and an ActiveX Control Button.

Adding a Form Control Button

In the Ribbon, select Developer > Insert > Form Controls > Button.

Click and drag in the worksheet to create a button.  As soon as you release the mouse button, the assign macro dialog box will appear.

Scroll down to 1) select the macro you wish to assign to the button, and then, 2) click OK.

Right click on the button, and select Edit Text to change the text on the button.

Type an appropriate name for the button and then click off the button.

Click the button to run the macro.

To change the format of the button, right click on the button and select Format Control.

Select the options to format, and click OK.

Adding an ActiveX Button

The ActiveX button is similar to a Form Control Button but instead of assigning an existing macro to the button, we need to write code behind the Click Event of the button. It also has a lot more Properties than the Form Control Button making it a much more flexible control.

In the Ribbon, select Developer > Insert > ActiveX Controls > Command button.

Click and drag in your worksheet to create the button.   The button will appear as an Embedded Command Button.

To change the name of the command button, or the text that is displayed, right-click on the command button and click on Properties.

Amend the 1) name of the button, 2) caption of the button and 3) format of the text as required.

To add code to the button, right-click on the button once more and select View Code.

This will automatically create the click event for the button in the VBE Editor. (the click event is the most common event for command buttons and is created by default).

Type the code you require into the event that has been created.

Private Sub cmdTime_Click()
 Dim strT As String
 strT = Time()
 MsgBox "The time right now is " & strT
End Sub

Close the VBE Editor to return to your worksheet.

Click once more on your new button.  You will notice that macro does not run, but the button gets selected.  This is due to the fact that when you create an ActiveX button, you are automatically put into Design Mode. 

To run the code behind the button, click on the Design Mode button on the Ribbon to go out of Design mode, and then click the button.

Adding a Macro to a Shape in an Excel Worksheet

In the Ribbon, select Insert > Shape.

Select the shape from the drop down, and then drag in the Excel worksheet to create the shape.

Right-click on the shape, and select Assign Macro.

Select the macro to assign, and then click OK.

When you move your mouse over your shape, the mouse cursor should turn into a small hand.  Click on the shape to run the macro.

Adding Command Buttons to VBA Forms

Creating VBA UserForms is a big part of programming in VBA.  It allows you to build a professional looking user interface to communicate with the users of your VBA application.  It also allows you to totally control the user in what they are doing to your workbook.  To learn how to create UserForms in VBA, click here.

Once a UserForm is created, and controls have been added to a UserForm, buttons can be added to perform functions such as OK and Cancel.

In the Toolbox, select the Command Button control, and then click and drag in your form to create the button.

Using the Caption Property, change the caption of the command button to OK, and the Accelerator to “O”

The purpose of the Accelerator is for the user to use the keyboard to activate the button, in this case Alt+O would activate the button.

Once we have created the command button, we can double-click on the command button to go to the VBA code.  Double-clicking on the command button in the form will automatically create the Click Event for the button as this is the event that is most commonly used by command buttons.

We can now type our relevant VBA code within the procedure that has been created.

I have assigned macro to few buttons.

How can I find out inside macro which button was clicked?

I am doing as user form, where he can input peoples from family:

name1:
surname1:

name2:
surname2: |add next member|

I wish button to appear always in last row of the last added person.
For simplicity I think it is better to have like 100 empty forms in the
sheet but all invisible at the begining.
Then when user clicks add next member I simply make next rows visible,
and move button to next person. But to do that I need to know my current position.

Similar with deletion I would make rows invisible when remove button is clicked.

name1:
surname1: [remove]

name2:
surname2: [remove]

name3:
surname3: |add next member|

I need to know which remove button was clicked.

EDIT:
Found in web — what do you think, seems to be best /way

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select

Макрос и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта.

Прежде всего нужно включить его. Дополнительные сведения см. в статье Показ вкладки «Разработчик».

Макрос и средства VBA находятся на вкладке «Разработчик», которая по умолчанию скрыта, поэтому сначала нужно включить ее.

1. Перейдите в Excel > параметры…> ленты & панель инструментов.

2. В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.