My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Excel Forms

You are here: Home / Excel VBA / Excel Forms
excel userforms
by

Excel forms, or Userforms, can be used to collect information from a user, validate it, then enter that data into your worksheet.

In this post I am going to show you how to create a form in Excel that gathers data, then populates a table for tracking expenses.

This is meant as a simple example and you can adapt the process to many other tasks.

Update

Since this article was written a new, better way to implement a calendar/date picker in user forms has been created.

I recommend that you use this new Multi-language Calendar for User Forms.

Related Articles: I've written other posts on Excel Forms related to this which you might find useful.

Excel Date Picker (Calendar) Using a VBA class to implement a date picker. This is a more robust solution than using an ActiveX control and more easily distributed to other people.

Excel Form Data Validation How to check the data being entered in your form is correct, and communicating with the user about errors

Excel Forms : Insert, Update and Delete Data Using your Excel Form to insert, update and delete records in a table.

Searching for Data With a User Form. Create a userform to search for data on the worksheet.


Creating Excel Forms - A Video

Watch this video to see how I build the form, and the VBA code that makes it work. I cover some things in more detail than I can just writing about it and if you are a visual learner it will be invaluable to you.

Creating Excel Forms - The Steps Involved

You can break down the steps involved in creating a form thus:

  • Designing the form
  • Building the form
  • Writing the VBA code
  • Testing

Designing the form

Before you rush off to create your awesome new Excel forms, you first need to know what it is you want the userform to do. Let's look at our sheet and see what data we will be gathering. The workbook I am using is a modified version of a template provided by Microsoft. Here's a screenshot:

expenses sheet

We need to gather a date, staff and client names, a description, several cash values, and a Yes/No regarding whether or not receipts were supplied. This data is in tabular format and the totals are worked out for us. Read about preparing data in tabular format if you aren't sure how to set this up.

I find that making a sketch on paper helps me to get a rough layout for the way I want the form to look. For small, simple userforms with only a couple of fields, this first step isn't always necessary, but for larger, more complicated forms, it can save you a lot of time.

Building the form

Forms are created through the VBA editor, press ALT+F11 in Excel to open it.

On the left hand side in the Project Explorer we see the workbook (our project) and the sheets in the workbook.

VBA Project Explorer

The userform and its code are stored in userform modules so let's insert one of these. Right click on the project -> Insert -> Userform.

Insert Userform

You get a new Userform module and a blank userform.

New userform with toolbox

We use the Toolbox to insert the controls we use in the form. The term 'controls' refers to both the inputs we can use like text boxes, option buttons, combo boxes etc., and things like labels and frames.

Update

As mentioned above, this old form control is now outdated.

You should use this new Multi-language Calendar for User Forms.

Let's start by inserting a date picker/calendar. The date picker or MonthView control to give it it's proper name, isn't available by default. To be able to use it you must first right click on the Toolbox below the existing controls, and then click Additional controls.

PLEASE NOTE: Unfortunately this control only works with 32 bit versions of Excel.

If you can't find the MonthView control in your Additional Controls you will need to download it. This is a .cab (Cabinet) file which is like a ZIP file and you will need to extract the contents using your archiver, like WinZip or 7Zip. Then follow these instructions to install it. Anybody you send your workbook too will also need to have the same file installed.

  • Copy the extracted files into your System32 folder, often located at C:\Windows\System32. If you're using a 64-bit version of Windows, copy these files into C:\Windows\SysWOW64 folder.
  • Open a Command Prompt, in Win7 and above run the Command Prompt as Administrator.
  • Windows 7: click on your Start button, type cmd, wait for the cmd.exe file to be found, then right click it and choose 'Run as Administrator'
  • Windows 8: Windows Key + X, then click/touch Command Prompt (Admin)
  • Windows 10: click on the Windows button (bottom left of the screen), type cmd, wait for the cmd.exe file to be found, then right click it and choose 'Run as Administrator'
  • In the Command prompt type: regsvr32 c:\windows\system32\mscomct2.ocx
  • For 64-bit Windows type: regsvr32 c:\windows\syswow64\mscomct2.ocx

Add additional controls

