VBA User Form: Add, Update, Delete and Save

[Music] in this video we are going to see how to create a data entry application with list box in Excel VBA data is saved in the same Excel file on database worksheet you can add update and delete the data to update or delete the record double-click on the record in list box and click on update or delete button to get started create a new workbook then save choose the type Excel binary workbook an Excel file saved as a binary workbook xlsb extension could be significantly smaller than the one saved as an Excel workbook now open the Visual Basic editor the easiest way is to use the keyboard shortcut alt Plus f11 you can also open it from the ribbon now insert a new user form go to insert then user form here is the properties window which allows you to customize a user form or its controls you can display it using the keyboard shortcut F4 the properties window shows the properties of the currently selected object Additionally the properties window can display the properties of the same object organized per two different criteria alphabetic or categorized for this form change the name of the application then the caption property which represents the name that appears in the title bar of the document window also change the height and width of the form we finish by defining the interior color now open the toolbox it's a relatively simple floating window that lets you add ActiveX controls to your user forms add a frame to your user form it is used for aesthetic or logical purposes edit the title of the frame from the properties window you can format this title through the font property here we choose to put the text in bold and finally change the height and width of the frame now place a label in the frame find caption from the available properties on the right side mention as title format this text as needed also change the height and width of the label now add more label controls on the user form just select this label then copy [Music] paste [Music] move a control by dragging and dropping it with your mouse in addition to the title we add the name email and phone number place a combo box to the right of the title label this control allows the user to select an item from the drop-down list also add three text boxes that allow the user to enter their name email and phone number now add the buttons when the user clicks a button your VBA application usually takes an action on this first button display the text clear by setting its caption property you can also display an image by setting its picture property modify the properties of the objects according to your needs in the same way add the buttons add update delete and Save to see what your interface looks like click on the play button located in the standard toolbar the F5 keyboard shortcut does the same you can modify the properties of several objects at the same time when you select several objects at the same time the properties window displays those properties that are common to the selection list box is the last control we'll add to user form let's find the baker property and Define the interior color of the list we also choose the etched parameter for the special effect property with this setting the object has a hollow line surrounding the control let's also change the height and width of the list box control you can use these settings to specify the horizontal spacing between selected controls save now let's add some items to the combo box so double-click on user form to open the coding area in the user form object now from the left drop down menu select user form then from the right drop down menu select activate an empty sub name user form activate will be inserted anything written in this sub will be executed when the user form is displayed the first code Line clears the combo box this way your items won't be added multiple times if you execute your code more than once the second code Line clears your own choice lines 3 and 4 add items to the combo box run user form now let's program the clear button to erase all the text boxes when the user clicks it double-click to display the code the macro is triggered by The Click event of the command button empty combo box 1 as well as text box 1 2 and 3. click on green tab to run your code now let's program the save button which allows the user to save the changes to the workbook the MSG box function displays a dialog box informing users that the registration is successful now add a new sheet to the workbook rename this sheet data in this sheet we will store the data received from the form add a header row ID is the registration identifier title name email and phone conform to the text fields of the form update time is a column to save the date and time of each recording or modification now we want to display the data in the list box open the code editor by double-clicking user form then write a refresh data sub procedure declare a worksheet variable assign datasheet variable the this workbook property Returns the workbook where the Visual Basic code is running here we identify the sheet by its name now declare a last row variable of type long this variable will contain the number of the last non-empty line to do this use the count of function which is part of the worksheet function family in VBA to count non-blank cells in column A use the width and with structure to make it easier to modify properties on the list box object column heads equals true specifies that the first cell preceding The Source range is used as the title in the list box the number of columns in the list box is 6. you can Define the width of The Columns of a list box by default the column width is 72 points 72 stitches equals 1 inch finally use the row Source property to populate the list box here it is the range of cells in the data sheet that starts with cell A1 and ends with the cell formed by the intersection of column F and the last non-blank line Now call the refresh data function in the sub user form activate function Let's test the code the data is well displayed but we want to remove the names of the columns let's go back to the code and replace A1 with A2 always the same result we add a record in Excel then we run the code column names are no longer displayed we have two cases to deal with depending on the value of last row if last row equals 1 then the value range starts from A2 to F2 otherwise the range of values starts from a to up to the cell formed by the intersection of column F and the last non-blank line Let's test the code and see if we got the desired result [Music] now let's program the add button which allows the user to add a record double-click this button to open the code start with this code which allows you to assign a worksheet variable and a non-empty last row variable then perform data checks if a field is empty the form should not be sent and an error message should be displayed where the value is missing here the drop down list must not be empty Let's test this code do the same for the other fields name email and phone number this line represents the last non-empty line its number is stored in the last row variable a new recording is made in the following line its number is last row Plus 1. this column will contain the numbers of the records the cells in this column will be filled in automatically let's write the code that fills this cell use range to return a range object that represents a single cell to give this cell the number of the record use the row function which Returns the row number of the cell in which the formula is written when we use the row function here we get 2. but we want the number of the first record to be one for that we subtract 1. now we are going to fill the cells in columns b c d and e with the values selected or entered by the user from the form in this cell insert the date and time of the recording to do this use the Microsoft Excel Now function which Returns the current system date and time finally empty all the fields after saving and call the refresh data procedure to update the contents of the list box Let's test the code of the add button the data has been saved in the data sheet the list box has been updated and the input fields are empty let's add more records now we want when we double click on a record in the list box the input fields are filled with the data from that record double-click on the list box to open the code here the object is list box from the procedures list box choose the DBL click event we delete the click event because we don't need it we start by transmitting the title to combo box from the list box the list property allows access to the elements of the list a list is an array of variants each item in the list has a row number and a column number to detect which element has been selected in the list box we use list index this is an integer between 0 and the total number of elements minus one the column number of the First Column is zero we have put one here because title is in the second column it's going to be the same code to transmit the name email and phone number to the corresponding text boxes just change the object name and column number here is the result to update or delete a record we need its number which is in the ID column for that we add a text box to the interface which saves the ID each time we double-click on a record in the list box the text box will be invisible its name here is text box 4. double-click on list box to open the code the principle is the same so just copy this line of code then put text box 4 as the object name and 0 as the column number double-click on a record and you notice that its number is displayed in text box 4. now let's program the update button before updating the data the user must select the record concerned to do this add a condition that checks that text box 4 is not empty if so a message is displayed telling the user to select a record for the rest of the code for the update button it will be the same as the code for the add button with some modification copy paste let's start the modifications we create a new variable name selected row we don't need the last row variable here the selected row variable will contain the position or the row number of the record selected by the user in the table of the data sheet when you double-click a record here its ID is displayed in text box 4. we use the match function to find this ID in column A of the data sheet the match function Returns the number 3 which represents its position in the list the match method has three parameters lookup value the value you use to find the desired value in a table lookup array an array of data either one column wide or one row high that you want to search in match type 0 minus 1 or 1 specifies what to do if an exact match is not found zero returns an error scroll down delete this line then replace last row plus 1 with selected row once the update is carried out we empty all the input Fields including text box 4. this line of code must also appear in the code of the clear button if you run this code you notice that it generates an error column A contains numeric values while lookup value is of type text so use the clng function to convert this text to a long data type here we choose to modify the name of the first recording now program the delete button which allows you to delete a recording we will copy some of the code from the update button first open the code of the update button then copy that part of the code open the code for the delete button paste deletion is like updating it requires the user to click twice on the record to know the ID of the record then with the match function you can find the number of the row to be deleted in the data sheet now write the VBA code to delete the entire row from the sheet here we specify the line to delete then we use the entire row delete method to delete the entire row from the Excel worksheet call the refresh data method to update the list box let's run this code here you need to double-click on the record the recording is deleted but we must empty all the input fields now hide this text box to do this open the properties of text box 4. then look for the visible property and set it to false we also want to hide the data sheet right click then choose hide finally add a command button in sheet 1 which opens the form on the developer tab in the controls group click insert and then under active X controls click command button click the worksheet location at which you want the upper left corner of the command button to appear select the button then click properties to edit caption double-click on the button to display the code use the user form show method to display the user form on the screen close the Visual Basic editor click design mode button to ensure design mode is off to run the VBA code that is now part of the button click the active X command button that you just created [Music]

Channel: hay kel

Views: 25,430

Keywords: excel vba, data entry, userform vba, vba add delete edit

Id: A-Y5Ld8cT40

Length: 22min 47sec (1367 seconds)

Published: Mon Jun 05 2023