Start Free Trial

Christopher Wright

asked on

Create Program or User-Friendly 'Home Page' for User Requests.

Greetings fellow Experts. I am in need of a solution to the attached spreadsheet. I am trying to build some sort of program or user friendly spreadsheet that will enable a user to go in, choose which option they need ('Request to Add New Items' OR 'Request to have Kit and its Bill of Materials created'), fill out the appropriate form (New Items Form Name = New Item Upload Form (NUIF); Kit Bill of Materials Form Name = Kit BOM Form (KBOM)), and submit. I have collaborated with a very great friend who has helped me thus far (and I am hoping he continues). The form is built with the dual functionality required. All that remains now is creating some sort of "HOME" screen in which the user can select what it is that they need.

NoW the catchy part is this; if the user selects to 'Request to Add New Items' and if during the form completion they indicate that one of the items they are requesting to add is a Kit, then the user will be prompted to complete the other form. I would also like this to work the other way as well. For example, if the user selects to 'Request to have a Kit and it Bill of Materials Created' and if during that process they select that an item is 'NOT' in Oracle, then they will be prompted to complete the form for the NUIF.

Now an even trickier part is this; if the user selects to 'Request to Add New Items' and they happen to be prompted with the KBOM form, the field 'Is Item in Oracle' will not be selectable and will state 'N' if they have placed that part on the NIUF. And as they continue to complete the KBOM form, every item that is on the NIUF will state 'Y' for the 'Is Item in Oracle Field' and will state 'N' for the items they add which are not found on the NIUF. This validation will not be activated if they chose to 'Request to have Kit and its Bill of Materials created'. I hope I am making sense. I have provided a link to the previous question from which this is derived below:

Link to Previous Question
Chris0207-Next-Question.xlsm

Working on it.

I Just ran into an uh-oh. If they choose the 'Request to have Kit and its Bill of Materials created' they start at the BOM sheet, right? If so and they want to add more than 1 kit, how do they indicate where a kit ends since there won't be any 'footers'. Any ideas?