Scroll down the list until you see Microsoft MonthView Control 6.0 (SP4) and check the box beside it. Then click OK

Add MonthView control

and you should now have a new Monthview control in your Toolbox

New MonthView control

Click on the Monthview control then click on the Userform to insert one.

Userform with MonthView control

Now we need to add all the other controls which are:

Data Control Type
Staff Name Combo Box
Client Name Combo Box
Description Textbox
Airfare Textbox
Accommodation Textbox
Ground Transport Textbox
Food & Drink Textbox
Miscellaneous Textbox
Receipts Supplied Option Buttons x 2
Enter Expenses Command Button
Clear Command Button
Close Command Button

We also need to enter labels for all of the text boxes and combo boxes, a frame around the option buttons, and we need to give all the controls meaningful names and captions where necessary.

Now would be a great time to watch the video as it will be much clearer watching me do all of these things than trying to follow complicated written instructions. In the video I show you how to use tables to populate the combo box lists, and how to use the Format tools to align controls quickly and get your form layout looking great. It's ok, I'll grab a cup of tea and wait here while you watch it.

What you should end up with is something like this.

Completed userform

Writing the VBA code

We need VBA to make this userform do anything and we only need a few subs to do this. Four of these consist of only a few lines of code (including the sub declarations), who said Userforms were hard to learn?

The code we need to write will:

  • Initialize the form
  • Open the form
  • Close the form
  • Clear the form
  • Enter data into our sheet

Let's start with opening the form, we'll look at initializing it later.

VBA to open the form

We'll be calling this code from the worksheet so we will put it into a code module. The rest of the code is part of the form so will be in the Userform module that we have already created.

To insert a code module, right click the project (your workbook) in the VBA editor and click Insert -> Module

Insert module

The new module should be selected by default, double click it if you aren't sure, and the right hand side (the Code window) will be empty.

This is the code to display the form:


Sub ShowExpensesForm()

    ExpensesForm.Show
    
End Sub

Copy and paste this VBA into the Code window for Module1 like so:

VBA to show the form

All we need to do is use the .Show method to display the form, with the form being named ExpensesForm.

To test this, go back to Excel (ALT+F11 or just switch to Excel). Insert a shape on the sheet, add some useful text like Enter Expenses and change the color/format to your liking. Mine looks like this:

Enter expenses button

Right click the button and choose Assign macro

Assign macro to button

NOTE: As well as shapes, you can assign macros to your QAT, the Ribbon or use a keyboard shortcut.

Our macro is called ShowExpensesForm so select that and click OK.

Choose macro for button

Click on the sheet to deselect the button, the click the button again and your macro should now run and open the form.

VBA to close the form

This is pretty simple too. This code goes in the Userform module so let's go back to the VBA editor (ALT+F11) and double click the Userform module, which in my case is called ExpensesForm.

This displays the form. To add code to any control, double click on the control. So, double click the Close button and you should see this

Close form sub without code

The VBA editor has entered the declaration for the _Click() action on the CloseForm button. This code will be executed when someone clicks the Close button. We just need to add one line of code.


    Private Sub CloseForm_Click()

        Unload ExpensesForm

    End Sub

Now when you open the form and click the Close button, the form disappears.

VBA to clear the form

Clearing the form requires us to reset all the input controls to either empty values for text boxes, FALSE for Option Buttons, and -1 for Combo Boxes. This applies just to our form, with other controls there will be other code required. For Combo Boxes the -1 value indicates that nothing in the list is selected.

I'm going to write a Sub to do this job for me and I will call this sub when I want to clear the form and when I want to initialize it. I actually don't need to call it when I initialize the form as there won't be anything entered/selected when the form is first opened. When we close the form we are Unloading it too, as opposed to just hiding it, so the form is re-initialized when we next open it.

But, if you wanted to initialize your form with certain default values you could do it in a similar way to this, by calling an initialization sub. So I will include the code and show how to call it here so that it may help someone understand the process.

Here's the sub to clear the form


