Solved

Macro to Add a Kit and its Bill of Materials to an Existing Database

Posted on 2013-01-21
106
402 Views
Last Modified: 2013-02-07
Greetings Everyone.  I have worked with a close coding friend to develop a spreadsheet which handles requests from a sales department to add new items to an existing database.  The current process is as follows:

Step One:  
 - Sales Department user opens and completes 'New Item Upload Form' spreadsheet.
Step Two:
 - Upon completion and validations passed, form emailed to the "Parts" Department.
Step Three:
 - Parts Department does final review and imports into Oracle Database.

The issue to be addressed now pertains to the 'type' of item to be added. Currently the only indicator that an item is a Kit is a listbox indicating Yes or No.  I'd like to modify this current spreadsheet to specify the Kit the Bill of Materials which belong to that Kit item.  I have added a second tab which attempts to break out this information.  I am open to new ideas and any constructive criticism.  If there is a better way to approach this, I am all ears.

The concept I have now includes the population of a userform when an item is indicated as  a kit 'Yes' in the specified listbox.  This userform would allow a user to complete the required information for each item belonging to that kit, the quantity of each item per kit, the cost of the item, and so forth.  Once this has been completed, the 'New Item Upload Form' would be submitted to the Parts Department like normal and the Kit BOM would be submitted to Kit's and Components Department and have the Parts Department CC'd on that email.  

The BOM tab on this spreadsheet includes the information required for each item.  It can be modified if needed.  If the item does not currently exist in the Oracle database, then it would be included on both spreadsheets. It would be included on the 'New Item Upload Form' in order to be imported into the Oracle database and also included on the Bill Of Materials spreadsheet in order to have the Kits and Components Department to assign the BOM items to the Kit Part Number within the Oracle Database.  

Thanks for the assistance and support with this one.

Link To Previous Questions:
 - Previous Question Concerning Spreadsheet
New-Item-Upload-Form--with-Kit-B.xlsm
0
Comment
Question by:Christopher Wright
  • 59
  • 45
106 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38802996
I'm looking at it now. BTW I forgot to mention that somehow the sheet acquired an imperfection.
What is it?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38803051
Questions:

1. Why a userform? Can't the user enter the data directly into the Kit-BOM sheet?
2. "If the item does not currently exist in the Oracle database, then it would be included on both spreadsheets". Referring only to the NewItem sheet, what does that mean? Do we need to add a new column to that sheet?
0
 

Author Comment

by:Christopher Wright
ID: 38803054
I am not sure what this is?  It has to do with that particular cell address.  I'm looking into that.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38803102
3. You have the font in the comboboxes set to bold. Do you really want it that way?
4. After entering some data into the New Item sheet I see that most of the work is done on the Kit-BOM sheet. Except perhaps for some validation like requiring a quantity and code to send the new sheet what do you need?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38803379
5. There is a data validation message for the 'Kit Name' on the BOM sheet which says "**Note:  If you have more than one kit, you must complete this form for each kit individually**". That's not correct is it?
6. When the user indicates on the Upload sheet that an item is a Kit, will more than one line ever be required on the BOM sheet for that item?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38803390
7. While it's no big deal, you didn't document at least some of the changes you made like changing the mail module from Display to Send and the text of the Submit confirmation message.
0
 

Author Comment

by:Christopher Wright
ID: 38803535
ID: 38803102
 - No sir, I do not want the combo box font set to bold.  I did not realize I had it set that way.

ID: 38803379
 - I do not want it to be that way.  I was hoping to make this more comprehensive so we can include more than one kit at a time.

ID: 38803390
 - My apologies for not including all changes.  There were so many versions floating around that I had to copy and paste all that changes that I thought were made.  Please forgive my oversight friend.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38803548
Thanks but you answered some of the questions. There were 7 of them numbered 1 to 7.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38803609
I fixed the imperfection.
0
 

Author Comment

by:Christopher Wright
ID: 38803900
Sorry Marty.  The wife was rushing me to run out the door.  I'm all out whack the past couple of days.  I'm so sorry Marty.  I will tighten up my game.  Please forgive my sloppiness.   :(

1.) A userform is not required.  That was just an idea I threw out there.  It is not mandatory whatsoever.
2.) This means that the item would need to be on the 'New Item Upload Form' so it can be added to the Oracle database.  It would also need to be sent to the Kit Manager on the Kit BOM sheet so the kit can be setup in Oracle as well.  No new columns need to be added, it would not be any different than adding a new item.  The only difference is the new Kit BOM form which identifies the Kit and its BOM.
3.) No sir, I do not want the combo box font set to bold.  I did not realize I had it set that way.
4.) I was looking for validation on the Kit BOM form that asks "Is Item In Oracle?", if NO is selected then it should force the user to have it completed on the 'New Item Upload Form.'
5.)  I do not want it to be that way.  I was hoping to make the entire process a bit more comprehensive so we can include more than one kit at a time.  That was my reasoning behind the userform.  I was thinking of the Kit BOM tab being hidden as the 'IDC Working Form' is, and the user would input data to that form via the userform which populated.  When submitted, it would send the hidden Kit BOM tab as the sheet does now with the IDC Working Form tab.
6.) A part number will only show up one time per kit.  But a kit could obviously have multiple rows with distinct part numbers.
7.) My apologies for not including all changes.  There were so many versions floating around that I had to copy and paste all that changes that I thought were made.  Please forgive my oversight friend.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38803914
Thanks and no apologies necessary. Can you list any and all validations that need to be done on the BOM sheet?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38806642
I assume that the only rows where 'Is It a Kit' is "Y" should generate a row on the KitBOM sheet. Correct?

