Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Data Entry Form (2002)

    I have been asked to create a data entry form for an Excel list - a form with a specific layout and some tools for repetitive entries, the kind of form I can make in Access that includes tab order, combo boxes, etc. I find hints that it can be done, but no map for the procedure to do it. (And the customer wants to keep using Excel, so that's what I need to try to do.). Can someone steer me in the right direction?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    What you need is a userform, which can be created in the Visual Basic Editor. Press Alt-F11 to get there, In the project explorer situated on the left of your screen, select the workbook that has to have a userform and choose Insert, userform.

    You will need to use VBA code to control the forms behaviour.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    I'm trying to accomplish a similar objective only I've created the form on the first worksheet ("Form") with a number of controls. Now I'm trying to sort out how to write this record to the second sheet ("Data") on the next available row. I'm trying to use a control button with a click event to transfer the contents of all the controls on the Form to the Data table. Is this the way forward? How do Linked Cells in the Properties box work with this--should I leave them blank or is this the way to control the writing of the record? I feel I'm still in the wilderness a bit--help!

    Thanks

    S.O.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    You could use the linked cell property of each control and line the cells up so they form a single row with the same layout as the destination table.

    If you name the row of cells containing the links to the controls (insert, name, define) "ControlSource"
    this single line of code will copy the current data from the Form to the data sheet (assuming this sheet contains a header in row 1):

    With Thisworkbook
    .Worksheets("Form").Range("ControlSource").Copy Destination:=.Worksheets("Data").Cells(.Worksheets ("Data").Usedrange.rows.count+1,1)
    End With
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    Jan:

    Thanks for your reply. That looks like exactly the sort of economical solution I'm looking for.

    I'm going to try it out now. Thank goodness you replied--things were getting quite hopeless!

    Thanks again!

    S.O.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    There is always (Excel-) hope as long as this board is live <img src=/S/smile.gif border=0 alt=smile width=15 height=15>!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    Jan:

    I'm back. Okay, I'm so sorry to bother you but it looks like I'm going to need some more specific advice.

    I added a UserForm Control Command Button to the worksheet Form. In Design Mode, I double-clicked on the button to bring up the editor and added your code so that it looks like this:

    Private Sub CommandButton1_Click()
    With ThisWorkbook
    .Worksheets("Temp").Range("ControlSource").Copy Destination:=.Worksheets("Data").Cells(.Worksheets ("Data").UsedRange.Rows.Count + 1, 1)
    End With
    End Sub

    Oh, I also created a Temp worksheet which I planned to hide later so that the Form controls link to the appropriate cells in the Range (Temp!ControlSource). I didn't think this would disrupt the execution of your code. For whatever reason, it doesn't run. This is the error:

    Run-time error "1004"
    Application-defined or object-defined error

    Even when I changed the linked cells and range to the Form worksheet, it still threw up the error. Any ideas?

    I'm also wondering if it's possible to add code to the same macro that will reset the form after the values are written to Data worksheet. I thought this would save me adding an extra button.

    Thanks for your help--sorry to pester you but you've got a live one here!

    S.O.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    My gues is that you need to change the code to this:

    .Worksheets("Temp").Range("Temp!ControlSource").Co py Destination:=.Worksheets("Data").Cells(.Worksheets ("Data").UsedRange.Rows.Count + 1, 1)

    in other words: be explicit as to what name it needs to look for.

    Of course this code can also live in the sub that resets the form, as long as it starts off with the copying <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    Thanks, Jan. Will give it a try.

    S.O.

  10. #10
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    I think it might have something to do with the ".Copy Destination:=" bit because I keep getting an error message saying:

    Compile error:
    Expected: expression

    Does that make any sense?

    S.O.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry Form (2002)

    There should be one single line of code between the lines
    With ....
    and
    End with

    so please remove all "enters" so it all fits on a single line.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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