Excel Vba Userform Examples Free Download

| Posted on by



Download the sample file with an enhanced Excel UserForm, with comboboxes; An Excel UserForm course is also available - Learn Excel UserForms for Data Entry. Related Tutorials. UserForm with ComboBoxes. UserForm Dependent ComboBoxes. UserForm ComboBox VBA. UserForm TextBox Validation Code. UserForm with Help Pages. Dear friends, As you see this is the second and last part of the tutorial 40 Useful Excel Macro VBA examples.In previous article i had published the first 20 examples. Download a FREE Excel Workbook with all 40 Examples. At the end of this article, you will have a link to download a FREE copy of all 40 useful excel macros collection. Next, you can drag a text box on the Userform. When you arrive at the Car frame, remember to draw this frame first before you place the two option buttons in it. Change the names and captions of the controls according to the table below. Names are used in the Excel VBA code. Captions are those that appear on your screen.

Excel Scorecard

Excel scorecard, Excel spreadsheet for design and implemented a balanced scorecard, excel dash board, you can read more about this excel in excel scorecard

Excel scorecard. (1,2 MB)

Excel scorecad - Format .rar (1,1 MB)

Excel 5s - Excel lean manufacturing

Excel 5s template which allow to do an audit planning that support the 5s lean management in our company.

Excel 5s(103 KB)

Excel 5s - Format .rar (32 KB)

Excel VBA Macro Management Stocks and Warehouses

Excel Macro vba applied to the management of stocks and warehouses, automatic reports of current status of stocks, calculation of minimum security stock, you can read more about this macro excel inexcel stocks

Excel Macro Stocks and Warehouses. (495 KB)

Excel VBA Gantt Macro, planification of project with Gantt Chart.

Excel Macro that automatically creates the Gantt chart of the project, allows us to make the planning of any type of project, report of advance or delay of the project and of each activity that include it, you can read more about this macro excel inexcel gantt

Excel Macro Gantt - Project Planning. (1,19 MB)

Excel Macro Gantt - Project Planning - Format .rar (245 KB)

Excel VBA Macro MRP - Calculation and explosion of MRP

Macro vba made in excel to calculate MRP, and to facilitate the management of stocks and of production in the manufacture context, you can read more about this macro excel inexcel mrp

Excel Macro MRP - Calculation and explosion of MRP. (1,53 MB)

Excel Macro MRP - Calculation and explosion of MRP- Format.rar(238 KB)

Excel VBA Macro - To list and organize files.

Macro vba made in Excel allows you to list any type of file, indicating the folder in which it is found ,its size and its date of creation, you can read more about this macro excel inexcel list files

Excel Macro - List and organize files. (835 KB)

Excel Macro - List and organize files- Format .rar (101 KB)

Excel VBA Macro Example - Automatic Report.

At first, you will see an example of an operating macro in excel, it consists on make the task automatic and give format to an specific report, you can read more about this macro excel inexcel macro example

Excel Macro Example- Automatic Report. (185 KB)

Excel Macro Example - Automatic Report - Format .rar(70 KB)


If you like, share it

Learn How to Update and Delete Using Excel VBA Userform?

It’s useful for making a task easy in business areas like Banking records, 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.

Download Sample Files

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

Learn Excel VBA (Beginner To Advanced)

If you want to be a master at Excel VBA Programming language for Excel 2007, then our Excel VBA macros tutorials will make it easier for you to access it in applications such as Microsoft Office. Come create your own Macros and rule in excel.

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

(Picture 1)

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

(Picture 2) – VBA Userform

Excel Vba Userform Examples Free Download For Mac

Steps to create VBA UserForm are mentioned below:

Step 1: Press Alt + F11.

Step 2: Click Insert, User form. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

Step 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.

Step 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.

Recommended:

Update and Delete Using Excel VBA Userform, Remove Password From Excel Using Excel VBA (Coding) or you can learn many more related tips & tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn How to create a Data Analytic Chart & Graph. Learn More

Once you are done creating a VBA user form 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 row source property, see the image below for your reference.

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

(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 to 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.

[gdlr_core_code style=”dark” ]
Dim SLNo As Integer

If Me.cmbslno.Value = “” Then

MsgBox “SL No Can Not be Blank!!!”, vbExclamation, “SL No”

Exit Sub

End If

SLNo = cmbslno.Value

On Error Resume Next

Me.txtregion.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 2, 0)

Me.txtmarket.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 3, 0)

Me.txtbNum.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 4, 0)

Me.txtcnum.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 5, 0)

Me.txtcity.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 6, 0)

Me.txtState.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 7, 0)

Me.txtpnum.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 8, 0)

Me.txtSdate.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 9, 0)

Me.txtInum.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 10, 0)

Me.txtIdate.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 11, 0)

Me.txtSamount.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 12, 0)

Me.txtsp.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 13, 0)

Me.txtSR.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets(“data”).Range(“A2:N1087”), 14, 0) [/gdlr_core_code]

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.

Recommended:

Update and Delete Using Excel VBA Userform, Remove Password From Excel Using Excel VBA (Coding) or you can learn many more related tips & tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn How to create a Data Analytic Chart & Graph. Learn More

Excel Vba Userform Examples Free Download For Windows 10

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.

Excel

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

Free Excel Vba Code Samples

Your code will look like below:

(Picture 6)

After pasting the above code then run the code and change the value of any field and click on the update button. You will see that changes made in value by you in the Userform for that SL No header are 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 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?

Excel Userform Examples Download

Recommended:

Update and Delete Using Excel VBA Userform, Remove Password From Excel Using Excel VBA (Coding) or you can learn many more related tips & tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn How to create a Data Analytic Chart & Graph. Learn More

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 the “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.

(Picture 7) – VBA user form

Free Excel Macro Examples

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.

Excel Vba Userform Examples Free Download Mp3 Rocket

Learn how to use Flash fill in Excel and related tips & tricks in Excel VBA Course: Click here for more details

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

Excel Vba Userform Examples

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 drop-down button.

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