If so then there are two problems:

First Problem
The formulas on the Kit BOM sheet are ignoring the fact that Is Item a Kit may be 'N'. A typical formula is

=IF('New Item Upload Form'!A3<>"",'New Item Upload Form'!C3,"")

Since all fields on the New Item sheet will be validated before going to the KIT sheet we don't need to worry about blanks so I can change that formula and all the similar ones to

=IF('New Item Upload Form'!J3="Y",'New Item Upload Form'!C3,"")

Please let me know if you see any problem with that.

Second Problem
This may be difficult for me to fix but maybe you know how to get around it. The problem is that the formulas on the Kit sheet are copied down. In other words a typical formula on row 4 of the Kit sheet says (I've added "a" and "b" in front of them to make them easier to refer to)

a) =IF('New Item Upload Form'!A3<>"",'New Item Upload Form'!C3,"")
 and the one on row 5 says
b) =IF('New Item Upload Form'!A4<>"",'New Item Upload Form'!C4,"")

So a) refers to row 3 on the New Item sheet and b) refers to row 4 on the New Item sheet,  however neither one may be a kit.

As I write this I think I just realized what the solution needs to be. Rather than depend on formulas to transfer the data, I should populate the rows on the Kit sheet via code, just like I do now for the IDC sheet.

I can't continue until this is resolved so please let me know what you think.
0
 

Author Comment

by:Christopher Wright
ID: 38807183
Sorry for the delay Marty.  Lovely day at work today for me.

To answer your question, if an item is marked as Kit ""Y" then it should populate on the KitBOM sheet.  However, the items in the BOM need to be included on that sheet and somehow "grouped" to the Kit Part Number.  Basically, a user marks an item as Kit "Y", then that item is passed over to the KitBOM sheet.  My idea was to have a userform which populated to alert the user that since they have marked the item as Kit "Y", they now need to identify the Bill of Material (BOM) items for that particular kit.  My concept was to have the user simply populate the data onto the userform.  When the user began their completion of the message box/userform, there would be a selection for each item entered asking if the BOM item was 'Item Already in Oracle'.  If they select "N" to 'Item Already in Oracle', then it would populate send the data to both the 'New Item Upload Form' and the KitBOM sheet.  The idea of the userform would help to segregate/group the BOM items to their particular Kit. This would be especially important if more than one item is marked as Kit "y". A worksheet works just as well as a userform, I simply came up with that before I began working with you.
0
 

Author Comment

by:Christopher Wright
ID: 38807204
Also, I am curious as to how you fixed our little imperfection on the form?  Did you find out the cause and what it was exactly?  Thank you for fixing it too my friend.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38807566
The New Item sheet has a floating combobox called cboTemp that is used to display all the different combobox values. Once it is set to Visible = False it's seems impossible to find (if you can figure out how, let me know). It turned out that the imperfection was a zero-width version (how that happened I have no idea)  of an early version of that combobox called tempCombo. Once I moved the blue label out of the way I could expand it and delete it.

Back to the KitBOM and some new questions…

8. Where do 'Kit Name' and 'Description' (in the headings) come from? If they're entered by the user I think we need a textboxes. However given that the sheet will be used for more than one kit, do either one of them make sense?

9. Right now we remove duplicate part numbers when the data is transferred to the IDC sheet. I think we need to do it "live" so that a potential second KitBOM row for the same part isn't generated. Do you agree?

10. I change the format of the Qty Per Kit from '00' to 'General'. When it was '00' if a user for some unknown reason entered 1.5, Excel would show that as 2, but the Extended Cost would use 1.5 in it's calculation. Is my change OK? Note that I plan on adding a validation which insures that only whole numbers > 0 can be entered.

11. Does "If they select "N" to 'Item Already in Oracle', then it would populate send the data to both the 'New Item Upload Form'…" mean that when the answer is no I need to add that vendor part number to the New Item sheet?

