怎么单击按钮获取后台数据_从Excel按钮单击获取数据

怎么单击按钮获取后台数据

Here's a simple example that shows how you can get data from Excel button clicks. There are 3 buttons on one sheet, and if you click one, the date and time are recorded on another sheet, along with your name and the button number.

这是一个简单的示例，显示了如何从Excel按钮单击中获取数据。 一张纸上有3个按钮，如果单击一个，则日期和时间以及您的姓名和按钮号会记录在另一张纸上。

带按钮表 (Sheet with Buttons)

This example could be used during a short test, with the participant clicking a button at specific intervals, to indicate their current stress level.

该示例可以在短期测试中使用，参与者以特定的时间间隔单击按钮以指示其当前的压力水平。

In this sample file, there is a sheet named Input, shown below. The participant's name goes at the top of the sheet, in the underlined cell.

在此示例文件中，有一个名为Input的工作表，如下所示。 参与者的姓名位于工作表顶部带下划线的单元格中。

Then, during the test, click the buttons that match your stress level, and Excel records the information.

然后，在测试过程中，单击与您的压力水平相匹配的按钮，然后Excel记录信息。

具有收集数据的工作表 (Sheet with Collected Data)

On another sheet, named TestData, the records are stored.

在另一张名为TestData的表上，存储了记录。

• A – Date and time of button click
  A –单击按钮的日期和时间
• B - Name from Input Sheet
  B-输入表中的名称
• C - Button number that was clicked
  C-单击的按钮编号

设置输入表 (Set Up the Input Sheet)

In this example, I inserted 3 buttons from the Form Controls, on the Developer Tab.

在此示例中，我在“开发人员”选项卡上的“表单控件”中插入了3个按钮。

NOTE: After you add a button, the Assign Macro window opens. Click Cancel, to close that window – you can assign a macro later.

注意 ：添加按钮后，“分配宏”窗口将打开。 单击取消，关闭该窗口–您以后可以分配宏。

After you add each button, right-click on it, and click Edit Text

添加每个按钮后，右键单击它，然后单击“编辑文本”

Then, type a number on each button – these buttons are 1, 2 and 3.

然后，在每个按钮上键入数字-这些按钮是1、2和3。

创建一个名称单元 (Create a Name Cell)

Near the top of the Input sheet, there's a cell with a bottom border, and "Name:" is in the cell to the left of it.

在输入表顶部附近，有一个带有底部边框的单元格，在其左侧的单元格中有“名称：”。

The underlined cell is named "UserName", and the macro will get the value from that cell. There are instructions for naming ranges on my Contextures site.

带下划线的单元格名为“ UserName”，宏将从该单元格获取值。 在我的Contextures网站上有有关命名范围的说明 。

添加输入说明 (Add Input Instructions)

Above the buttons, I added a question – "What is your current stress level?".

在按钮上方，我添加了一个问题–“您当前的压力水平是多少？”。

Then, I formatted that text in 18 pt bold font, so it stands our on the worksheet.

然后，我以18 pt粗体将该文本格式化，因此它可以在工作表上显示。

Below the buttons, I added text to show that buttons at the left represent low stress, and buttons at the right are for high stress.

在按钮下方，我添加了文本以显示左侧的按钮表示压力较小，而右侧的按钮表示压力较高。

Low ============> High

低============>高

按钮宏 (Macro for Buttons)

There's one macro in the workbook, and it adds the data to the TestData sheet. All 3 buttons will run the same macro.

工作簿中只有一个宏，它将数据添加到TestData表中。 所有3个按钮将运行相同的宏。

Here's the macro code, and it is stored in a regular code module in the workbook.

这是宏代码，它存储在工作簿的常规代码模块中。

Sub AddButtonData()

Dim wsData As Worksheet
Dim wsIn As Worksheet
Dim nextRow As Long

Dim BtnText As String
Dim BtnNum As Long
Dim strName As String

Set wsIn = Worksheets("Input")
Set wsData = Worksheets("TestData")

With wsData
  nextRow = .Cells(.Rows.Count, "A") _
    .End(xlUp).Offset(1, 0).Row
End With