Actually I just thought of one. Maybe I could add a picture-button (like the Submit button at the top of the sheet that when clicked will add a footer. Or I could create a macro that would be assigned to Ctrl+F that would add a footer.

After thinking about some more over night, I don't think you/we should pursue this two-option approach. I have a couple of reasons for saying that which are one, I really don't see the point since as soon as someone who is entering data on the NIUF sheet has a kit to build, he'll be shown the BOM sheet (and vice versa), and two, in order to accomplish what you want, another level of complexity will be need to added to the workbook's code which will make it more difficult to maintain.

If there's some strong reason for doing this please let me know and perhaps we can accomplish the goal in some simpler way.

Christopher Wright

ASKER

I see where you are coming from. I was only pursuing this for ease of use. You know the old saying, you can lead a horse to water but you cant make him drink? I wanted to take them all the way to the water's edge on this one. Any ideas on how I can create some sort of home page to give them an option?

I don't see the point of that since in a lot of cases they'll wind up seeing both sheets anyhow.

I do have a suggestion that's sort of related. How about adding a button to each sheet that when clicked, displays a sheet with instructions for the sheet. It could be a combined set of instruction or specific instructions for the sheet.

With our current workbook should we allow a user to add a kit directly on the BOM regardless of whether or not the kit part is on the NIUF? If so that brings up something I asked above which was

Maybe I could add a picture-button (like the Submit button at the top of the sheet that when clicked will add a footer. Or I could create a macro that would be assigned to Ctrl+F that would add a footer.

If they are allowed to manually create the complete kit on the BOM there needs to be some manual or automatic way for the footer to get generated.

Also when you ask

Any ideas on how I can create some sort of home page to give them an option?

Is there some specific problem(s) you are trying to correct?

Christopher Wright

ASKER

No sir. Ultimately, I want one "go-to" location for the user to go and choose from a drop down of multiple needs. Once chosen, a form would populate for them to complete and then submit, which would email to the correct department. It's essentially going to be a help desk system without the hassles of using ticket numbers and the like.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Christopher Wright

ASKER

Would you be interested in integrating more processes which involves other spreadsheets like the NIUF & Kit Bom?

Christopher Wright

ASKER

For example, there are multiple requests that are sent all day every day such as:

• un-expire/expire part numbers
• change part descriptions
• update a vendors pricing with next year price structures.
And so on.

I can provide the spreadsheet formats for each so we can have a starting point. If you agree to take this on with me, I'd suggest one process at a time which would involve a question per process since they will be involving elements down to the micro level. I'd really like you to help since you are very familiar with it thus far. Thanks Marty.

Christopher Wright

ASKER

Marty provided a better approach and practical logic in which to handle this project altogether. Thank you Martin.

I'm happy to do more/other processes but there are a few things I want to clear up first so please answer all of these if you can.

1. I assume that a user can create a kit from scratch on the BOM. When I designed the footers I didn't consider that so I need some way to know when to add one. I can easily do that if there's only one manually added kit, but if there's more than one how do I tell where one ends and the next one starts? I talked about that above and suggested that I could add a picture-button (like the Submit button at the top of the sheet that when clicked will add a footer. Or I could create a macro that would be assigned to Ctrl+F that would add a footer. Please think about this and tell me what you'd like to do. It doesn't have to be one of those two things.

2. After seeing the list of the 3 new processes, I now understand why you want to show only certain sheets for certain process but since the adding of Oracle parts and the certain of kits are intertwined I don't see the point of hiding one of them when we're doing the other. However I already have a version of the spreadsheet that already does that that I'll attach so you can comment on the approach. Note that there will be problems so don't bother doing any real testing of the WB.

3. I asked above about adding a button to each sheet that when clicked, displays a sheet with instructions (help) for the sheet. It could be a combined set of instruction or specific instructions for the sheet. What do you think?

After you download the WB click the "Oracle" button and then try out the two new buttons at the top of NIUF.
With-Entry-Choice.xlsm

Christopher Wright

ASKER

1.-Users can create kits from scratch (technically, they are building a request to have a kit created from scratch)
- I would prefer to have a submit button since 99% of the users would need to be told to Ctrl+F every time.

2. & 3.)This new sheet is quite nice Marty!! I like it. I like the pop up help display as well. Moving forward, I’d like to have a list box with a list of all the processes to request. I am thinking specific instructions per sheet. That would keep it concise for the user. I like the new buttons as well!!

Unfortunately I don't think eaiher a button or ctrl+f to add the footer will work unless we can be assured that the user will do it as he finishes each kit. If instead he adds two kits and then clicks the button or ctrl+f, how will the code know where to put the first footer? The approach I'm trying out is to add a one-entry drop-down list in the Component Part Number column which says "END OF KIT" and when the code sees that it will replace that row with a footer.

Still not done but try this out. Initially select 'Kits' and then click the Help (question mark) icon.:)

Also please give me your honest opinion about the icons.

First, should we have icons at all, or would you like regular buttons instead?

You may notice that I changed the help and validate icons so that they look similar. Do you like the way they look, and by that I mean to include their colors and gradient?

Should I change the Submit icon so that it looks like those two?

Christopher Wright

ASKER

This looks great Marty! I like it. Quick question, could we simply have a message box populate with the help info instead of creating a new sheet?

I also removed the text box from each sheet and added the text to the header cell. Is this going to create an issue later on? Thanks again.

Yes we could use a msgbox but they are very limited in what they show. The way I've done it is to make a PDF out of a word document and that document could include pictures if you wanted. What is your concern with the way I've done it?

Please attach the sheet with your mods so I can look at it..

Christopher Wright

ASKER

No concern whatsoever. I was simply making a suggestion. I like the idea of pictures. We could add screenshots showing what they are supposed to be doing if they needed help. I really like how its looking so far now. Thank you so much!

What about my questions about the icons?

I think I found something else that I should validate for and that is that there shouldn't be more than one row with the same component part number in a kit. True?

Christopher Wright

ASKER

About the icons:
• I personally like the icons. It gives the sheet a little flare. They look great to me. Buttons are okay but plain.

• I like the colors of the help and validate buttons. All three look similar and like they "belong" on the sheet.

• I'm not sure I know what you mean? Like I said, they all look similar to me. I like the way they look now. How would it change?

About the kit components:
• You are absolutely correct, there should only be one row per item/part number per kit. If the kit contains multiples of that item/part number, then it will be indicated in the quantity column.

What I mean about the email (submit) icon is that I could change it to something like this

User generated image

but with a bigger symbol.