12. I believe I can generate a userform which would eliminate the need for the user to ever actually see the Kit-BOM sheet. The form below (which is a mockup) would initially show 1 row for each part that's indicated as a Kit. Clicking the '+' would generate a line containing 3 controls like the first three on that line. Clicking the '-' would delete the line but they would not be allowed to delete the original line. Since I can get most of the information for the KitBOM sheet from the User Input sheet, is any other user input required? For example, are the Vendor and/or Country of Origin always the same as the original part number? If no then I'll need to add controls for it/them.

Mockup of proposed KIt BOM userform
0
 

Author Comment

by:Christopher Wright
ID: 38808126
I am in class at the moment with a dying laptop.  Once I can plug-in, I will answer.  Sorry my friend!  I'm on top of it though.  Thanks Marty!!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38809570
I'm going to have to back off on the idea I proposed in 12 above. After making some initial attempts in a demo workbook I see that it would be too difficult to keep track of all the dynamic controls that would need to be generated.
0
 

Author Comment

by:Christopher Wright
ID: 38809943
Oh, I see.  What is your thought now?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38810025
I'd like your comments on 8 to 11 above first please.
0
 

Author Comment

by:Christopher Wright
ID: 38811187
8.) Currently, this info is entered manually into those cells by the user.
9.) I totally agree. By doing this live, we can prevent the population of unnecessary warning/error messages.  Great idea!!!
10.) That change is perfectly fine and appropriate.  Thanks Marty!
11.) Yes sir.  I was thinking that the part number would be added to both.  It would be added to the ‘New Item Sheet so it can be added to Oracle and added to the KitBOM sheet to be setup by the Kit manager.
12.) Disregarded as per your advice.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38811217
Sorry not to be clear, but my point in 8 was that AFAIK the KitBOM sheet will now be used for multiple kits, so a (single) kit name and a (single) description probably don't make sense and I'd like to get rid of both rows and replace them with a heading that looks similar to the Item Upload sheet. Should I do that?
0
 

Author Comment

by:Christopher Wright
ID: 38812471
Yes sir.  Sounds like a great plan to me.  :)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38812529
Okay then, in addition to what we've talked about above here's my plan.

The user will not be allowed to go to the BOM sheet unless all errors are corrected first.

The act of going to the BOM will cause the appropriate data from the New Item sheet to be transferred to the BOM sheet if that data is not already there. This will only happen for those where 'Is this a kit' is "Y'.

The user will not be able to email until all errors are corrected and the quantity on the BOM rows is entered. This will also stop people if there's a 'Y' on the sheet but they haven't been to the BOM to complete it. Please tell me what else I should validate on the BOM since they will be able to change most anything they like. Should we have the same drop downs for things like Country that we have on the New Item sheet?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38812856
Here's a first draft of my scheme. It does not mail the BOM but it does do everything else. You'll find that it contains a K -BOM backup sheet which is just a safety measure while I'm testing.

Please see the msgbox in BOM_LastRow function and let me know if that's okay, or if not what it should say.
Marty0123.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38814617
Given that the BOM sheet will have more than one kit,
13) does the total line at the bottom serve any purpose? Do you want a total line for each kit? 14) Is the "please return in Excel format" line at the bottom needed?

And
15) I need the email address for the Kit's and Components Department.
0
 

Author Comment

by:Christopher Wright
ID: 38815086
Quick question - Your name keeps coming up in our email box
0
 

Author Comment

by:Christopher Wright
ID: 38815090
I think we received maybe 20 emails with your name.  Everyone starting chiming, "Who is Martin Liss?"
0
 

Author Comment

by:Christopher Wright
ID: 38815111
Good morning My Friend.  I am sincerely gracious for your help thus far.  I wish I could pay you somehow.  Send a gift, anything! To answer your questions:

13.) The total line is not necessary.
14.) The "please return in Excel format" is NOT needed.
15.) You can my email addresses for testing. I can always go in and add those guys once we are complete.  If you are okay with that?
**content removed  ModeIT 1-24-13*
0
 

Author Comment

by:Christopher Wright
ID: 38815116
I think you were testing this and it came across.  It was 17 emails that came through like you had a loop running on your end...
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38815120
I'm famous:) Sorry about that. I'm testing the mailing of the BOM sheet but didn't have the address for the Kit's dept (see 15 above). Anyhow i'll change to .Display for both sheets.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38815164
If you are okay with that?
Yes, and you should probably 'Request Attention' and ask the moderator to delete your email addresses so as to avoid you getting spammed.

Note to mod: The email addresses are and will only be used in the application and not for private communication.
0
 

Author Comment

by:Christopher Wright
ID: 38817187
Thanks for the heads up Marty.  I didn't realize that it would be susceptible here.  I have attached the WB with the updated msgbox message.  Are you awaiting anything from me?
Chris0123.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38817212
Sorry but now that we removed the stuff at the bottom of the BOM sheet there's no need for the message.

