Surveys and userforms in ExcelSurveys and userforms in Excel: You can create lovely survey forms with the help of userforms. The controls like textboxes, optionbuttons, checkboxes and image controls can make the design of the survey form easy and interesting. Next you create a command control on the form and code it appropriately. Although in the beginning the coding may look intimidating, it is in fact quite easy to master with a little perseverance. In essence, we frst define the row and worksheet variables. Next we write the code to find the blank row. Then we write the VBA code to transfer the data from the form to the Excel worksheet which we have appropriately renamed to 'survey'. The controls can also be named like 'txtSurvey' o indicate that we are naming a textbox. However, to keep things simple, we have used the default names like TextBox1 for the textbox. Some conventions need to be followed to provide names to the controls and the controls can be accessed by those names like standard variables. At present, however, this is not vital. Once the data has been transferred to the Excel worksheet we need to clear the data in the form and set the cursor in the textbox so that we can enter new data without having to delete it manually everytime. If we do not wish to enter any further data, we can click on the 'end' command button whose job is to stop the program. The images on the form contribute to making the form more interesting and intuitive. The macro code is displayed for your ready reference:
Private Sub CommandButton1_Click() 'find first empty row in database survey 'copy data to the Excel worksheet ws.Cells(eRow, 1).Value = Me.TextBox1.Value 'Clear data for new entry Me.TextBox1.SetFocus End Sub Private Sub CommandButton2_Click()
|
|