+ Reply to Thread
Results 1 to 10 of 10

How to make a Command button run the "FORM" command to fill/populate a table?

  1. #1
    Registered User
    Join Date
    11-21-2012
    Location
    Warwickshire, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    How to make a Command button run the "FORM" command to fill/populate a table?

    Dear All,

    I have little to no experience using excel.

    I have a spreadsheet which has been made to look like a form with around 16 columns which act as headers/fields, the rows beneath will be data entries to be filled in by users. There will be multiple entries, hence multiple rows of data.

    Selecting my column headers and clicking the FORM command excel brings up a window where I can add a value to the selected field, like a form!

    How can I, by using a command button, simply click this button and begin filling out my form?? I want the user to click a single button and the window apper just as if hed selected the table headers and used the FORM command.

    I have tried doing a bit of reading and experimenting and I found myself trapped in VBA with allsort of errors and other messages popping up. thought Id ask for some advise as most post i can find differ slightly from my issue.

    Any help would be much appreciated as this piece of work is kind of urgent. Thanks in advance.
    Last edited by jrobin7; 11-21-2012 at 12:27 PM.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to make a Command button run the "FORM" command to fill/populate a table?

    if your headers start within A1:B2 then you can use
    Please Login or Register  to view this content.
    if the headers are somewhere else you have to name the data range "database" and ensure it is at least two rows
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    11-21-2012
    Location
    Warwickshire, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to make a Command button run the "FORM" command to fill/populate a table?

    I create my button using "activeX command button"..... place it where I want it.... then this appears...

    Private Sub CommandButton1_Click()

    End Sub


    Where do I input your answer of:

    Sub ShowForm()
    Range("A1").Select
    Application.DisplayAlerts = False
    ActiveSheet.ShowDataForm
    Application.DisplayAlerts = True
    End Sub


    Also the cells containg the data I wish to be used as form fields are on row 7, column A to P. Im really struggling with this.. apologies

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to make a Command button run the "FORM" command to fill/populate a table?

    select A7:P8 (or more rows if you have data already) and in the drop down box to the left of the formula bar type:
    database
    and press enter. then alter your button code to
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-21-2012
    Location
    Warwickshire, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to make a Command button run the "FORM" command to fill/populate a table?

    I believe I have done the above correctly naming the data "Database" however when trying to run I get an error 1004 at line ActiveSheet.ShowDataForm which I understand to be excel cannot find the data... in the lines above we have specified no data range location so excel uses data range named "database" as default... however it doesnt seem to be able to find it... any sugestions, after hours of reading your two comments have made hurdles of sense to me.

    I have got my info from http://support.microsoft.com/kb/110462

    Kind regards

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to make a Command button run the "FORM" command to fill/populate a table?

    can you post the workbook with the code in place? you can remove all data and any other worksheets

  7. #7
    Registered User
    Join Date
    11-21-2012
    Location
    Warwickshire, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to make a Command button run the "FORM" command to fill/populate a table?

    I think its attached. theres nothing important on it. manny thanks for this

    Emergency Process_A.xls

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to make a Command button run the "FORM" command to fill/populate a table?

    your named range is only one row-I did say it had to be at least two ;-)

  9. #9
    Registered User
    Join Date
    11-21-2012
    Location
    Warwickshire, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: How to make a Command button run the "FORM" command to fill/populate a table?

    You did indeed..... I have given up trying to do it that way now and have moved the column headers to row A..... this has worked first time round and I recon Im gonna go with it. Thanks again for your help!

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to make a Command button run the "FORM" command to fill/populate a table?

    ok though if you amended the name to include row 8 it would work where it was before

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1