Add some data in the NIUF and see what the BOM in the attached looks like! I hope you like it. IMO it's the BOM:)
Marty0124.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 38819327
After some testing, I think I am appreciating the double-click for the comboboxes.  It has really affected the form functionality.  You were right.  :(
0
 

Author Comment

by:Christopher Wright
ID: 38819350
This sheet is incredible!  I like the way you have it show where the end of the kit is for an item.  That's really neat!!
0
 

Author Comment

by:Christopher Wright
ID: 38819365
Quick question, I noticed that the 'IDC Working Form' was not populated in the process.  Will these be combined once we have finished.  Basically, the submit function will include the submission of New Items to the Parts Department as before while simultaneously sending the Kit BOM request to the Kit's Team?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38819475
Yes they will.

After some testing, I think I am appreciating the double-click for the comboboxes.  It has really affected the form functionality.  You were right.  :(
I'm not sure what that means. Do you mean you like the way it works now?

And there's one topic that you haven't responded about and that is validations on the BOM sheet. Currently the only thing that's validated is the quantity, so….
16) Do you want any other of the fields to be validated like they are on the New Item sheet?
17) Should there be drop down lists like there are on that sheet?
18) Can the user change the Country, etc on BOM to something different than what's on the New Item?
19) Should there be a drop down or a list of valid values for the 'In Oracle?' column?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38819720
Oh, and 20) The subject for both emails is now "New Item Upload Request". Do you want the BOM email to say something else?
0
 

Author Comment

by:Christopher Wright
ID: 38820932
Good Evening/Afternoon Marty.  It's been such a busy week, but busy is definitely a good thing when it comes to business.  My apologies for the sporadic correspondence.  Pertaining to the quote:

After some testing, I think I am appreciating the double-click for the comboboxes.  It has really affected the form functionality.  You were right.  :(

This simply means that the form is volatile.  When the field is selected, the combobox opens, thus making the user wait.  It also makes it difficult for tabbing from field to field.  On the previous versions that require a double click, the user has more control over whether they deem the combobox necessary or not.  I have been using that form instead of the auto combobox for the purpose of practicality.

To answer your questions:
16.) I was under the impression that the values would be validated on the New Item Upload Form therefore I didn't deem it necessary for redundant validation.  However, I now realize that there is now back and forth functionality.  Basically, the user can complete either the 'New Item Upload Form' or the 'Kit BOM' form.  With that being said, all values should be validated if the values on the 'Kit BOM' form if it was manually entered by the user.  Is there a way to differentiate?  

17.) Since the user has the ability to start with the 'Kit Bom' form, there should be drop downs for the user to select.  I would have drop downs for all columns with a list of values. (ie: Vendor Name, Country of Origin, etc.)

18.) The user should not be able to create a different Country of Origin value for an item between sheets.  This should be mirrored from one sheet to the next.

19.) I am thinking that simple option button/check box would suffice for this value.  If the item is in Oracle, they check the block, if not, they leave it blank.

20.) Yes sir, can the Kit BOM request email state "New Kit & Components Request" & "dd-mm-yyyy hh:mm".

Thank you for the help and support my friend!  God bless.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38820980
I can easily put back the double-click requirement and I will.

16 & 18) The values are validated on the New Item form but after transfer to the BOM the user at present can change what's on the BOM. So for BOM validation I propose this using Country as an example:
     If the BOM part number is on the NIUF then the Country must be the same as the NIUF Country of Origin. If they are not the same I can do a few different things. ONE, just the usual yellow error message saying that "Country must be the same" (or some such), or TWO,  change what's on the BOM to match the NIUF with an "error" saying "value changed", or THREE just change it.

     If the BOM part number is not on the NIUF then the BOM Country of Origin can be anything as long as it's in the list.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38822380
I went ahead and assumed you'd want my "ONE" possibility in the previous post. Give it a try.
Marty0126.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38823100
Here's a slightly updated version. Click one of the 'footer' rows on the BOM sheet:)

BTW if I haven't mentioned it before the Mail_IDC_Sheet sub has been replaced by MailSheets.
Marty0126a.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 38823449
Good evening Marty.  I looked in the footer rows and I'm not seeing anything?  What am I looking for?  This form looks great by the way!  I am thinking of actually splitting these into two separate user options.  For instance:

SHEET SCENARIO ONE:
If a user has to put a kit together and send to the kit manager for approval, they would open the KIT BOM sheet.  Now, if the user builds the kit with items that are NOT in Oracle, then they will be prompted to complete the NEW ITEM UPLOAD sheet (this sheet will be hidden unless needed).  Then they hit submit and the KIT BOM sheet will be emailed to the kit manager and the NEW ITEM UPLOAD sheet will be emailed to the Parts Department.  

SHEET SCENARIO TWO:
If a user has items to have added and some happen to be KIT items, then they will be prompted to complete the KIT BOM sheet (this sheet will be hidden unless needed).  Then they hit submit and the NEW ITEM UPLOAD sheet will be emailed to the kit manager and the KIT BOM sheet will be emailed to the Parts Department.