Private Sub ResetForm()

    Dim Ctrl As MSForms.Control

    For Each Ctrl In ExpensesForm.Controls
        
        Select Case TypeName(Ctrl)
        
            Case "TextBox"
                Ctrl.Text = ""
                
            Case "OptionButton"
                Ctrl.Value = False
                
            Case "ComboBox"
                Ctrl.ListIndex = -1
                
        End Select
        
    Next Ctrl
    
    ChosenDate.Value = Date

End Sub

To make use of this, double click the Clear button on the form designer in the VBA editor and you will see this

Clear form sub without code

Copy/paste the code above into the top of the module, and enter the name of the sub into the ClearForm_Click() module like so:

Clear form VBA code

VBA to initialize the form

This code is executed anytime the form is first displayed. In this scenario, this is every time we open it as when we close the from we are Unloading it ( Unload ExpensesForm). This is distinct from simply hiding the form ( ExpensesForm.Hide). Hiding it just doesn't show it on screen, and any selections or entered data is preserved until the next time the form is displayed.

So, we actually don't need to do this here, but I include it here as an example in case you want to know how to call some code when initializing your form.

Double click one of the buttons to access the form code, or you can right click the userform module and choose View Code

View userform code

and copy/paste this code 

Private Sub UserForm_Initialize()

    ResetForm
    
End Sub

VBA to enter data on the sheet

Finally, the important bit 🙂 . As we are entering data into a table, we can use the ListRows.Add method to add a new row to the bottom of our table and make things easy for us. Tables are great and if you don't use them you should start now.

Once we've added our new row, we just enter the values of each of our controls (the text boxes, combo boxes and option buttons) into the appropriate cell in the row.

Double click the Enter Expenses button in the form designer, and copy/paste this code into the EnterExpenses_Click() sub


    Dim LastRow As Range
    Dim ExpenseTable As ListObject

    'Add row to bottom of Expenses table
    ActiveSheet.ListObjects("Expenses").ListRows.Add
    
    'Enter data from form into our new row
    Set ExpenseTable = ActiveSheet.ListObjects("Expenses")
    Set LastRow = ExpenseTable.ListRows(ExpenseTable.ListRows.Count).Range
    
    With LastRow
    
        .Cells(1, 1) = ChosenDate.Value
        .Cells(1, 2) = StaffName.Value
        .Cells(1, 3) = ClientName.Value
        .Cells(1, 4) = Description.Value
        .Cells(1, 5) = Airfare.Value
        .Cells(1, 6) = Accommodation.Value
        .Cells(1, 7) = GroundTransport.Value
        .Cells(1, 8) = FoodDrink.Value
        .Cells(1, 9) = Misc.Value
        
        If ReceiptsYes.Value = True Then
        
            .Cells(1, 10) = "Yes"
            
        Else
        
            .Cells(1, 10) = "No"
            
        End If
        
    End With

Testing

All we need to do now is test the form. We've tested each of the buttons as we've been going along but you may decide that you want to alter the form layout, colors, labels etc.

Download the workbook

Enter your email address below to download an Excel workbook containing the form and related code.

By submitting your email address you agree that we can email you our Excel newsletter.

Sharing is Caring

If you liked this or know someone who would like to learn how to create Excel forms, please click the buttons below to share it with your friends and colleagues.

email icon twitter icon facebook icon linkedin icon
excel userforms

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Excel VBA Posts

Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA
Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image
List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists
Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar
Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA
Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba
Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject
Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List
Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points
Calculate end of period dates in Excel
Calculating Excel End of Period Dates can be tricky if you don't use standard month ends. Here is a formula and UDF that solves the problem.

More Userforms Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Hide, Delete, Sort Sheets with VBA in Excel
Using a form and some VBA, you can quickly hide, unhide, delete and sort sheets in your Excel workbooks.
add data to combo box drop down list in excel form
If the values in your Excel Form's drop down list aren't sufficient, allow the user to add their own, and save the new value in the list to use again.
Excel forms, insert, update, delete data
Insert, update and delete data in an Excel table with a user form.
excel form data validation
Thorough and effective Excel form data validation techniques. Clearly communicate errors to your users without annoying them.
excel date picker
An Excel date picker (calendar) for all versions of Excel, both 32 and 64 bit. An easy to use VBA class, with examples to download in a workbook
Excel Form Controls
Add interactivity to your spreadsheets with user friendly form controls like option buttons, combo boxes, check boxes and scrollbars.

