+ Reply to Thread
Results 1 to 9 of 9

Creating a simple order form

  1. #1
    Registered User
    Join Date
    08-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Creating a simple order form

    Hi
    I'm having problems creating an order form i got it sort of working sometime ago and can not remember how. I need it again now and i can not get it to function at all. I need the complete line on product listing sheet copied to the order form sheet when a quantity and hand is selected. The pricing seems to work fine. Or maybe there is an easier method for what i'm trying to achieve.
    Thanks in advance Matt
    Attached Files Attached Files
    Last edited by matthew1968; 11-06-2010 at 03:24 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Creating a simple order form

    I removed the code you had put into the PRODUCT LISTING sheet module and I also tweaked the formulas in column F.

    Then I put this code into the ORDER module:
    Please Login or Register  to view this content.

    This should do it. The benefit of this approach is that you can add/delete items from the product page and the Order form will always be correct and in the right order.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    08-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Creating a simple order form

    Excellent thank you very much

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Creating a simple order form

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  5. #5
    Registered User
    Join Date
    08-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Creating a simple order form

    Sorry
    I've found a problem it all works great until i protect the worksheet so prices and changes can not be made it then will not transfer to order sheet and errors "runtime 1004" you can not use this command on protected sheet.
    Thanks in advance
    Matt
    Last edited by matthew1968; 11-05-2010 at 07:24 PM.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Creating a simple order form

    Record a macro of you setting all the protections on your sheet the way you want them. Upload that code here along with whether or not you used a password. (password won't show in the recorded code)

    Remember to put your code between code tags:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Creating a simple order form

    This is my code for the protection i require on my sheet. I've also included the protected order form that errors transferring the products to the order sheet. It's password protected and the password is 1742. Which is more about people accidently deleting the formulas than sercurity reasons.
    Code:
    Please Login or Register  to view this content.
    The only cells i need people to edit once the sheet is protected is the handed and the quantity cells. The cells with borders that is.
    Thanks in advance
    Matt
    Attached Files Attached Files

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Creating a simple order form

    Assuming you've already manually set the locked status of the cells you want, there's no need to redo those. What you really need is for the workbook to set a secret flag called "UserInterfaceOnly:=True" on the product listing each time the workbook is opened. That flag allows the sheet to stay protected, but most macro code can run freely on the sheet without unprotecting it first.

    So, I put this in your ThisWorkbook module to do that when the wb is first opened:
    Please Login or Register  to view this content.

    I also corrected the formula in F234. With that, the macro seems to be working fine now.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    11

    Thumbs up Re: Creating a simple order form

    Excellent help thank you JB. The order form works great just what i need.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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