What do you think about setting it up this way?  Thanks as always for everything you have done so far my dear friend.  God bless you!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38824082
I should have said double click a footer row and when you do that a new row is added above it. I see now that the new line isn't being formatted properly so I'll fix that.

I can do either one of your scenarios but I think we are at a point again where a new question is warranted.
0
 

Author Comment

by:Christopher Wright
ID: 38824946
I agree there.  Would you like for me to post it now or would you rather I wait until you have resolved the line formatting?  Thanks again for all of your help my friend.  For the scenarios, I think we already have one complete as far as the New Item Upload process.  We will simply have to modify the Kit Bom sheet so that it is hidden unless needed, then the user is prompted to use.  We are almost finished with the Kit Bom process.  We only need to modify so that the New Item Upload form is after the Kit Bom sheet and hidden.  Like the other, it would only be seen when prompted.  Thanks so much for the help.
0
 

Author Comment

by:Christopher Wright
ID: 38824948
I should not have named those sheets as  SCENARIO in the other post.  They would be two separate spreadsheets but overlap in their procedures and processes.  I hope that makes sense.  Thanks
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38824970
Lets wait. I'm doing some "finishing touches" and I (as usual) have questions:)

21) When a value is 1000 (or more) the formatting on the NIUF shows it as 1000 but 1,000 on the BOM. Should they be the same and if so which way?

22) There are some heading inconsistencies

NIUF                        BOM
Vendor Name            Vendor
ADS Cost                  Cost
All uppercase            Mixed case

Do you want to change them to make them consistent? If so which sheet should be changed?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38825007
I should not have named those sheets as  SCENARIO in the other post.  They would be two separate spreadsheets but overlap in their procedures and processes.
I'd need to think about it because if code overlaps it is error prone and time-consuming to make the same changes in two places. Could we not have just one workbook with a list of usernames who would see the workbook one way and other people the other way?
0
 

Author Comment

by:Christopher Wright
ID: 38825008
21.) & 22.) Great catch!  I actually forgot to mention that altogether.  I noticed it Friday but did not prioritize it at the moment.  IMO, it would be best to have the Kit BOM match the formatting of the NIUF.  What do you think?  Thanks Buddy.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38825018
I'll make the BOM look like the NIUF.

I should have an updated WB for you within the hour. (We've been doing this a long time haven't we?)
0
 

Author Comment

by:Christopher Wright
ID: 38825097
Yes sir.  It's been quite an adventure.  But I have had fun in learning from you sir.  Thanks Marty.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38825099
Okay here it is. I encourage you to look in the Change Log but here's the main stuff that is changed.

Froze the BOM headings like the NIUF headings are frozen
Backed out the 6b change autodropdown change
Corrected footer double-click
Made header rows un-selectable in both sheets. Couldn't prevent Textboxes in headers from being selectable however. Note that that change only affects people other than us.
Changed Environ use in the workbook to make it easier to maintain. This  included adding a table named "All-Powerful" in the Form Codes sheet which contains our pc user names. It also contains "Chris". Way back I used that in some of the environ code because I didn't know your user name and I put it there as a placeholder. Remove it if it's not valid. A note for the future: If anyone else works on the WB, there's user name should be added.
Marty0127.xlsm
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Christopher Wright
ID: 38825177
Greetings Marty.  I have tested your file and ran into a few issues/questions.  I have listed them below:

23.) The items on the New Item Upload Form did not copy the items onto the hidden 'IDC Working Form' tab.  

24.) For Kit Part Numbers on the Kit Bom Form have the Extended Cost field auto calculated and uneditable by the user?  This would be a simple calculation. (ADS Cost * Qty of Items per Kit)  

25.) Can we have the Qty per kit greyed out for the Kit part number itself?  This will never be a field that needs to be populated by the user.

26.) Can we have the drop down list boxes on the Kit Bom Form have the double click combo box just like the New Item Upload Form?
 
27.) Is it a possibility to have the rows that are auto created to be formatted as the line above it?  Currently it comes out with no borders.  I am simply thinking ahead of the users getting data into the wrong cell.  Trust me, it happens.  :(

28.) For an item that is on the New Item upload form, can we have that auto filled to N for is item in Oracle?

29.) Could we have the Total Cost of the Kit added to the footer row under the Extended Cost Column by chance?  The Footer Row could read something such as :
'End of Bill of Materials for Part Number TEST-KIT-2B               Total Kit Cost: $100.00'

30.) This question may need to wait for our next Posting but I figured I'd type it out:
-When we branch these two processes apart, will it be possible during the New Item Upload process to have the user be stopped as soon as they enter Y on the Is Item a Kit Field.  They would be stopped and prompted to enter the Kit BOM immediately. Then if they enter a N for is item in Oracle, that info would transfer over to the New Item Upload Form.  Then we could have other handles on the Kit Bom sheet.  I know I'm getting ahead of myself but thought I'd mention it.

