Jump to Download the Sample File - Download the sample Excel UserForm file; Download the sample file with. Excel UserForms for Data Entry. Excel Macro Examples & Free Downloads. One of the best ways to learn Excel VBA is by exploring code samples & examples. That is why, we publish detailed. This Workbook shows how you can use the Worksheet_Calculate Event and a Validation list to have formulas automatically reference different Workbooks. The code has been set so it works in both Excel 97 and 2000. The reason it uses the Worksheet_Calculate Event and not the Worksheet_Change is because a Validation list will not fire the Worksheet_Change Event in Excel 97. This download contains four Workbooks. They should be all saved to the same folder on your hard drive. Then open ' ChangingExternalFormulas'. To create this Userform, execute the following steps. If the Project Explorer is not visible, click View, Project Explorer. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below. Add the Multipage control, labels, text boxes (first at the top, the second below the first), frame, option buttons (first at the left, the second at the right), list box, Image control and command button. Once this has been completed, the result should be consistent with the empty version of the Userform shown earlier. For example, create a Multipage control by clicking on Multipage from the Toolbox. Next, you can drag a Multipage control on the Userform. When you arrive at the Gender frame, remember to draw this frame first before you place the two option buttons in it. You can change the names and the captions of the controls. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of the controls, but it is not necessary here because we only have a few controls in this example. To change the caption of the Userform, Multipage tabs, labels, frame, option buttons and command button, click View, Properties Window and click on each control. To show the Userform, place a on your worksheet and add the following code line. Private Sub ListBox1_Click() If ListBox1.ListIndex = 0 Then Image1.Picture = LoadPicture('C: test Mountains.jpg') End If If ListBox1.ListIndex = 1 Then Image1.Picture = LoadPicture('C: test Sunset.jpg') End If If ListBox1.ListIndex = 2 Then Image1.Picture = LoadPicture('C: test Beach.jpg') End If If ListBox1.ListIndex = 3 Then Image1.Picture = LoadPicture('C: test Winter.jpg') End If End Sub Explanation: these code lines load a picture depending on the selected item in the list box. Double click on the OK button. Add the following code lines. Private Sub CommandButton1_Click() Dim emptyRow As Long 'Make Sheet1 active Sheet1.Activate 'Determine emptyRow emptyRow = WorksheetFunction.CountA(Range('A:A')) + 1 'Transfer information Cells(emptyRow, 1).Value = TextBox1.Value Cells(emptyRow, 2).Value = TextBox2.Value If OptionButton1.Value = True Then Cells(emptyRow, 3).Value = 'Male' Else Cells(emptyRow, 3).Value = 'Female' End If Cells(emptyRow, 4).Value = ListBox1.Value 'Close Userform Unload Me End Sub Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Next, we transfer the information from the Userform to the specific columns of emptyRow. Finally, we close the Userform.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
September 2018
Categories |