none
What are the advantages/disadvantages of using the Form Command over Creating a User Form in VBA? RRS feed

  • Question

  • I’m in the early stages of creating a spreadsheet that will require data entry into the worksheet. The worksheet could get to 1000s of rows, depending on the particular user.

    When I refer to the ‘Form Command’ I’m referring to the option of adding a Quick Access Tool bar button by selecting ‘more commands’, ‘all commands’, ‘Form…’

    My questions/concerns would be relating to ‘should I develop this spreadsheet with the Form Command or a User Form in VBA?

    Concerns/Questions:

    Deployment to other users

    Protecting worksheet cells so the user can only add records using the form command or the user form in vba. In other words, don’t allow users to edit cells that are not within the table structure of the sheet or add rows if they’re not using the form

    Data validation when entering data…

    Keith


    Keith Aul

    Wednesday, April 15, 2015 6:25 PM

Answers

  • Hi,

    Both variants have pro's and cons. If you plan to use VBA you have to consider the deployment settings on other p.c.'s where you have to allow macro's where as a regular form doesn't have the need for that.

    That said the userform gives you much more control because the form command is not saved. it is simply a temporary form which you have to setup every time. It's perfect for data entry. If you are looking for data validation the userform is the one to go for. You also have the possibility to work on placements of the data entry.

    So if you are looking for speed and simplicity then go for the form command. If it is handling control you are after I'd go for a userform.

    Just my 2 cts

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    Wednesday, April 15, 2015 10:08 PM
  • Re:  using a data form

    John Walkenbach has a free "Enhanced Data Form" download at...
    http://spreadsheetpage.com/index.php/dataform/home

    Note: just because a program functions on xl2007/2010 does not mean it will work on xl2013.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, April 17, 2015 9:40 PM

All replies

  • Hi,

    Both variants have pro's and cons. If you plan to use VBA you have to consider the deployment settings on other p.c.'s where you have to allow macro's where as a regular form doesn't have the need for that.

    That said the userform gives you much more control because the form command is not saved. it is simply a temporary form which you have to setup every time. It's perfect for data entry. If you are looking for data validation the userform is the one to go for. You also have the possibility to work on placements of the data entry.

    So if you are looking for speed and simplicity then go for the form command. If it is handling control you are after I'd go for a userform.

    Just my 2 cts

    Maurice


    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer. Thank You

    Wednesday, April 15, 2015 10:08 PM
  • Thank you for your response. This does shed a little bit of light on this subject and will help me decide on which way to go.

    Keith


    Keith Aul

    Friday, April 17, 2015 9:08 PM
  • Re:  using a data form

    John Walkenbach has a free "Enhanced Data Form" download at...
    http://spreadsheetpage.com/index.php/dataform/home

    Note: just because a program functions on xl2007/2010 does not mean it will work on xl2013.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, April 17, 2015 9:40 PM