Thank you for staying with me throughout this long and arduous process.  I really am grateful and appreciative for all of the help, support, mentorship, and patience! God bless.
Chris0127.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38825287
I opened your WB and I don't see any notations in the ChangeLog that would indicate that you changed anything. Did you?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38828950
Here's the latest. It includes changes/fixes for 23 to 29.

For 25, there's no way to protect a cell and make it un-editable unless the whole sheet is protected. the best thing I could think of to do was to validate it so that if the user did change it it would get a yellow error.

For 29, I'm not 100% happy with the way it turned out. The problem is that I designed the footer row as a merged cell row, so positioning things so that for example the kit total lines up nicely is not easy. I would up right justifying the cell and inserting a calculated number of spaces in certain places. Maybe I can do better at a later date.
Marty0128.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38828993
For 30, in the current process without any changes if the user starts in the NIUF and enters a kit part, he will be "stopped" because the Extended cost won't be there. Also he can't produce the emails unless the kit BOM is completed.
0
 

Author Comment

by:Christopher Wright
ID: 38829194
I did not make any changes to the form.  I should have included that in my latest message.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38829391
I just noticed that I left a combobox showing on the NIUF to the right of the headings. Ignore it, it will be gone next time.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38831875
Don't bother with Marty0128. This one formats the totals in the footer much better (it's no longer one big merged cell) and I really fixed the row borders problem in the double-click of the footer to add line.
Marty0129.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 38831883
Okay - Perfect.  I will take a look at it momentarily.  Sorry, at work right now.  Looks like another busy week.
0
 

Author Comment

by:Christopher Wright
ID: 38833533
Greetings Marty.  I am still running into some issues with the form when it is submitted.  The Kit Bom sheet comes across as an appointment reminder instead of an email with attachment.  The is still no validation in terms of all CAPS.  Also, can the extended quantity be automated and not touchable by the user.  Thank you so much
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38833540
The is still no validation in terms of all CAPS.
Please explain.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38833543
As far as it being an appointment, I hopefully can fix that, but the attachment is still a workbook.

Edit: I just fixed it:)
0
 

Author Comment

by:Christopher Wright
ID: 38833554
I'm going to try to tackle the all caps validation.  I will note all changes in the change module.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38833555
At this point please don't. It would be better if I made all the changes.
0
 

Author Comment

by:Christopher Wright
ID: 38833559
My apologies for the ambiguity.  On the new item upload form, if you type a lowercase it auto updates to all upper.  Sorry
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38833564
So does that means it is working correctly?
0
 

Author Comment

by:Christopher Wright
ID: 38833588
I wanted to thank you for the patience Marty.  I realize I have probably tested it often.  Please forgive me for that.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38833594
No problem, but please confirm that the only problem is the email being an appointment.
0
 

Author Comment

by:Christopher Wright
ID: 38835755
The intent was to have the Kit BOM sheet auto convert any lowercase to uppercase just as the NIUF.  I will go through the sheet again to confirm that the only problem is the email/appointment issue and I'll list out any other issues that I find.  Thanks again friend.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38835895
Oops, I found a bug so you might want to wait for a new version. The bug is that after you go to the BOM, if you go back to the NIUF again and add a new part, the BOM sheet gets messed up.

The good news is that I found a simple way to guarantee that everything is uppercase.
0
 

Author Comment

by:Christopher Wright
ID: 38836059
Perfect!  Thanks buddy!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38836444
Give this a whirl. Changes include

A better looking footer row
Really (really) fixed the BOM footer double-click process
Correct the bug which was causing the BOM email to be an appointment.
Guarantee that everything is upper case by putting code in the Worksheet_Change event for both the NIUF and BOM sheets which by default checks every cell on the sheets
Fixed the bug where after TransferToBOM, if you were to go back to the NIUF and add a new kit, TransferToBOM would put the data in the wrong place.

I also added a couple of Notes at the bottom of the ChangeLog that you may find useful.

I know there things in addition to this that you want done, but once you've tested this WB and assuming there aren't any bugs let's go on to a new question.
Marty0130.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38837483
Thoughts for the future:

At some time in the past I thought you said you might want to have one group of people see something different from another group but I couldn't find the post. Recently you did say this however.

SHEET SCENARIO ONE:
If a user has to put a kit together and send to the kit manager for approval, they would open the KIT BOM sheet.  Now, if the user builds the kit with items that are NOT in Oracle, then they will be prompted to complete the NEW ITEM UPLOAD sheet (this sheet will be hidden unless needed).  Then they hit submit and the KIT BOM sheet will be emailed to the kit manager and the NEW ITEM UPLOAD sheet will be emailed to the Parts Department.  

SHEET SCENARIO TWO:
If a user has items to have added and some happen to be KIT items, then they will be prompted to complete the KIT BOM sheet (this sheet will be hidden unless needed).  Then they hit submit and the NEW ITEM UPLOAD sheet will be emailed to the kit manager and the KIT BOM sheet will be emailed to the Parts Department.