Christopher Wright

ASKER

Personally, I like the style we have now. Unless you want to change it, I say we keep it as is. What do you think?

How about this?

User generated image

BTW, you said above

I also removed the text box from each sheet and added the text to the header cell. Is this going to create an issue later on? Thanks again.

and I asked if you would attach the wb so I could see what you mean.

Christopher Wright

ASKER

I like that! Very nice. How did you combine the two? Did you use picture software? Great stuff Marty. :)

I apologize for not attaching the workbook. I updated the change log as well. Here it is.
Chris0211.xlsm

I use Photoshop Elements 9. I originally found the green to yellow button icon on the web and PS 9 allowed me to first change the icon's border from black to green, and then in this case to overlay the icon that I was previously using for Submit on top of it. The program is very feature rich and I've barely scratched the surface.

Your change is no problem at all and I'll do the same thing in my version.

Give this a whirl and let me know what you find. I've also attached the Word documents that are a basis of the help sheets so that you can add what you feel is appropriate.

YOU SHOULD TEST THIS AS A REGULAR HUMAN

There were a lot of changes in this version including:

Added Validate icons to both the NIUF and BOM sheets

Changed the 'Submit' icon on the NIUF and added one like it to the BOM.

Validate BOM for "kits" that contain only one part. This validation only happens during the 'Submit' process.

Made "In Oracle?" non-editable.

Added code to ValidateKit_BOM to set In Oracle to "N" if the Component part number is on the NIUF and "Y" otherwise

Created the ProtectWorkbook routine so that if the WB password is changed, it only needs to be done in one place.

Added a little code to delete the BOM items after a successful Submit

Added a lot of code to support the user making a choice between adding Oracle parts and adding kits.

Added code to support manually adding footers.

Added help sheets

Validate kits for no duplicate parts

Some other things to note:

In this version I deleted the annotations for the first several versions. If there are too many annotations the code gets cluttered with them and it's hard to read. I normally only keep the annotations for the last half-dozen or so changes. If in the future in the future you decide to remove any annotations you obviously have to be careful that you don't delete needed code!

In the code I previously set up several global variables. That practice is actually not the best programming practice because being global they can be used/changed anywhere and that's both good and bad. In this version I added a Private Property instead of another global variable. Look for it and see if you understand how it works.

Marty0211.xlsm
HelpBOM.docx
HelpNIUF.docx

Christopher Wright

ASKER

Sorry I didn't get a chance to test this one out last night. I am looking into it. Thanks Marty!

Christopher Wright

ASKER

While testing, the validation works awesome. It catches the appropriate incorrect values as necessary. When I submit the NUIF, I am not getting anything. It appears to be doing something but after some wait, nothing.

Update, I found the issue. I cleared the NUIF and added my own parts. The values which were on the KIT BOM form did not clear out, therefore validation was prohibiting the process from moving forward.

Is there a way to have both the cell highlighted and a msgbox/userform populate which lists the validation errors as well.

Is there a way to transistion between the forms. For instance, I could not go to the KIT BOM form from the NIUF to even check for validation errors.

I am thinking now that maybe when the submit is clicked, instead of highlighting validations concerning incorrect values/symbols/characters, the code automatically replaces these with acceptable values. We would still maintain validation for higher level processes (ie: more than one item per kit, no duplicate items in kit, etc.). What are your thoughts?

I really like the Help sheets. May I add the appropriate write-ups and return to you?

I will continue to test but I wanted to point these out first. Thank you so much Marty!

Is there a way to have both the cell highlighted and a msgbox/userform populate which lists the validation errors as well.

If both sheets are visible and there's an error on the BOM during submitting it will take you there, but as you just found I didn't test it with errors on the BOM and just the NIUF open. I'll add a message like "There are errors on the BOM and emailing can not continue. Do you want to go there now?", and on a "yes" I'll open that sheet.

Is there a way to transistion between the forms.

That's the point I was trying to make when asked about separating this into two processes:) Anyhow I think the new messagebox will fix it.

I am thinking now that maybe when the submit is clicked, instead of highlighting validations concerning incorrect values/symbols/characters, the code automatically replaces these with acceptable values....

I don't think that's a good idea. What if the data on the BOM is right and it's the data on the NIUF that's wrong? I'm sure there are other situations too where an automatic "correction" would cause problems.

