Excel getdata download#
To download the sample workbook for this example, go to the Excel Sample Files page on my Contextures website. Another option is to get the name from the Office installation, or from the network. The sample file has a cell where the name is entered, and the macro uses that value. That would give you a wider range of colours, instead of grey, grey or grey. Instead of using Form Control buttons, you could use shapes, such as rounded rectangles. Just put a unique number on each button, so it can be identified. The sample file only has 3 buttons, and you could add more, if needed. You should see your name there, along with the date/time information, and the button numbers. To see the data that was collected, go to the TestData sheet. Wait a couple of seconds, and click a different button.First, put your name in the underlined cell.Repeat those steps for the remaining buttons. In the list of macros, click AddButtonData, and click OK.Right-click on a button, and in the popup menu, click Assign Macro.The final step is to assign that macro to each of the three buttons. The current date and time (Now) is added in column A, and formatted.The name comes from the “UserName” cell.That caption text is converted to a number.the macro uses Application.Caller, to figure out which button was clicked, and gets the caption from that button.Then, it finds the next available row in column A, on the TestData sheet.First, the macro sets variables for the Input sheet and the TestData sheet.Here’s a quick overview of what the AddButtonData macro does.
Excel getdata code#
Here’s the macro code, and it is stored in a regular code module in the workbook. There’s one macro in the workbook, and it adds the data to the TestData sheet. Then, I formatted that text in 18 pt bold font, so it stands our on the worksheet.īelow the buttons, I added text to show that buttons at the left represent low stress, and buttons at the right are for high stress. There are instructions for naming ranges on my Contextures site.Ībove the buttons, I added a question – “What is your current stress level?”. The underlined cell is named “UserName”, and the macro will get the value from that 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. Then, type a number on each button – these buttons are 1, 2 and 3. Click Cancel, to close that window – you can assign a macro later.Īfter you add each button, right-click on it, and click Edit Text NOTE: After you add a button, the Assign Macro window opens. In this example, I inserted 3 buttons from the Form Controls, on the Developer Tab. On another sheet, named TestData, the records are stored. Then, during the test, click the buttons that match your stress level, and Excel records the information. The participant’s name goes at the top of the sheet, in the underlined cell. In this sample file, there is a sheet named Input, shown below. This example could be used during a short test, with the participant clicking a button at specific intervals, to indicate their current stress level. 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. Here’s a simple example that shows how you can get data from Excel button clicks.
0 kommentar(er)