Unless I misunderstand what you want, I think that the two scenarios are mutually exclusive. (See the two phrases I underlined). I say that because each one wants the other sheet to initially be hidden which if both scenarios were implemented would result in neither the NIUF nor the BOM sheet being visible.
0
 

Author Comment

by:Christopher Wright
ID: 38839461
These two will be two exclusive spreadsheets.  One sheet will be used to add new items.  The other will be used to request a kit be built.  Each one will have the other sheet hidden unless the need arises.  For instance, the user wants to request the addition of new items.  In the new items to be added, one is a kit, then the kit bom spreadsheet is prompted for completion. Would it be best to have this setup as it was in the very beginning? If you remember, there was a spreadsheet that had a "home page" if you will. When the spreadsheet is opened, there are choices laid out for the user.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38839709
I hope you don't mean that you want two separate workbooks because that would be very hard to maintain. Otherwise we could do something to ask what the user wants to do, but what's the problem with always displaying both sheets?

Were you able to test the current WB?
0
 

Author Comment

by:Christopher Wright
ID: 38840946
No sir.  I do not want two separate workbooks individually.  It would be best described as two processes.  The two will be located in the same workbook but users would have the option to choose if they want to exclusively add a kit or exclusively add new items.  Now if while they are doing either process, they would be prompted with the other worksheet if required.  

Tested the current workbook:
- The Qty per Kit for the 2nd kit keeps stating that the "qty for the kit can only be 1" but on the components row.  
- The total kit cost looks great! It is not adding up as components are added.
- Is it possible to use the country of origin value from the New item upload form to populate the Kit bom sheet?  

This is looking amazing Marty.  Wow.  Thank you for seeing this through thus far.  God bless you man!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38840969
I'm doing this this on my iPhone so..

"it's not adding up"
I believe it will if you move to another cell but I think I can make it do it in real time.


More later.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38841344
The Qty per Kit for the 2nd kit keeps stating that the "qty for the kit can only be 1" but on the components row
.  
I can't reproduce that error so tell me the steps that caused it to occurred for you.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38841696
- The Qty per Kit for the 2nd kit keeps stating that the "qty for the kit can only be 1" but on the components row.  ==> As I said above I can't reproduce this.
- The total kit cost looks great! It is not adding up as components are added. ==> It was but I changed it so that the change is more immediate
- Is it possible to use the country of origin value from the New item upload form to populate the Kit bom sheet?==> It already is.  

Anyhow here's today's which reflects the above.
Marty0131.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 38843980
For some reason, it is only occuring on the 2nd Kit Section.  See picture below:

Screen Shot




Also, the Extended Cost field is only calculating on the first Kit section.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38844347
Were you using Marty0131.xlsm when you took that picture? I ask because when I use that workbook I can't reproduce either problem. If you did use that workbook then I need step by step instructions on how to reproduce the problems.

???
0
 

Author Comment

by:Christopher Wright
ID: 38845278
Yes sir, I was using Marty0131.xlsm.  I completed the fields on the both worksheets and submitted.  I have provided the saved workbook with the errors.

Also, is there any way to simply have the data that is on the New Item upload form just auto populate into the Kit Bom sheet based on what is on the NIUF? A lot of the information is the same so it is redundant populating the same info twice.  Is this possible?
Marty0131.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38845581
Also, is there any way to simply have the data that is on the New Item upload form just auto populate into the Kit Bom sheet based on what is on the NIUF?
If you mean when you 'Submit' it does that now.

A lot of the information is the same so it is redundant populating the same info twice.  Is this possible?
If you mean by that that there are duplicate part numbers on the NIUF  and that duplicate kits are created because of that, then that's a topic I brought up before that got overlooked, but it's an easy thing to do.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38845967
"Quantity for the kit part must be 1" problem corrected, plus some other stuff like preventing the extended cost from being changed. The quantity problem was due to the fact I never considered that a sub part number on the BOM might also be on the NIUF.
Marty0201.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38847847
I'm anxious to finish this version so I don't want to do anything now but I just want to get your response on these two things for the future.

31) Should I validate on the BOM that there has to be a total of at least two parts including the kit part for each kit?

32) What if anything should I do if they delete the NIUF line item for a kit that's already on the BOM?

33) Earlier in this thread you said in reference to the combobox drop-down, "When the field is selected, the combobox opens, thus making the user wait.  It also makes it difficult for tabbing from field to field. Is the problem just that it takes a bit of time to open or is it just the fact that it always opens that's the problem?
0
 

Author Comment

by:Christopher Wright
ID: 38848992
31.)  The kit part number is not counted. I am persuaded to believe that a kit would not have a single item so I think that validation checking that there are more that one item is a great solution.  Well played Marty!

