• Home  / 
  • VBA
  •  /  How to Update and Delete Using Excel VBA Userform ? | VBA Userform

How to Update and Delete Using Excel VBA Userform ? | VBA Userform

how_to_update and delete records using Userform

It’s useful for making a task easy in business areas like Banking Record, Personal Finance, Stock Reporting, Recordkeeping, Automate Training room booking, Hotel Room booking, Car Booking, booking IT resources and approval of business expenses such as refreshments, conveyance, stationery etc.

This technique will help User access the data in the VBA Userform only and then if he\she wants to update any info, he\she can update the same by pressing “UPDATE” button and the same records will be updated in the sheet (Database). Similarly, User can delete the records if required.

Let’ start with the recordset which contains a lot of info like below.
Picture 1

(Picture 1)

First, Create a VBA UserForm like one in below picture.

Picture 2

(Picture 2) VBA Userform

Steps to create VBA UserForm are mentioned below:

  1. Press Alt + F11.
  2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.
    UserForm Creation 1
  3. Add the controls listed in the table below. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform.4. Change the names and captions of the controls according to the table above. 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 controls. This will make your code easier to read. To change the names and captions of the controls, right click on VBA Userform and click on each control.

UserForm Creation 2


Once you are done creating a VBA userform which looks like UserForm shown earlier with another 2 command buttons; “Update” and “Delete”. This VBA Userform will pull up all the information from the sheet when I will choose any value in “SL No” in UserForm.

Every time you choose the “SL No” from the drop down the value for the other fields will be changed. First click on the drop down box in UserForm next to “SL No” to set the rowsource property, see image below for your reference.
UserForm Creation 3

You should set a name to Object in VBA Userform like I did by naming the “SL No” drop down box as cmbslno.

Update records using Userform

(Picture 3) VBA Userform


Now if you choose any value from the box then the value of the other boxes will auto populated. To do so I have applied simple Vlookup formula using VBA Code, Please refer the codes.


We have already covered how to create userforms and how to set the permission in the sheet using VBA.


Now double click on the box (cmbslno) and paste the below code.

Code to update records using Userform

(Picture 4) VBA userform

If you look at the code very carefully you will notice that I have applied vlookup formula to  pull up the value for the other boxes. You need to give a proper name for all the text boxes you are going to use in the form.


Now you choose any value from the SL No drop down box and you will see the values are coming from the excel sheet as shown in Picture 2.


Now if you want to update any info’s then what are the things need to do.

Whatever value you choose from the drop down it is fetching the value of corresponding columns, Right?

So we need to first select the entire row then we can set the cell value as per our requirement.


If you choose 1 from my SL No drop down then it is showing the value from row no 2. Isn’t it? As the first row is the header. Now we need to play with this trick.


Create a Command Button and give the name like cmdupdate and change the caption to Update.

Update records using Userform

(Picture 5) VBA userform


After setting the above properties double click on the “Update” button and paste the below code between sub and end sub

Your code will look like below

Picture 4

(Picture 6)

After pasting the above code then run the code and change the value of any field and click on update button. You will see that changes made in value by you in the Userform for that SL No header is reflected in the Sheet’s respective Cell.

We can modify the code a little bit also. After one record is modified if we want to modify another record then….Do we need to start from the scratch once again?

No…what we can do is we simply give a message box which will prompt that records have been modified. Do you wish to continue?

For that, we need to add the below code

Now if you run the code and choose any SL No then you will get all the values for the other boxes. If you change any value of any box and click on “Update” button, you will see the same value is now updated in the sheet and it will give you a message that SL No has been updated and do you want to update other SL No or not.
Update records using Userform

(Picture 7) VBA userform


If you click on Yes then the same form will be reloaded and by clicking on No you will be redirected to the sheet where you have updated the data.

We will apply the theory in case of delete and then apply the below code


Download Working Files

Now if you want you may hide the sheet from the user. So that user will update or delete the data using the userform only. But here is one problem. Every time you need to choose the value from the SL no dropdown button.

Why not such type of userform where I need to choose only 1 time and then I can add, update, delete, previous, next all the button in a single user. We will create the same in the next blog.


Learn more about our Advanced Excel VBA Tutorials and our Free Excel VBA Tutorials
Comment below regarding Userform

About the author

Niladri Sekhar Biswas

Hi, I have 9 years of experience working in Microsoft Excel. I am also an Excel and VBA corporate trainer. Writing about Excel and sharing my knowledge is my passion. I hope you learn more about Excel from my articles.

  • Len Silva

    Thank you for this sample. As a barely literate coder, it fits almost exactly with my needs.
    I do have a problem though.
    In my data, col A is UNIT No (Condo), beginning with 101. Selecting it in the combobox fills my userform perfectly.
    However, when I update, it writes a new row. For instance UNIT 120 is in row 21, but when I run update, it puts the data in a new row 121.
    I just cannot see what I may have done wrong.

    Thanks for any help

  • Niladri Biswas

    Hi Martin, thanks for reading the article and sorry for my delayed response. whenever you choose any id from the box it selects that row and update the same what ever the values are there in the Userform. I downloaded the file and checked it and it is working fine. Please go through the code and play with it.

    The box is text box. From 2010 there is no calendar control but you can use format function to get your desired date format. Please let us know if there is any concerns. we will be happy to help you out.

    • Kirk Martin

      Thanks Niladri

      I have gone through the code but it does not work for me. My id that I select from the drop down in the user form finds all of the relivent information then only thing that I can see between yours is that in your SL_No column in the sheet has some sort of formula “=Row()-1” and they run sequentially. All of mine are staff No so thay are all unique and for some reason when I run the update it puts it into the first row and not the row it was looking up pease help?

      • Niladri Biswas

        So you need to find the Staff ID first and for that you need to use a loop to get the row no and then update the row. In SLno_change module add the below code
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        For x = 2 To lr
        If Cells(x, 1) = SLNo Then
        End If

        In update moule comment out the below code

        ‘SLNo = Me.cmbslno.Value
        ‘Dim rowselect As Double
        ‘Dim msg As String
        ‘Dim ans As String
        ‘rowselect = Me.cmbslno.Value
        ‘rowselect = rowselect + 1

        add this one
        rowselect = ActiveCell.Row

        you need to change the msgbox section as per your requirement. Let us know if it does not work

        • Niladri Biswas

          Please share your ID so that I can send you the update file. I have also changed the row() -1 one and checked with the unique Ids and it works for me

          • Kirk Martin

            How do I do that new to here

  • Kirk Martin

    Hi I have been using your code to do what yours does but when I hit the update button it always goes to line 1 and does not update the line that it should. Also how do you get the date box to display corectley ie dd-mm-yyyy