More Excel VBA Posts

macro to center across selection
Use this Excel Center Across Selection shortcut macro generated by ChatGPT instead of merge and center.
Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.


Category: Tag: ,

Reader Interactions

Comments

  1. Kha

    Hello!

    It’s been a few years since you’ve posted this, but I’m trying to work this form to help streamline my company’s spreadsheets. I was wondering how you would serialize each entry on the worksheet onto the excel table. For example Build#: 1,2,3,etc. Would it be a loop function that checks if there is already a number? A function that grabs the maximum for a column and adds +1 to the value?

    Basically, I want the build number to be automated sequentially rather than have the user look it up for the last entry and manually enter it, since the way I’ve set up the data from would autopopulate about 8 entries at a time.

    Thank you for the resources, they’ve really helped

    Reply

    • Catalin Bombea

      Hi,
      Easiest way in VBA is to use the excel sheet MAX function.
      NextID=Application.Worksheetfunction.Max(Table1.ListColumns(“ID”).Range)+1

      Reply

  2. Greg

    Hello Phil,

    This was a fantastic tutorial on how to build a Form within Excel. I was able to follow the tutorial exactly – however, my application differs. I have a table of static data that I need to reference in the Form and fill out some additional detail within that form to be reflected in the row of data. I am not sure how I would bring in the static data to my form and update the row data as needed. Is this possible? If so, would it be more helpful if I supplied files or additional details?

    Reply

  4. jim

    Hi Phil,

    as I’ve said elsewhere, this is a most impressive piece of work which I’ve shamelessly adapted for our own use (ok, if anyone asked, I freely admitted that’s it’s not all my own work and MOTH is credited in the modules)

    it was demonstrated, tested and really liked; until it was used for real:

    you can’t copy text from another spreadsheet to paste in whilst the form is open (emails or other docs, no problem)
    which was kinda one of the points of it in the first place (providing a standard, compact way of feeding data from a variety of different sources into a very wide table)

    looks like we’ll have to type entries into the form manually or paste spreadsheet items into rows of the table directly

    so close

    jim
    .E.:
    we kinda got round this by previewing the spreadsheet in an email rather than opening it directly

    Reply

  5. Philip Lovell

    Really great tutorial.
    Can you help please, I wish to use this user from where the employee takes out a pre-numbered document that is already on the spreadsheet so when they use your user form they enter the number in your user form and it takes them / finds that number on the spreadsheet and the data they enter is placed beside that number.

    Reply

    • Philip Treacy

      Thanks Philip.

      We’d really need to see your workbook and data in order to be able to come up with a proper answer for you.

      Can you please open a topic on the forum and attach the workbook.

      Regards

      Phil

      Reply

  6. Robert Hyde

    This is a great tutorial; clear and detailed.

    Adapting this for my own needs would fulfil my goal perfectly. My only problem is the Date Picker. I guess using a 64 bit OS and Excel 2016 64 bit is my current nightmare.

    Anyway, I will see if anybody can help me out in forum.

    I am new to this forum, but I think I will become an avid viewer…

    Reply

    • Catalin Bombea

      I’s not an error in Date Picker, it’s how you use the objects. A Frame has a .Caption property, it does not have a .Value property.
      Anyway, using Frame1.caption will not return a date from the calendar.
      Use instead:
      With lastrow
      .Cells(1, 1) = Calendar1.Value
      .Cells(1, 2) = BulkCode.Value
      End With

      Reply

  7. Zoe Davy

    Hi,
    Love this tutorial. It’s exactly what I’m looking for, but unfortunately because I require the spreadsheet to be shared I’m unable to apply all Sub coding (where referencing adding data to tables).

    Is there a way to adapt this to reference sending the form data to cell ranges not set to tables?

    Zoe

    Reply

    • Catalin Bombea

      Hi Zoe,
      No idea why you’re saying that the code referring to tables cannot be used in a file that will be shared. The code will work exactly the same, no matter if you refer to a table cell or to a worksheet cell.
      Here is what you can do:

      Set NextRow = ActiveSheet.Cells.Find("*",ActiveSheet.cells(1),,,xlByRows, xlPrevious).Row+1
      'add data to next row
      ActiveSheet.Cells(NextRow, 1) = ChosenDate.Value
      ActiveSheet.Cells(NextRow, 2) = StaffName.Value
      ActiveSheet.Cells(NextRow, 3) = ClientName.Value
      ActiveSheet.Cells(NextRow, 4) = Description.Value
      ActiveSheet.Cells(NextRow, 5) = Airfare.Value
      ActiveSheet.Cells(NextRow, 6) = Accommodation.Value
      ActiveSheet.Cells(NextRow, 7) = GroundTransport.Value
      ActiveSheet.Cells(NextRow, 8) = FoodDrink.Value
      ActiveSheet.Cells(NextRow, 9) = Misc.Value
      If ReceiptsYes.Value = True Then
      ActiveSheet.Cells(NextRow, 10) = "Yes"
      Else
      ActiveSheet.Cells(NextRow, 10) = "No"
      End If

      Reply

  8. J Slogoff

    Hi. I have been searching for this answer for a long time. It looks great, however, I get an error message that “Could not load an object b/c it is not available on this machine.”

    How do I determine what object is missing?

    Reply

    • Catalin Bombea

      Hi,
      Press Alt+F11 to open VB Editor. In Editor menu, go to Tools-References, check if one of them has MISSING prefix.
      You should also try to open the form in safe mode, without any add-in activated (keep Ctrl key pressed when opening the file, make sure there is no open excel file before opening in safe mode.)
      If it works, one of the add-ins is causing the problem.

      Reply

  9. Francis Gahizi

    Hello! I am trying to create a form to capture my cattle farm data and produce reports, emulating the travel expense report, buh, so so hard.. Can I send the table for you look at it and help please!

    Thank you.

    Francis

    Reply

    • Mynda Treacy

      Hi Francis,

      Sure. Please post your question on our Excel forum where you can upload an Excel file with your sample data and desired result. We can then help you with the solution.

      Mynda

      Reply

  10. Jannette Trevino

    Thank You, Thank You, Thank You.
    I finally made it. I spend two days trying to figure it out, is because I’m new in excel. Coding was the difficult part, changing names and make it work… I almost died of a heart attack LOL. Important, It WORKS. Thank you again. Did I thank you enough.

    Reply

  11. An Phan

    It is a great lesson. Thanks.

    One question : when there is hundreds of rows are entered into the form, if we wanna double check any mistyping from back up paper into form, how should we do?
    maybe a scan search based on date or client name etc.

    Reply

    • Catalin Bombea

      Hi An,
      There is no double check based on paper, other than manual evaluation. There can be valid client names that have only a single character different than the other, like Maria and Marian for example. A fuzzy lookup needs a database of valid entries, but the result is unreliable, because will return partial matches.

      Reply

  12. SUNIL KUMAR YADAV

    Please add the “search button ” also in the user form. and give the process to create interactive user form in excel with soft copy example.

    Reply

    • Catalin Bombea

      Hi Sunil,
      Where should the “Search Button” search? Not sure what you mean, you have to provide more details. This article does describe the process of creating interactive user forms, there is also a downloadable file at the end of the article.
      Cheers,
      Catalin

      Reply

    • Catalin Bombea

      Hi Kara,
      Of course it’s possible, here is the place where you can change the destination:
      ‘Add row to bottom of Expenses table
      ActiveSheet.ListObjects(“Expenses”).ListRows.Add

      ‘Enter data from form into our new row
      Set ExpenseTable = ActiveSheet.ListObjects(“Expenses”)

      You can change it to:

      ‘Enter data from form into our new row
      Set ExpenseTable = ThisWorkbook.Worksheets(“Sheet1”).ListObjects(“Expenses”)
      ‘Add row to bottom of Expenses table
      ExpenseTable.ListRows.Add

      You should create the table in the sheet you want before running the code, it is expecting to find a table there, it will not create it from code.

      Reply

      • Anushree

        Hi Catalin,

        The code is working fine if the button to run the form is placed within the active sheet. However I want to place the button in Sheet1 and the form to add data into Sheet3. I tried the modification suggested by you here but it’s giving me “Compile Error: Variable not defined” and the word “Expenses” is highlighted in this line ThisWorkbook.Worksheets(“Sheet3”).ListObjects(“Expenses”). I guess the table named Expenses is not getting recognized when placed in a separate sheet. Can you please advice? Thanks.

        Reply

        • Philip Treacy

          Hi Anushree,

          It’s really difficult to debug code without having the code in front of you.

          Please start a topic on the forum and attach your workbook so we can run through your code ourselves.

          Regards

          Phil

          Reply

  15. melissa2910

    Can someone please tell me how to write code so that in description box I can fill in the entire box (instead of it just being on one line).
    Also once the sheet is entered-mine wont close-how do I get it to close after data is entered?
    Thanks for your help.

    Reply

    • Philip Treacy

      Hi Melissa,

      In the form designer, click on the Description text box, and you should see the properties for this in the Properties Window. If you don’t, press F4.

      Look for the MultiLine property and set that to True.

      Not sure what you mean by ‘when the sheet is entered’? Do you mean once a line/record is added to the sheet, the form stays visible? If you want it to disappear after adding a record, then modify the EnterExpenses_Click sub. Add this line before the End Sub:

      Unload ExpensesForm

      Regards

      Phil

      Reply

    • Catalin Bombea

      It seems that you do not have a table named Expenses in the active sheet.
      Use ActiveSheet.ListObjects(1).ListRows.Add, if you want to add rows to the first table from the active sheet, using a table index instead of the name.
      Catalin

      Reply

      • Rakesh

        Hi Catalin,
        Thanks for your reply. I am trying the same example show above. The name of sheet is “Expensestb” also in the same sheet I have named the range as “Expenses”.which has the expenses details.
        Again I am posting the full vba code as below. Is anything incorrect in this code or still the table name is missing.Please advice

        Private Sub cmd_ee_Click()

        Dim LastRow As Range
        Dim Expensetable As ListObject

        ‘Add row to bottom of Expenses table
        ActiveSheet.ListObjects(“Expenses”).ListRows.Add AlwaysInsert:=True

        ‘Enter data from form into our new row
        Set Expensetable = ActiveSheet.ListObjects(“Expenses”)
        Set LastRow = Expensetable.ListRows(Expensetable.ListRows.Count).Range

        With LastRow

        .Cells(1, 1) = ChosenDate.Value
        .Cells(1, 2) = cmb_staffname.Value
        .Cells(1, 3) = cmb_clientname.Value
        .Cells(1, 4) = txt_des.Value
        .Cells(1, 5) = txt_airfare.Value
        .Cells(1, 6) = txt_accom.Value
        .Cells(1, 7) = txt_grotr.Value
        .Cells(1, 8) = txt_fd.Value
        .Cells(1, 9) = txt_misc.Value

        If opt_y.Value = True Then

        .Cells(1, 10) = “Yes”

        Else

        Cells(1, 10) = “No”

        End If
        End With

        End Sub

        Reply

        • Catalin Bombea

          A defined table is an object. Did you created that object by selecting the range, and pressing Ctrl+T (or from Insert tab, press on Table), choose the table name in the dialog box that will open.
          You are saying that you named the range.
          Maybe it’s better if you upload your sample file on our forum (create a new topic) so we can see what you have there.
          Catalin

          Reply

          • Rakesh

            Hi Catalin,

            Thank you so much it’s working now. The error was due to table.

            Please could you suggest the vba code. When the Enter Expenses button in clicked it should not allow any blank record to update from the textbox,combobox and option button.

            Thanks
            Rakesh

          • Catalin Bombea

            Hi Rakesh,
            All you have to do is to wrap the code into a conditional test, checking the length of the text before writing the values, like:
            If Len(Textbox1.Text)>0 Then
            ‘ rest of code
            End If

    • Philip Treacy

      Thanks Rathanak,

      Do you want to use the form to save the workbook? or just automatically save the workbook before it is closed?

      If you want to use the form then add a command button to the form and use code like this:

      Private Sub CommandButton1_Click()