32.)  If they delete the corresponding line on the NIUF, I would simply have the value for “Is item in Oracle” changed from “Y” to “N”. since this item no longer needs to be added.  However, I would think it best for validation checking that if the item IS on the NIUF that the Kit Bom should say “N” for that value by default.  I think that is a field that should not be editable by a user but should be controlled by the NIUF. (as the other values  like Country of Origin, Vendor Name, etc.)

33.)  There is no problem with the combobox any long.  Since we went back to controlling it with a double click it is great.  I guess the only issue now is that it doesn’t go away when you tab or click away.  Is that able to be fixed?  Thank you so much friend!
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38849273
31) Thanks and will do in the next question.

32) Will do in the next question.

33) Corrected in the attached workbook

A few other minor changes are included like

Improved the BOM "must be the same as..." error messages by including the NIUF value that was changed.
Changed the Excel data validation message in all drop-downs to make them somewhat customized.
Marty0203.xlsm
0
 

Author Comment

by:Christopher Wright
ID: 38851428
Looks great Marty!  Thank you so much for the help.  Quick question: Why does the post have an administrator comment that off-topic remarks were removed?  Did I say something wrong?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38851583
Nope, it was me. I made a request of you that the moderator reminded me was against the forum rules.
0
 

Author Comment

by:Christopher Wright
ID: 38851743
Oh, now I'm curious of the request...?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38851763
Sorry, I can't explain but don't worry about it.
0
 

Author Closing Comment

by:Christopher Wright
ID: 38854795
Again, Marty has come to my rescue and help.  He's blessed me with his time and patience to see this project all the way to fruition.  I am grateful!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38855118
You're welcome.
0
 

Author Comment

by:Christopher Wright
ID: 38855167
I keep getting an error saying type mismatch?  They error message for validation is not populating?  Also, do I need to keep the footers on the Kit Bom or will they auto create when a user selects Y for an item on the NIUF?
Chris0205.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38855461
There are no items with Is it a Kit = Y on the NUIF in the WB you attached. When I changed one of them to "Y" every worked as normal so I deed more information on how your error occurred.

After the data is transferred to the BOM from the NIUF, what do you call the additional parts that you add to a kit? For this discussion I'll call them "component parts".

If you have a kit on the BOM you can safely add and delete component parts. If you delete the kit part or the footer, you should delete the whole kit (kit part, component parts and the footer), otherwise the code will not work properly. If you do remove the total kit, going back to the NIUF and back again to the BOM will cause any kits to be repopulated if they are not already there. Does that answer your question about keeping the footers?

I'll try to add validation around improper/incomplete deleting of kit parts and/or footers.
0
 

Author Comment

by:Christopher Wright
ID: 38857703
Oh, I see.  Great stuff.  Do you think that this is ready to move on to the next question?  

Post ID: 38824082
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38857729
Yup.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38858077
While I'm waiting for the next question I'm working on #32.

In your comments on that question you said
If they delete the corresponding line on the NIUF, I would simply have the value for “Is item in Oracle” changed from “Y” to “N”. since this item no longer needs to be added.
That's not too hard to do but I'm not planning on giving any indication that it was done unless you have some ideas on what I should do.

You also said
However, I would think it best for validation checking that if the item IS on the NIUF that the Kit Bom should say “N” for that value by default.  I think that is a field that should not be editable by a user but should be controlled by the NIUF. (as the other values  like Country of Origin, Vendor Name, etc.)
I'm not sure what you mean because there is no In Oracle? on the NIUF. What's happening now is that the In Oracle on the BOM is being set initially to "N" and the user can change it to "Y" if he wants to. In reading what I just wrote I'm now thinking that what you mean is that it must be "N", but if that's the case then the first part of your comment that I quoted doesn't make any sense since it can never be "Y"

As you can see I'm confused so I'll wait to hear from you before I proceed.
0
 

Author Comment

by:Christopher Wright
ID: 38859269
My apologies Marty.  I am a serial user of ambiguity.  Forgive me please.  To clarify, the 'Is item in Oracle?' field should be an automatic value that should not be able to be changed by the user.  If the item is on the NIUF, then that value should be set to 'N' and if not, then 'Y'.  This field is only allowing the user to see if they have the items on the NIUF from the Kit BOM sheet if needed.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38859866
Understood.

I can't easily stop the user from changing it, but I can as with Country of Origin, Vendor Name, etc. show an error and not allow the reports to be created until it's put back the way it was.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38862261
I can't seem to hide the NIUF or BOM sheet; the 'Hide' menu items are grayed out. Do you know why?

Edit: Never mind, figured it out.
0
 

Author Comment

by:Christopher Wright
ID: 38866100
I'm sorry for the wait Marty.  It's been a hectic day.  Here's the link to the next question:


Next Question
0
 

Author Comment

by:Christopher Wright
ID: 38866102
I'm sorry for the wait Marty.  It's been a hectic day.  Here's the link to the next question:


Next Question
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now