With wsIn
  BtnText = .Buttons(Application.Caller).Caption
  BtnNum = CLng(BtnText)
  strName = .Range("UserName").Value
End With

With wsData
  With .Cells(nextRow, 1)
    .Value = Now
    .NumberFormat = "mm/dd/yyyy hh:mm:ss"
  End With
  .Cells(nextRow, 2).Value = strName
  .Cells(nextRow, 3).Value = BtnNum
End With
    
End Sub

宏做什么 (What the Macro Does)

Here's a quick overview of what the AddButtonData macro does.

这是AddButtonData宏的作用的快速概述。

• First, the macro sets variables for the Input sheet and the TestData sheet.
  首先，宏为“输入”表和“测试数据”表设置变量。
• Then, it finds the next available row in column A, on the TestData sheet.
  然后，它在TestData表上的A列中找到下一个可用行。

From the Input sheet,

在输入表中，

• the macro uses Application.Caller, to figure out which button was clicked, and gets the caption from that button.
  宏使用Application.Caller来确定单击了哪个按钮，并从该按钮获取标题。
• That caption text is converted to a number
  该字幕文本将转换为数字
• The name comes from the "UserName" cell.
  该名称来自“ UserName”单元。

On the TestData sheet

在TestData表上

• The current date and time (Now) is added in column A, and formatted.
  当前日期和时间(现在)已添加到列A中并进行了格式化。
• The name is added to column B
  名称已添加到B列
• Button number goes into column C
  按钮编号进入C列

将宏分配给按钮 (Assign Macro to Buttons)

The final step is to assign that macro to each of the three buttons.

最后一步是将该宏分配给三个按钮中的每个按钮。

• Right-click on a button, and in the popup menu, click Assign Macro
  右键单击一个按钮，然后在弹出菜单中单击“分配宏”

• In the list of macros, click AddButtonData, and click OK
  在宏列表中，单击“ AddButtonData”，然后单击“确定”。

Repeat those steps for the remaining buttons.

对其余按钮重复这些步骤。

测试按钮 (Test the Buttons)

To test the buttons, follow these steps:

要测试按钮，请按照下列步骤操作：

• First, put your name in the underlined cell.
  首先，将您的名字放在带下划线的单元格中。
• Then, click one of the buttons
  然后，单击按钮之一
• Wait a couple of seconds, and click a different button
  等待几秒钟，然后单击其他按钮

To see the data that was collected, go to the TestData sheet. You should see your name there, along with the date/time information, and the button numbers.

要查看收集的数据，请转到TestData表。 您应该在此处看到您的名字，以及日期/时间信息和按钮编号。

定制说明 (Customization Notes)

The sample file only has 3 buttons, and you could add more, if needed. Just put a unique number on each button, so it can be identified.

该示例文件只有3个按钮，您可以根据需要添加更多按钮。 只需在每个按钮上放置一个唯一的数字，即可对其进行识别。

Instead of using Form Control buttons, you could use shapes, such as rounded rectangles. That would give you a wider range of colours, instead of grey, grey or grey.

除了使用“表单控制”按钮，您还可以使用诸如圆角矩形的形状。 那将为您提供更广泛的颜色，而不是灰色，灰色或灰色。

The sample file has a cell where the name is entered, and the macro uses that value. Another option is to get the name from the Office installation, or from the network.

该示例文件有一个用于输入名称的单元格，宏使用该值。 另一个选择是从Office安装或从网络获取名称 。

获取样本文件 (Get the Sample File)

To download the sample workbook for this example, go to the Excel Sample Files page on my Contextures website.

若要下载此示例的示例工作簿，请转到Contextures网站上的“ Excel示例文件”页面 。

In the UserForms, VBA, Add-ins section, look for UF0047 - Click Button to Capture Data.

在“用户窗体，VBA，加载项”部分中，查找UF0047-单击“按钮以捕获数据” 。

The zipped file is in xlsm format, so be sure to enable macros when you open the file, if you want to test the buttons.

压缩文件为xlsm格式，因此，如果要测试按钮，请确保在打开文件时启用宏。

翻译自: https://contexturesblog.com/archives/2019/03/21/get-data-from-excel-button-clicks/

怎么单击按钮获取后台数据