I really like the Help sheets. May I add the appropriate write-ups and return to you?

Thanks, and of course you can. That's why I attached them.

Christopher Wright

ASKER

Great idea Marty. That message box with an option sounds great. To me, that sounds like the best route to take. :)

Just to further explain what I meant concerning the auto replace:
I only want to remove/replace the invalid characters which cause errors in the upload. For instance: if the description contains the "TM" trademak symbol, the macro would remove it automatically. These characters would not belong on either sheet at all.

I also found that if the country of origin is 'United States' it does not translate over to the 'IDC Working Form' as the two letter country code. It seems that this is only occuring with this specific country selection.

I only want to remove/replace the invalid characters which cause errors in the upload. For instance: if the description contains the "TM" trademak symbol, the macro would remove it automatically. These characters would not belong on either sheet at all.

OK I understand now and sure, I can do that.

I also found that if the country of origin is 'United States' it does not translate over to the 'IDC Working Form' as the two letter country code. It seems that this is only occuring with this specific country selection.

I'll look into that.

Christopher Wright

ASKER

Are you awaiting anything from me Marty? Hope all is well.

I thought you were still testing. If not then let me know and I'll post an updated wb.

Oh, and you talked about updating the help docs.

Christopher Wright

ASKER

I'm good to go now. I have identified the errors that I found. Sorry for not specifying that I finished my testing.

Okay then here's the latest.

BTW for later on would either of these be of any help?
1) A 'Print' functions to print the BOM and/or NIUF sheets
2) Instead of sending the NIUF and/or the BOM as attachments, put the data directly into the body of the email
Marty0213.xlsm

Christopher Wright

ASKER

The workbook looks great! To answer your suggestions:

1.) We would not need to print often but I do think a print button would be a value add to the sheet. It would have to only print cells with values and be limited so someone doesnt print 1,000,000 rows of nothing.

2.) I'd rather keep the data into spreadsheet format since there would be times when thousands of items are being modified, adjusted, added, etc. That would only clutter the email body, you think?

Also, can we add button to enable to switch back and forth to sheets such as a "HOME" button? Thanks again Marty. You are the best!!!

Okay then I assume we are done with this question. BTW the Print button could, if you want, have two options; print to printer and print to email (like the sample).

Christopher Wright

ASKER

Could we add a home button and/or a Table of Contents which would allow navigation from page to page?

The Home button is easy. We have a sort of table of contents on the 1st page now and I assumed you wanted to add more choices there for your other functions. I guess we will need to change the title of that page because from what I gathered from a previous post of yours, some of the new functions will not be about any kind of "new item".

Christopher Wright

ASKER

Good mornign Marty. I never noticed this but for some reason the file emailed has nothing on it. Also, as you will see on the attached file, it can be quite quite cimbersome searching for all of the errors on large files. Is there a way to enable the user to be directed to the first error, correct it, then correct the next, and so on? Thanks Marty.
Chris0215.xlsm

file emailed has nothing on it

What/which file? I notice that in the WB you attached that both the NUIF and BOM are open, but that there are no kits so the BOM is empty. If you were testing as a regular user (do you know how to do that?) you should not have been able to display the BOM. BTW when I emailed from the NIUF I got a correct report and emailing from the BOM I got nothing which is expected since there's nothing there.

I can definitely give you a find next error process but I think I could also give you a button that when clicked would show only the rows with errors. (I've never done it but it should be possible). Let me know which method you prefer.

Did you notice that with an NIUF that hasn't already been processed that if you click the check-mark icon that Description cells like A21 in your attached workbook get highlighted in green with a comment that explains that (in this case) 1 character was removed?

Christopher Wright

ASKER

My apologies Marty. I was referring to the file that was submitted via email. When I opened the attachment, I only found a blank sheet (headers only). As far as testing as a regular user, I simply add ' before the portion of the code which looks to see if you or I am the user.

I did notice the new green indicators that show each cell that had a replacement/correction. I really like that. I thought I commented on that already. I really like it!

Okay all I can say about the email is that it doesn't happen to me. If it happens again to you please note what the steps were that caused it. To test as a regular user go to Form Codes and modify your userID in column AC in some fashion like adding an x at the end.

Christopher Wright

ASKER

I have posted another question for development of the next process. The link is below:

Next Question