ActiveWorkbook.Save

End Sub

      If you want to automatically save the workbook before it is closed the you will need to use an event saved in the ThisWorkbook module

      
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ActiveWorkbook.Save

End Sub

      Let me know if you can’t get it to work.

      Regards

      Phil

      Reply

  18. Max

    Good Afternoon Phillip,

    I have a share trading spreadsheet and I was looking at using a data form to insert details of my next trade to my spreadsheet. I don’t know if it’s possible to do what I want, I have spent some time looking around the web to no avail and took a short cut to ask you if it is possible.

    I have about 60 columns, some don’t need filling in as they have formulas and automatically fill in when other data is put in. I would need to input data in about 30 fields, this I was hoping to do using the data form. Some of these fields are in between others that don’t need filling in.

    The spreadsheet already have the formulas carried down the spreadsheet by many many rows.

    Is it possible to use the data form in this way?
    If so, using the data form and having it add a new row, will it add empty cells with no formulas in where there was formulas?

    Hope this make sense and appreciate any help you might be able to supply.

    Cheers … Max

    Reply

    • Philip Treacy

      Hi Max,

      If you can please open a Helpdesk ticket and attach your workbook, it’ll make it easier to understand what you are trying to do, and we can then give you an idea of whether it’s possible or not.

      Cheers

      Phil

      PS No worries about the double comments 🙂

      Reply

  19. Charles Hall

    I want to add 2 more group boxes with yes no in them but I am lost on how to add the code to the existing code. can you explain or do you have another lesson that teaches you how to do this?

    Reply

    • Philip Treacy

      Hi Charles,

      Adding a couple more yes/no buttons wouldn’t be too hard, but what exactly do you want them to do? Do you want to check if they have been selected? Can the form be submitted if one of them isn’t selected?

      If you raise a Helpdesk ticket and I’ll see what I can do to help.

      Cheers

      Phil

      Reply

      • Charles Hall

        I want to check if selected. In you course you have one group box with yes/no, I want to add 2 more group boxes with Yes/No, tried to add to your existing code but cannot figure out how to make it work.

        Reply

        • Philip Treacy

          Hi Charles,

          Please raise a Helpdesk ticket and send me what you have done in your workbook and I’ll see what i can do to help.

          Regards

          Phil

          Reply

  21. Julian

    It would be perfect if you could include “update” and “delete” functions to manipulate the data entries furthermore. I do expect for it. Thanks.

    Reply

    • Philip Treacy

      Hi Julian,

      We could make modifications for you but we would need to understand exactly how you want these carried out. If you want to raise a Helpdesk ticket we can discuss it further.

      regards

      Phil

      Reply

      • Julian

        Hi Philip,

        I Just used your “template” to keep a list of my collected CDs privately. Therefore, I’ve changed each control names and caption as well as replaced a calendar control compatible to Excel 97. I have been learning something about Excel by my own partly from on-line training, like yours, for many years. Now that you suggest me to raise a Helpdesk ticket for in-depth learning, could you please explain more details about it. I think as long as I’m afford to buy it, even I’ve had retired for more than 6 years without income, I still like to keep learning what I’m interesting in.

        Regards,

        Julian

        Reply

        • Philip Treacy

          Hi Julian,

          It’s impossible to say what work is involved to add UPDATE and DELETE functionality to your code. there are several reasons for this. Because it is code you have written/modified we first of all need to familiarise ourselves with it and understand how to incorporate what you want into the existing code without breaking it. Or we may need to alter existing code to add the new functionality.

          We’d also need a very clear explanation of how you want the DELETE and UPDATE to work – our ideas for this will probably be different to yours. For example, when we DELETE a record, are there special record identifiers that need to be updated (a unique record key)? Do we need to write a new routine to go through each record in turn asking if you want it deleted (this could take a long time) or would it be better to just delete the record from the sheet?

          So all I meant was that we can do all of this for you, but we need to see your code and need clear instructions on what you want. The first step in this is to create a Helpdesk ticket and supply the workbook and instructions.

          It’s great that you are keeping busy after retirement. I know that when I retire I’ll certainly want to keep busy too and coding is an excellent way to keep the brain active.

          Regards

          Phil

          Reply

          • Julian

            Hi Phil,

            Be honest, I don’t know how to write the code regarding those two functionalities. However, when it comes to database mangement it involves “ADD”, “CHANGE/UPDTE”, “DELETE” for records manuplation and record ID is the key to retrieve the specific record. That’s what I learned from MS Acess. That’s it. Now that I’ve learned how to ADD records via a VBA form and related controls from you. It’s a great breakthrough for me. I do appreicate it.

            Regards,

            Julian

          • Philip Treacy

            Hi Julian,

            I have made a note of this in my ‘VBA Blog Topics’ list an will look at covering this in a future blog post.

            Regards

            Phil

          • Aaron S

            I had the same request like many others with the request of updating/deleting records using this form. I took what you provided us and made some changes to accomplish this. I’m sure it’s not the best way but it serves my purpose so hopefully it will help others. You can download a copy at the link below. Enjoy!

            https://copy.com/itW12FEz3N3lMWtU

  22. Bob

    Well done Phil. And your homage to the Walking Dead wasn’t lost on viewers. 🙂

    Excel forms, and the necessary VBA code, are something I’ve been interested in learning to improve my dashboards. Having a data entry form users can call up from the dashboard, rather than force users to go into the workbook to find a data entry sheet, would improve data entry integrity it seems to me.

    Thanks for a very informative tutorial.

    Reply

    • Philip Treacy

      Thanks Bob. I’ve got another post on forms coming soon. As you say they can be useful to make sure the data going into your sheets is valid.

      You’re the first person to mention the WD 🙂 Can’t wait for the next season. I’m currently working my way through S5 of GoT

      Cheers

      Phil

      Reply

  23. Vered Samuel

    Wow – excellent tutorial!!
    When I tried creating my own form, I keep getting a “Run time error 424: Object required”
    with applying the macro to the “Enter Expenses” button. I seem to be missing a reference of some sort, but I’ve checked and its identical to your form. Any idea what I am missing?? Thanks in advance

    Reply

    • Philip Treacy

      Thanks Vered.

      If you open a Helpdesk ticket and send me your workbook I’ll take a look.

      Regards

      Phil

      Reply

  24. Stefan van Gaal

    Hi Philip,
    Very clear and understandable instruction, which makes me want to give a go at userforms myself. One question though: My toolbox does not contain the Microsoft MonthView Control 6.0 (SP4). under Additional Controls. Any advice how to add this to my version of Excel?

    Reply

    • Philip Treacy

      Thanks Stefan. If you are missing the control you can download it from Microsoft and install it. I’ve added instructions towards the top of the post. You need 32bit Excel but there is an alternative for 64 bit.

      Regards

      Phil

      Reply

  25. Lionel B. Dyck

    MonthView looks outstanding. If I add that to a workbook and share that workbook with others will they need to get into developers view and add it or does that only need to be done by the developer?

    Reply

    • Philip Treacy

      Hi Lionel,

      The recipient must have the MonthView control installed too. They don’t need to go into the VBA editor and do this, as long as the control (which is actually a file on the computer) is installed and registered in Windows, it should work fine.

      See the section I’ve added to the top of the post regarding downloading and installing the MonthView control.

      Cheers

      Phil

      Reply

  26. Wayne F. Eichler

    Excellent presentation by Phil. The presentation was very clear and complete. He used a real world example in how to set everything up. It adds another tool to use and enhance your skills and add value within organizations.

    Reply

  27. Paul S.

    Thanks, Phil. Great post! I really appreciate tips like this. It opens up a whole new world to some of us.

    However, when I try your workbook, I get the message “Object library invalid or contains references to object definitions that could not be found”. When I try my own workbook, on the “ExpenseForm.Show”, it highlights the “Show” and I get the error message – “Compile error: Method or data member not found”.

    I assume that in the VBA modules, under Tools, I need to add some references. Do you have any idea what references I may need to add to get this to work (or perhaps there is something else I need to do)?

    Thanks!

    Reply

  28. Ira

    This was great!!! I never knew this was something you could do with Excel. The video made the process seem so easy. Thanks.

    Reply

    • Philip Treacy

      You’re welcome Ira. Great to know we’ve opened up a new area for you to explore in Excel.

      Phil

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *