+ Reply to Thread
Results 1 to 35 of 35

Please Help - Userform For Item Additions

  1. #1
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Please Help - Userform For Item Additions

    Greetings Everyone. This is my cry for help as I am stumped with VBA. I should be embarrassed to admit but its true. Okay, I have attached both files that I created which the sales force uses in my company to add "prospective" items to our Oracle database. The "New Item Upload Form" has multiple tabs but I left only one available as a user-form for the sales team. Once they have completed their item request, it gets emailed to another department for review at which time that department runs the 'Macro - New Item Upload' vba to basically pull a hidden tab from the original and preps it with the appropriate format for Oracle interaction.

    What I am trying to do is first of all, combine these two files.

    Second, take the first tab and create a user-form to be completed.

    If possible, create some buttons on that form to send to the final department via out. Their test email is: [email protected]

    Once the email is received, the "Parts" department can run the 'Macro - New Item Upload' macro with a button that is password protected.

    It sounds pretty hefty, I know, and I sincerely apologize. If you look, you can see my feeble attempt with the use of long IF statements so the second hidden tab can contain what the user places on the available tab.

    Please help me out. I am VERY open to any suggestions for a different way to go about this as well. I accept any and all constructive criticism. Thanks and God bless.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Is anyone willing to help me out on this? I don't have any money to offer but I sincerely need help on this. Did I not post this correctly? Please let me know. Again, thanks to anyone that is willing to step up to the plate and offer a solution or suggest a different approach. God bless.

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    Welcome to the Forum!

    Yes, you posted it correctly and in the proper Forum. The issue I'm having is understanding what you require. Here's my understanding of your process:

    With Workbook New Item Upload Form.xlsx:
    • A Salesperson(s) completes Worksheet New Item Upload Form
    • As this Worksheet is completed Worksheet IDC Working Form is being filled out with a series of "If" statements (except for Column I...it's left blank).
    • At some point in time this workbook is emailed to "another Department" for review.

    (This is where things get a bit fuzzy)

    After review that "other" Department runs a Macro to prep the data for Oracle.
    The Workbook we just emailed has no Macros in it; the Workbook "Macro - New Item Upload.xlsm" does have a Macro (Macro8) in it but has no data and only one worksheet (IDC Working Form).

    So I'm missing something here...Workbook (New Item Upload Form.xlsx) contains no Macros but contains data. Workbook "Macro - New Item Upload.xlsm" has a Macro but has no data. In your world, how are they getting married?

    Now, you requested
    take the first tab and create a user-form to be completed
    I assume you mean Worksheet New Item Upload Form in Workbook New Item Upload Form.xlsx. Not an issue...can be done (except for the #REF errors in Columns F and O Data Validation...can you fix those...I don't know what they should be. Could be related to the Links in the Workbook).

    Further, you requested
    What I am trying to do is first of all, combine these two files.
    Again, not an issue. Should I assume the survivor will be New Item Upload Form.xlsx (except it'll now be New Item Upload Form.xlsm as it will contain Macros)?

    You can help us help you if you can FULLY describe the current process, step by step. If we don't understand your process we can't write effective code for the process.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Hi John. Thank you for the correction. I apologize for not explaining myself properly. I'm human, therefore, inherently lazy. Okay, what we have been doing, as silly as it may seem, is open BOTH files; 'New Item Upload Form' and 'Macro - New Item Upload'. We simply use the Macro contained in 'Macro - New Item Upload' to make changes on the 'New Item Upload Form'. When we receive the 'New Item Upload Form', it would have already been filled out by one of our Sales Reps, so all the Macro from the 'Macro - New Item Upload' does is simply formats the 'New Item Upload Form' to how it needs to be prior to pushing into our Oracle database. The first tab on the 'New Item Upload Form' is basically the form that our sales force uses to fill out their items for upload. I want to eliminate that tab altogether and have a userform in the same format with some code behind it that can populate the info needed on the second tab (hidden) on the 'New Item Upload Form'.

    I have fixed the #REF errors. That was an oversight on my part.

    I have attached the files with the errors corrected.

    Like you said, basically, by combining the two files, I will simply be changing 'New Item Upload Form.xlsx' to 'New Item Upload Form.xlsm'

    I hope this clears things up a bit. Again, please accept my apology for the cloudy description of my need. God bless
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Do you need anything else from me for this...?

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Not for the moment.

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    Will Column B (INV_ORG) EVER BE other than "103"? If so when?

    Will Column G (PV_Approval) EVER BE other than "Y"? If so when?

    What goes in Column I (Compliance_Code)?

    Will Column K (Expired) EVER BE other than "N"? If so when?

    Is Column J (NSN) ALWAYS a 13 digit string?
    Last edited by jaslake; 11-14-2012 at 04:49 PM.

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    Do you need anything else from me for this...?
    Now I do. I need answers to the questions I asked in Post #7 and I need you to play with the attached file that incorporates
    take the first tab and create a user-form to be completed.
    The attached does not yet address
    create some buttons on that form to send to the final department via out. Their test email is: [email protected]
    Once the email is received, the "Parts" department can run the 'Macro - New Item Upload' macro with a button that is password protected.
    What I need you to do is see if Sheet "IDC Working Form" is being populated as you expect. Play with it...get back to me.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Thank you so so much John!! Everything works perfect so far. To answer your questions:

    Q Will Column B (INV_ORG) EVER BE other than "103"? If so when?
    A This will always be "103"

    Q Will Column G (PV_Approval) EVER BE other than "Y"? If so when?
    A This column will always be "Y"

    Q What goes in Column I (Compliance_Code)?
    A The string "ITP" always goes in this column,

    Q Will Column K (Expired) EVER BE other than "N"? If so when?
    A This will always be "N"

    Q Is Column J (NSN) ALWAYS a 13 digit string?
    A This will always be a 13 digit string in this format "0000-00-000-0000"[/COLOR]

    Now, will there be another button to run the Macro that was previously contained in 'Macro - New Item Upload'? If so, would there be a possibility to have that button password protected so only the "Parts" department can successfully hit that button to run the Macro? Thank you again so much John! God bless you!

  10. #10
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    Let's leave this for a moment...we're not that far along
    will there be another button to run the Macro that was previously contained in 'Macro - New Item Upload'? If so, would there be a possibility to have that button password protected so only the "Parts" department can successfully hit that button to run the Macro
    We're still building the Foundation...let's deal with that first.

    Is the string in Column J (NSN) ALWAYS Numeric or could it be Alpha Numeric?
    Last edited by jaslake; 11-14-2012 at 08:57 PM.

  11. #11
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Yes sir. The NSN is always a numeric value. Thanks again sir!

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    How painful would it be for your users to enter the NSN as a 13 digit number (without the formatting)?

  13. #13
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    It would not be painful at all. I can have that update by that department once the items have been pushed into Oracle. I can definitely work with the NSN without the formatting. Thanks

  14. #14
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    You won't have to do this
    I can have that update by that department once the items have been pushed into Oracle
    The code I provide will do this. I'm going to send you a new file with tentatively final code to prep the file for Email (not tonight...still more testing to do in the AM).

  15. #15
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    The code in the attached file appears to do as you require for the Data Entry part of your issue. Let me know of issues.
    In the meantime I'll work on the Email part of the issue. I'm assuming the only sheet that needs to be sent is the IDC Working Form. Is this correct?
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Yes sir, that is correct, the only sheet that should be emailed is the IDC Working Form. Again, thank you so much John. I sincerely appreciate this help you have provided. God bless you.

  17. #17
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Simply amazing John. This looks awesome. Thank you so much! God bless you!

  18. #18
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    The code in the attached will send an email with the IDC Working Form as an attachment. Code will need to be written to reformat as necessary for the Oracle interface. Let me know what I can do to help.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Thank you so much for the help John. I am so grateful for your help and assistance with this project. I have started the implementation process for this. I am using your code as a learning experience as well, so you have caught a fish for me as well as taught me how to fish at the same time. I really appreciate it sir. God bless you!!

  20. #20
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    You're welcome...glad I could help.

    If this resolves your issue 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

  21. #21
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    One last question before I mark solved. I have attached what I have done to hopefully get this operational status this week. I am attempting to create 2 routes for the user to take. I created a command button with the userform you provided for someone to submit just a single item. I took that a step further and created a command button with a new userform in order for the user to create mulitple items at once before they email this to the Parts Department. I have provided my file for your review. I think I am going about this the wrong way. Is there a more streamlined code I can use for the multiple item userform rather than duplicating your code for each row...? Thanks again for helping me with this. God bless.

    Couple things I'd like to point out:
    What do I need to do to stop having it send two emails?
    How can I have the email send with the "IDC Working Form" hidden?

    Thank you again - SO MUCH!!
    Attached Files Attached Files

  22. #22
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    I'll get back to you...too late tonight.

  23. #23
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Okay. No need to rush man. I am grateful that you even bothered to help in the first place. Let me know if you have any questions. Thanks again John.

  24. #24
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    To fix this
    What do I need to do to stop having it send two emails?
    In the Sub Mail_IDC_Working_Form()
    Please Login or Register  to view this content.
    To fix this
    How can I have the email send with the "IDC Working Form" hidden
    In the Sub Mail_IDC_Working_Form()
    Please Login or Register  to view this content.
    Regarding this
    Is there a more streamlined code I can use for the multiple item userform rather than duplicating your code for each row
    Qualified Yes...I need to ponder it...get back to you.

    FYI...you don't REALLY need the Multi Form. The user can add as many items as they wish on the single form. When the user is done, click the Mail Button. Further, after the Mail is sent you'll no doubt wish to clear the IDC Working Form.

  25. #25
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    Still pondering...have much of it resolved. What have you decided...still want the Multi Form?

  26. #26
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    After speaking with my some of the guys at work, they think I should still come up with a mutli-form in order to maintain consistency with what I had before. I will keep a method for them to have a single upload versus having the multi-form format so they can build all of their items in a "spreadsheet" type format before they hit the button to move to the "IDC Working Tab". Thanks again John and God bless!

  27. #27
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    I've the issues surrounded. There's a hitch in the Getty Up that relates to TextBoxes 7 through 12. I'm playing with a solution that doesn't involve adding more TextBoxes to the UserForm. Get back to you.

  28. #28
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Thanks again John!

  29. #29
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    It would be amazing if it could be without adding textboxes!

  30. #30
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    Are there ANY fields that are NOT required...may the User leave ANY field blank?

  31. #31
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    The goal is to have them complete all fields. If not, then it would force them to enter some value. Thanks

  32. #32
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    Well I've beat the MultiForm to death and have eliminated all the bugs I can find. I need a fresh set of eyes to look at it and try to break it...you're elected.
    Let me know what you find.

    PS: There are some Tab issues as you move down the MultiForm as you get down to about the 9th line item...I figured you could fix this...if it's a problem let me know.

    PPS: I eliminated a bunch of your colors...I'm color blind and couldn't see stuff...put them back in when things are working as you require.
    Attached Files Attached Files
    Last edited by jaslake; 11-23-2012 at 04:25 PM.

  33. #33
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    Wow! This is undoubtedly a masterpiece. I tested the Multi-form and had no errors. The tab issues are an easy fix, no worries there. John, I absolutely appreciate this man. I wish I could shake your hand for all of the effort. You are a Saint! Thank you again for the extremely hard work. I am grateful Sir! Thank you again.

  34. #34
    Registered User
    Join Date
    09-30-2012
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: Please Help - Userform For Item Additions

    I will test this more extensively in a production setting on Monday once I return to the office. Once I have done that, I will mark this thread solved. Thank you again John. God bless you a thousand times!

  35. #35
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Please Help - Userform For Item Additions

    Hi chrisewright20

    You're welcome...glad I can be of help...thank you for all the Blessings...I Believe and I need 'em. If you don't mind I'll pass a few along to certain Family members.

    As I was working on the MultiForm, and assuming all the kinks are (or will be) worked out, I thought "Let's get rid of the Single Form, it's not required". Think about it...ask your Users. They can add 1 item or up to 15 from the MultiForm. And it's one less UserForm and associated Code to manage.

    Let me know how the project works out...it's been an interesting exercise. I'm certainly not expert on Class Modules and haven't used them in a while. So it was good to get a bit reacquainted with them.

    PS: There's some extra code in several Modules that I've commented out...please leave it for the moment.
    Last edited by jaslake; 11-24-2012 at 04:52 PM.

+ 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