Finishing a Spreadsheet User Interface Program

Working on it.

You are amazing!

In you request you say that you added code the close the workbook after submit. I don't see that code.

The attached may be what you want. I added a modChanges. It's similar to something I do in my VB6 work. Note that the changes I made are annotated in the code with a comment like 'mod2a. That means that the code is related to change '2', description 'a'. If you don't like the idea feel free to delete it.

I noticed that the Vendor ID column is formatted as General and maybe it should be Text because sometimes if a valid number with a large enough value is entered, Excel changes it to scientific format.
New-Columns-Upload-Jan-8.xlsm

Here's a minor tweak that prevents the IDC form header from being deleted.
New-Columns-Upload-Jan-8a.xlsm

Thanks for all of the help Marty.  My apologies for not providing the workbook with the code I inserted to close the workbook.  I thought I had already added it to that file.  An oversight on my part.

Here is what I would do.


Private Sub cmdSubmit_Click()
   
    ' If the user hits 'Enter Another Item' but then realizes they do not have anymore items, they
    ' need a way out. This allows the form to be mailed if everything is blank on the form but
    ' there has been previous input as indicated by the presence one or more rows in the IDC Working Form.
    If Sheets("IDC Working Form").Range("A" & Rows.Count).End(xlUp).Row > 0 And _
       txtDescription.Text = "" And _
       cboKit.ListIndex = -1 And _
       cboVendorName.ListIndex = -1 And _
       txtADS.Text = "" And _
       txtMSRP.Text = "" And _
       txtVendorPN.Text = "" And _
       txtNSN = "" And _
       cboCountry.ListIndex = -1 And _
       cboBerry.ListIndex = -1 And _
       txtManufacturerPN.Text = "" Then
        Application.Cursor = xlWait
        Mail_IDC_Working_Form
        Application.Cursor = xlDefault
        Unload Me
        'new
       Application.DisplayAlerts = False
        ThisWorkbook.Close
       
        Exit Sub
    End If
   
    If ValidateData Then
        UploadSingle
        Application.Cursor = xlWait
        Mail_IDC_Working_Form
        Application.Cursor = xlDefault
        Unload Me
        'new
       Application.DisplayAlerts = False
        ThisWorkbook.Close
       
    End If
End Sub

This form is looking great.  I tested this pretty extensive today.  The validations work perfect.  The form does not close upon submission, l but I believe I can tweak that out.  

Now, what I ran into during my test is that if a user has a few hundred/thousand items, what are the options in this type of scenario?  Is it a possibility to allow the user to copy and paste from one workbook to the IDC Working Tab columns?  Would the worksheet still have all of the validations apply?  Or is it possible to have a transpose option where they user can highlight the data they need and a button will move the highlighted/selected data from the users workbook to the IDC Working Tab?  This has been an extremely dynamic case, and for that I do apologize!

1. When you say "The form does not close upon submission", is that after the bolded lines in post 38759155 are added?

2. "Is it a possibility to allow the user to copy and paste from one workbook to the IDC Working Tab columns?" Sure, they can do that with a simple copy/paste if the columns are in the same order. I can also envision an 'Import' button which could semi-automatically (source WB, rows, etc might have to be specified) copy and paste the data. After the data is pasted it could be directly submitted, and while it's up to you, I would validate it unless you are 100% certain it's always going to be valid. If we did validate the imported data and we find a line that's not valid we could extract the data from the IDC row and place it in fmSingle for correction. No need to apologize for the "dynamic case", but I'm assuming that this would be done via a new question.

3. In my current code I've attempted the center fmSingle over the visible rows. It seems not to work sometimes. What has been your experience?

1.) Yes sir.  I simply copied and pasted your code into the module with no luck.  I manually typed the code into the module, and again no success.  I tried changing ThisWorkbook.Close to ActiveWorkbook.Close but had no luck.  I'm beginning to think I am bad luck for my own project.  :(  Haha  

2.) I considered a simple copy/paste but the no two workbook are ever alike.  I have attempted to standardize how data is submitted to that department, hence this project.  This is one step of many in my pursuit of data security/integrity.  You share my vision with the 'Import' button.  As I stated, each workbook would differ so columns/rows would have to be specified.  Validation is a must as this data is coming from a very low tech savvy sales force.  Marty, I simply cannot stress how gracious I am for your help and support here!!

3.) I have had similar experiences with the userform populating in different coordinates. It seldom covers the visible rows consecutively.  

Would you rather we continue work on the Close event and Userform population with this question and wait for me to ask concerning the new import procedure? Or should I close this out and combine all in a new question?  Thanks Marty!!!

1. When Submit is clicked do you want Excel to close or just this particular workbook?

2. Will the columns in the source workbooks be the same, in other words will column A always contain a description (no matter the column heading), and column B always contain a vendor name (no matter the column heading), etc?

3. I believe I've fixed the centering of the form.

Let's work on the close for now.

BTW I just tested the Submit button and when this code is executed the WB, closes but Excel stays open.

Private Sub cmdSubmit_Click()
    
    ' If the user hits 'Enter Another Item' but then realizes they do not have anymore items, they
    ' need a way out. This allows the form to be mailed if everything is blank on the form but
    ' there has been previous input as indicated by the presence one or more rows in the IDC Working Form.
    If Sheets("IDC Working Form").Range("A" & Rows.Count).End(xlUp).Row > 0 And _
       txtDescription.Text = "" And _
       cboKit.ListIndex = -1 And _
       cboVendorName.ListIndex = -1 And _
       txtADS.Text = "" And _
       txtMSRP.Text = "" And _
       txtVendorPN.Text = "" And _
       txtNSN = "" And _
       cboCountry.ListIndex = -1 And _
       cboBerry.ListIndex = -1 And _
       txtManufacturerPN.Text = "" Then
        Application.Cursor = xlWait
        Mail_IDC_Working_Form
        Application.Cursor = xlDefault
        Unload Me
        'new
        Application.DisplayAlerts = False
        ThisWorkbook.Close
        
        Exit Sub
    End If
    
    If ValidateData Then
        UploadSingle
        Application.Cursor = xlWait
        Mail_IDC_Working_Form
        Application.Cursor = xlDefault
        Unload Me
        'new
        Application.DisplayAlerts = False
        ThisWorkbook.Close
        
    End If
End Sub

Select allOpen in new window

Epiphanies happen:)

Assuming that you wanted to completely quit excel after the submit, I added the 3rd line below (in both places).


        Application.DisplayAlerts = False
        ThisWorkbook.Close
        Application.Quit

When I did that I found that it was no different than

        Application.DisplayAlerts = False
        ThisWorkbook.Close

I then searched a million websites for an answer to what I was doing wrong and/or why Application.Quit wasn't working. Everything I saw said that it should work. It then I dawned on me that the Application.Quit is in the workbook code following the ThisWorkbook.Close line which closed the workbook so the Application.Quit line never gets a chance to run!

Change the code to this and Excel and the workbook will close.

        Application.DisplayAlerts = False
        Application.Quit

Please don't take this as me trying to rush you but I've obviously become very interested in this project and I'm wondering if everything so far is working and also when we can discuss the 'Import' function.

I am totally with you Marty.  My apologies for the delay.  I had a medical emergency and am just now able to get back online.  I hope you have not lost interest with the project.  Everything thus far works great.  I should have caught that Application.Quit issue.  Now I know.  

To answer your questions in thread ID: 38761143:

1.) It would be best to have only the workbook close in case the user has multiple workbooks open.  Unless we have this workbook open in its own instance of excel...?

2.) The source workbooks will almost never be the same since they will be coming from multiple companies all over the country.

It would be best to have only the workbook close in case the user has multiple workbooks open

Good point!

Given what you say about the various formats of the inputting companies spreadsheets I can't think of any way to standardize the import. If you have any ideas let me know, but you probably should go ahead and ask a new question and see if there's someone clever out there who can think of a way.

I've attached a new version. In that version you'll find code in cmdSubmit_Click() that uses the Environ function to get the user name of the person using the workbook. Right now you'll see in two places that it uses "Martin Liss" and "Chris". I assume that your username is not Chris so the way the code is now it will close the workbook. If you change "Chris" to whatever your username is it will close the workbook. I did that for testing purposes so that I didn't need to be always opening the workbook after a test.
New-Columns-Upload-Jan-10.xlsm

Could it be possible to highlight a column/continuous set of data and then hit an 'import' button? This process would transpose the data that is on the source workbook and move to the cursor on the target workbook.  Maybe it isn't possible but I thought I'd ask.  Basically, could we base the import criteria on what is selected/highlighted?  If so, I could instruct the users of that functionality.  Also, if they chose not to 'Import' but rather decided to manually Copy and Paste, could we still have validation on what is entered?  Thanks Marty.  Again, I apologize for being away for so long before.

As far as highlighting the data, I think we could do that, but are the columns in the same order as yours? A copy/paste manually directly to the sheet is problematic because of how and when to validate it, but a copy/paste via code is feasible depending on my column order question. BTW are you are you aware that users can enter data directly into the IDC sheet now?

Yes sir, I am aware they can.  With this being the case?  Is it possible to apply validation to the IDC Sheet on Mail Module now?

Yes we can. Is that what you want to do? If you want we could eliminate fmSingle altogether and just validate the data on the sheet?

BTW, who see's this workbook? All your customers or just you?

This is an internal form that will be used by all of our Sales Members in order to request that new items be added to our Oracle database.  Once submitted, the form will be validated and uploaded by our Parts Department.  I am a simple, sub-par, developer.  As I am certain you have already realized.  

- With the removal of 'fmSingle', we are back at square one.  I totally embrace the concept.  We do lose some of the 'Wow' factor obtained with the user-form automation but my utmost concern is data integrity.  

- I have tinkered around with a few other ideas as well.  While they may be far fetched, I figured I'd throw them your way for your thoughts.  Your knowledge and wisdom are highly revered and I have leaned heavily on you thus far.  

1.) First thought: Have this user form available as you suggested but instead of the form sending an email when the 'submit' button is clicked, the data will simply dump to a centralized location at the first empty row.  An email can still be sent with a "requisition number" to both parties for tracking.  I would actually consider combining every possible need into this. That way, it would be a central location to send requests to the Parts/Database team. Basically, it would be a rudimentary help-desk or ticketing system. I have researched and found that it is possible to dump to a closed workbook with ADO. But am not sure if this is a practical approach.  

2.) Second thought:  We take what we have and do as you suggested.  No fmSingle and validate the IDC Form.  Copy and paste would be allowed and the validation occurs as it does now.  When a new item is added or when the submit button is clicked.  The difference is that the comboboxes would have "rowsources" from closed workbooks as well.  I have some Batch scripts that run nightly and export Oracle discoverer reports to a specified location.  Same name, same file, same workbook output.  The form would simply have a dynamic source from which to pull things such as the Vendor name, Vendor Id, Buyer Name, Buyer Id.  Also, when the Add New Item or Submit Button is clicked, another validation would be to verify if any of the requested items are already on the exported file.  If so, then it would error out and provide message.  I don't know if this would be slow or even feasible with excel but, unfortunately, I have Microsoft Office as my only resource.  

So, these are a couple of ideas pertaining to what we have.  Sorry for the lack of brevity.  I hope you don't simply say, "What is this guy thinking!!!!?"  I am certain that with your expertise and wisdom, we can definitely come to a logical and practical solution.  I am actually hoping that we can continue working together because I have thoroughly enjoyed it thus far.  What do you think is the best route to take?  Thanks Marty!

Wow! I didn't realize how long that last reply was until I posted it.  Sorry for the short novel! Haha

I am a simple, sub-par, developer

Don't put yourself down. You may not have a lot of programming experience, but has anyone else had the drive to do this for your company?

1. I wouldn't know how to do that.

2. I'm not sure I understand the proposal and my skill is in VB6 and VBA so when you begin to talk about rowsources, etc we're out of my area of expertise and maybe you should put the question to the EE community. When i talked about inputting the data directly into the sheet, I may not have been thinking clearly because I was thinking that the values for such things as Country of Origin would be entered manually, letter by letter, or by cut and paste and when 'Submitted' they could be validated against the data in the hidden sheets, rather than the user being able to make a choice from a combobox on the form that's loaded with the data from the hidden sheet.

So basically I'm not sure where to go from here.

You are correct, nobody has had the drive or even the has even taken the initiative to find a solution.  So I am working with years of inefficiencies and horrible processes.

1.)  I can work with the row source later.  No biggie.
2.) This is basically the same as the first but only pulls data from a closed workbook source versus pushing data into the same.

We are currently using something as you have stated.  The unhidden form has data validation lists but also enables the user to copy and paste.  This creates a lot of data issues as well as unneeded characters.  I have provided the current spreadsheets used in the process we are using now.  Basically, the user completes the form and manually emails to the Parts/Database department, then the Parts/Database department run the macro on the second spreadsheet.  It's a very inefficient process.  Take a look and let me know if this is something that we could modify or not? Thanks Marty.
New-Item-Upload-Form.xlsx
Macro---New-Item-Upload.xlsm

I'm not following you, sorry. Please describe what happens using the names of the two workbooks (or to simplify you could call New-Item-Upload-Form WB1 and the other, WB2)  and some details of how data is pasted into the workbook. By row? By column? By cell? Also please give me more information about

This creates a lot of data issues as well as unneeded characters.

My apologies Marty.  I can agree, I was quite ambiguous in my detail.  So this is what happens:

User quotes their customer based on a price list we have received from one of our Suppliers/Vendors.  If one of the items that is quoted is not already in our Oracle database, then the 'WB1' is needed.  The user then takes the form and fills it out according to the workbook structure (they are not allowed to change this).  Once they have completed what they need, they then email the completed form to the Parts/Database team.  Once the Part/Database team has received the form, they open the file 'WB2'. They then reactivate 'WB1' that the User emailed, they go to View in the Excel toolbar, and then runs the Macro that is contained IN 'WB2'.  Once they macro has completed running, the Parts/Database team member uploads into our Oracle database.  

I hope this makes sense.  I know it is strange that the two forms were not simply combined.  I chose to keep them separate so the File that contained the code/macro remained static instead of being touched by so many users outside of the Parts/Database team.  On the hidden tabs of 'WB1' you can see how I attempted to use excel functions/formulas to have the Sales user's data manipulated for use by the Parts'Database team (ie: Vendor Name to Vendor ID, Buyer Name to Buyer ID, Category Name to Category ID).  Data is entered based on columns for the most part.  User's simply have a bulk transfer when they send a request.  Therefore, they simply copy a column of descriptions from their source workbook and then paste into 'WB1', then copy a column of 'Part Numbers' from their source and paste to 'WB1' and so on.  I am sure if the data on their source were to happen to be in the same format as 'WB1' that they would simply select all and paste. But that is an almost impossible scenario with all of the tons of worksheets we receive.

The issue I am having actually arises from the user's input of data into 'WB1'.  Sometimes there are scenarios when they have copied data from a .pdf or other file that contains hidden metadata and paste into 'WB1',  Many times this data is invisible to the Parts/Database team but when the data is uploaded into Oracle, weird characters appear in the database.  That is bad, bad, bad for us.  Especially if the character appears in an inventory item since these are not editable once in the database.  :(  Thanks for the help Marty!

Your process is clearer now. Do you have a WB1 that contains "hidden metadata"? If so do you know what cell(s) it is in?

I don't have any on hand at the moment.  However, it is normally characters such as 'Hard Returns' and/or ASC characters.

The cells could be examined to look for and remove specific ASCII characters or to remove everthing except a list of valid characters.

I have been using that already in the code on 'Wb2'

If that's the case then what's the problem? Are some characters getting through the "Item_Import" code that shouldn't? And as an aside, I assume that most of the characters you are looking for are invisible, so why are you replacing them with a space rather than null?

I've included the space just in case the characters exist between words. This way I dont have one long text string.  The intent was to have validation on the front end rather than having the Parts Team run it every time.  I aimed to create a userform that would do the same as the macro but in real time.  So, when the user is entering incorrect data, it will kick back an error (like the userform you created).  Then, when it is mailed, it will be sent to the Parts Team just like the file does now with the userform.

I guess the question is, can we just take the userform and make the IDC Form worksheet act as such.  Would it be safe to create commandbuttons on the worksheet itself?

You answered my aside but I don't think you answered my main question which was "Are some characters getting through the "Item_Import" code that shouldn't"? In other words why aren't you happy with what you have now?

As for putting ActiveX controls on a form, I've never had problems doing it, and while other people have I've seen references to code that prevents the problems. But before we go there please help me understand what's wrong with the current process.

The current process is a slow and quite cumbersome.  There are two locations for the sales user to request items to be uploaded.  For quotes needing up to a qty of 10 items created, they use a quoting system which is quite slow and almost impossible to have modified.  For quotes that need 10 items or more created, they use the New Item Upload Form.  The problem lies in the fact that one member may forget to run the code on the Item Import or they may try to combine forms two forms.  One possibly could have had the macro ran against it and the other not, which leaves invalid characters.  

Yes, some characters manage to get through.  For instance, an * slipped through today.  Also, some invisible characters slipped through which, although not able to seen on the spreadsheet, they populated into the Oracle database as an upside question mark.  Would it be more practical to have the validation somewhat automated at data entry versus having it manually ran by an individual team member?  I kind of envisioned it as much safer with automation.  I trust code more than human I guess.  When it comes to uniformity and constraints I suppose.  What do you think is the best route to take?

I'm not an expert in this area but here's what I'd do.

1. I'd combine the two workbooks into one and then protect the code in the WB2 part by protecting the code in a similar fashion to how you hide the sheets. I believe it's done through VBAProject properties but I'm not in front of my PC so I can't check.

2. I would then change the validation to allow only a range of characters and/or certain characters. I would think that it would be pretty easy to define the valid set (all upper and lower case letters, all numbers, all punctuation characters, space and maybe a few more) and even if initially a few characters were removed that shouldn't have been, that's a less severe error than including * and such.

Think about the above and poke holes in it.

Okay.  That sounds great.  So basically, the combined version would act as the latest version with the userform, except without a userform.  The error handling will all be the same and validation will act with highlights, correct?  Thanks

Yes, with two additions
1. The input would also be checked for my "valid set" and everything else removed. BTW did I miss anything in that set? Also would you like any kind of notification if invalid characters were found?
2. the addition of a "Submit" button on what was "WB1"

As you know, the validation in fmSingle validation is done field by field, so if a mistake is found in the first field an error pops up and then the second field is looked at, etc. But that's for just one record and if there were 20 errors on the sheet and the user was notified one by one it would be annoying. So the validation in the sheet should be done differently, take your choice or modify one of them.

1. All the fields validated at once, each problem field yellowed, and a userform popup showing a list of cell addresses and an error message for each cell that's incorrect

2. All the fields validated at once, each problem field yellowed, and a comment popup attached to each cell that's incorrect that contains the error message

3. Similar to either 1 or 2 but row by row.

Great idea Marty!  One by one would be extremely annoying.  I like the sound of all fields being validated at once.  Both options sound great and quite user friendly.  Option 1 sounds pretty nice, but if the user views the message, then attempts to fix the errors but forgets one, the form would simply repopulate.  However, Option 2 pinpoints the error and the user could zero in and correct.  I think having a comment popup on each cell with the error message is the best way to go here. Then, the user can correct any issues then submit.  Once submitted, they could get a userform populate which states a specified message.  They hit okay and it closes out the form plus the workbook. Thanks again Marty!

With you and I scratching our heads with this one, a few more concepts popped into my head for other projects concerning excel.  I am going to post another question or two and wondered if you would like to have first grabs?  One will be concerning SQL and/or ADO but I remember when I mentioned one of the questions with these previously, you stated that you were not well versed and to post for others to help.  But I wanted to ask again. Thanks my Friend!

I think having a comment popup on each cell with the error message is the best way to go here. Then, the user can correct any issues then submit.  Once submitted, they could get a userform populate which states a specified message.

I think so too but I don't understand the part of your comment I bolded. What I'm proposing is that if they, say, put 123456789 in NSN that the field would be yellowed, and a comment with "value must be exactly 13 characters in length or a 16 character string like 1111-22-333-4444" would be added. Once corrected and the Submit button pressed again, that comment and the yellowing would disappear.

Post the SQL/ADO question so anyone can answer it.

Oh and please verify you understand what I'm prosing using my "valid set" and let me know if it's complete.

My understanding is that your valid set would be the code identifying the only acceptable characters for use, correct? You have is stated as this:
" 0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZ()"

To add everything that is acceptable, it would vary.  Part Numbers can only contain:
" 0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZ()-_"
While Descriptions can contain:
" 0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZ()-_+$:;\/<>&"

Concerning the quote:

I think having a comment popup on each cell with the error message is the best way to go here. Then, the user can correct any issues then submit.  Once submitted, they could get a userform populate which states a specified message.

I was referring to the userform populating after all errors have been corrected and the form has been submitted to the Parts Department. I figured having a Userform populate with a message such as "Submission Complete" or something similar would be their confirmation that it was sent.  Then the form would close automatically as we did before.  Your thoughts?

Okay, for the valid set we were talking about two different things. Let me put it this way.

I'm going to look at every field on the sheet and if any field contains any character that's not in the valid set (which will be " 0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZ()-_+$:;\/<>&") the invalid character will be removed. Then I'll do the specific validation for each field. For example if the Description is

This is a ***very*** important customer

My scan using the valid set will silently remove the stars and result in the comment being

This is a very important customer

Then my specific validation for the description will find that it's okay.

I don't think we need the userform. If you are referring to populating something that looks like the old fmMultiple,it would have to be modified to show more lines if there were, say, 20 lines in the sheet. I propose just a popup message that says "Submission complete" since the sheet shows them what they've submitted.

On another subject. In your recent New-Item-Upload-Form.xlsx there are comments(?) like "National Stock Number must be…". How is that done?

Another example. If one of the cost fields contains

$1.23

the valid set check will find that all the characters are okay, but the specific validation for the cost filed will say it's an error because of the dollar sign.

Oh and what is the "xfit" in your name?

I see what you mean now. I had the general idea but did not think about the specific validation.  That is incredible!  

I wasn't referring to populating a user-form like fmMultiple.  I only wanted a small little message box to pop up stating that the form was successfully submitted.  I was not thinking when I wrote that.  That does not require a user-form, it could be a simple message box.  I am still on board with using the worksheet and applying the validations there instead of the user-form as before.  You have some great ideas.  I like the way your logic applies to this.  I am learning a lot!

The "xfit" refers to Cross Fit.  It is a workout program/gym that I attended a year ago before I had all kinds of cardio issues.  But I like the username so I continue to use it.  Thanks for all of your help and overall "mentor-ship" Marty.  I am sincerely grateful for all you have done.

So I assume that was your "medical emergency". I hope you're okay and have a good prognoses.

The only thing you didn't answer was about the comment-like help messages on the cells in the  recently updated form. And I forgot to ask, do I need to do anything with the Kit-BOM sheet?

Ahh, I see that the "comments" are done via data validation.

Correct, but that slows the sheet quite a bit.  Also, when the request is mailed, can it only send the 'IDC Working Form'? I would rather use your comments with validation versus what I have done via data validation.

The Kit BOM sheet was included for our Kit Manager.  If a user wanted to submit a Kit with its BOM, this allowed the item to be specified and sent to the Kit Manager as well as the Parts Department.  But that was only included if the Kit Item "Y" was checked.

Thank you for the concern with my med issue.  Yes, that was my emergency.  I ran out of medicine and had to get that squared away.  Sunday was my last dose but I figured that it would arrive in the mail on Monday and I would be okay.  It did not show until Thursday!  It was my stupidity for relying on the USPS and The Veteran's Affairs.  All of my medical is free since I am a disabled veteran.  But sometimes it is not the most efficient.  But, thankfully, I have my meds now and am back on track.  Thank you again my Friend!

I'm glad you're feeling better. From what I understand the VA is a very poorly run organization.

So here's what I will do. Please tell me if it's correct and/or desired.

That sounds perfect.  

I do have a question, however.  Would it require a separate question to add functionality to the Kit-BOM Form?  Could we possibly integrate the overall function of the Kit-BOM Sheet into the IDC workbook? What I was thinking was maybe have something trigger when a user selectS 'Y' if an item is a Kit.  If 'Y' was selected, could we possibly have a process that will identify the Kit Item and the related BOM. Then, in this scenario, it would send an email to both the Kit Manager and the Parts Department?

What are your thoughts?  I am thinking that a userform could populate that would require the user to select the line numbers/row numbers that contain the Part Numbers that make up the BOM for that selected kit.  This would also require the quantity of each and such....Am I totally digressing here?  Haha  Thanks Marty!

I'd rather put that off for later if that's okay.

That's perfectly fine with me Friend.  I just wanted to get your opinion concerning my concept.  Thanks

Here's a first cut at the new design. All it does is validate the data.

Note the picture on the right of the data on the sheet. That's the Submit button:) If you have good eyesight you'll be able to see that the address on the envelope is "Parts Dept".

I changed cell format of Description, Vendor Part Number, NSN and Manufacturer Part Number to Text from General. I did that because if for example a vendor part number were 123456789111, Genera format would change it to Scientific notaion (1.23457 × 10⁹). Excel 2010 however wants you to know it's a number and it does that by putting a green triangle in the corner of the cell. Is that a problem?

Fields like ADSCost are formatted on the sheet like $      1.00. Would you rather have "$1.00"?
No-userform-Jan-13.xlsm

The ADS Cost fields are set as accounting values instead of currency. I am okay with changing those.  Thank you for changing over the other values from general to text.  That will alleviate a lot of potential issues down the road. I am taking a look at the file now.  Thanks buddy

I think the picture is pretty neat.  How did you design that?  Haha.  I have a couple of questions:

1.) One question I have is about the size of this file. 2.0MB is pretty large and I'm certain is a result of the manual excel formulas I have on the 2nd tab ('IDC Working Form').  Is it possible to have the columns hidden on the first tab and simply have it auto fill based on the values inserted into the related columns? (IE: Vendor Name inserted into column B and the appropriate Vendor ID, Buyer Name, and Buyer ID is auto filled into those hidden columns.)  Would it be more practical to leave the 'IDC Working Form' tab as a mirror image to what the user has placed on the first tab 'New Item Upload Form' and simply have the appropriate Vendor ID, Buyer Name/ID populate on the 'IDC Working Form'? Or would it be a better approach to only have those values auto fill upon submission of the form to the Parts Department?  For example, user fills out first tab 'New Item Upload Form', cleans up all validation errors, then submits to the Parts Department.  When the form finally submits, the code auto fills the correct Vendor ID, Buyer Name/ID on the 'IDC Working Form' bound for the Parts Team. What are your thoughts?

2.) A second thought is concerning the submit and/or cancel options.  While the picture looks great, would a "floating" user-form or having the ActiveX controls in a header or something be more accessible?  I have provided a worksheet with a trial user-form.  I am not sure it would be as visible all the way on the right side of the form.  Thanks again for the help Marty!
No-userform-Jan-13--1-.xlsm

The picture/button is added via Insert|Picture

1. On my system the file size is 489KB so we can still talk about the things you mentioned following that if you like. Let me know.

2. I wasn't considering a cancel option because the user can just delete the rows. Why do you say the submit button isn't as accessible as you'd like? Is it too far right?

Yes sir.  It is far right.  I wanted to make it very noticeable.  I reduced the size of the file by deleting all the rows after row 20 on 'IDC Working Form'. This reduced the size on my system to 498KB.

What about this where the first row is frozen?

That works for me.  Looks great when.  I like the fact that it is frozen in the top row and noticeable as soon as the page opens.  Thank you Marty!

How about this? The "New Item Upload" is a label. A label in VB6 would not be editable by the user, but this forms label seems to be. Can we change that??? Also strictly speaking it should probably be "New Items Upload". What do you think? Note: It looks better "in person".

Wow! That is impressive Marty. I really like this!! Perfect approach here.  I'm telling you, I am learning so much from you. Thank you, Thank you, Thank you my friend!

I'm learning as I go too. An old dog can learn new tricks:)   I'm 71:(

Wow, I am impressed! Retired I presume? Consultant? Pro Bono scripting counselor for a sub-standard newbie. ha-ha  Well, I'm 32. I should be much more advanced at this but I spent 10 years in the Army.  While I had a lot of adventures, I didn't gain any ground in the business world.  So here I am, an ambitious junior trying to reach the top someday.  Someday sooner rather than later.

Yes, very happily retired? I was a programmer for 40 years:) Stick with it and you'll do fine. Where did you serve?

A technical question for you which I can't figure out right now. Let's say someone chooses 'Albania' as the country of origin. In that case the listindex will be 1. How do I refer in code using a range or cells value to the second column of that combobox so that it returns "AL"?

I'd try an Index or some sort of lookup function.  Perhaps, an applications.worksheet vlookup.   I'm not the best at Evaluate in vba.

Evaluate("=lookup(" & Range.Value & "," & _
            Lookup1.Address & "," & Lookup2.Address & ")")

Select allOpen in new window

Am I waaay off here?

No idea, but I figured out how to do it in a very different way. Sorry I didn't let you know sooner.

This is the valid string we decided on

" 0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZ()-_+$:;\/<>&"

It looks like it needs to have ' added since one of the vendor names is

180'S INNOVATIVE PERFORMANCE WEAR

Please let me know if you agree as soon as you can.

Ahhhh, agreed.  Sorry I did not catch that.  Thanks for the keen eye my friend!

I am on the East Coast so, depending on your location, there may be a lag in our respond times any ways.  Whereabouts do you reside?  Nothing specific obviously.  Haha.  East Coast, West Coast, Central Plains, etc?

San Jose, CA.

My approach is to populate and expand the data from the New Item Upload form to the IDC Working form and while I do that I will remove all the characters that aren't in the valid set. In looking at the 'Macro' workbook I see that some other things are done as well including formatting the output sheet. I assume that I don't have to do that. True?

I'd like to go through the data validation code that I show below (in an abbreviated and broken up fashion) with you and I've got a few of questions.

1. What does this do?
Sheets("IDC Working Form").Cells.Select
    ActiveSheet.Range("$A:$T").RemoveDuplicates Columns:=4, Header:= _
        xlYes
------------------------------------------------------------------------------
2. Do I need these? Note that the 3rd one would change the ' I mentioned to FOOT
Sheets("IDC Working Form").Cells.Select
    Selection.Replace What:=",", Replacement:=";", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="""", Replacement:=" INCH", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="'", Replacement:=" FOOT", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
------------------------------------------------------------------------------
3. This one and similar look like they are trying to reduce a string of blanks to a single blank. Is that needed?
    Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
------------------------------------------------------------------------------
4. This one seems to remove ™ (trademark). Do I need to do that?
    Selection.Replace What:="™", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
------------------------------------------------------------------------------
     Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
------------------------------------------------------------------------------
5. This one doesn't seem to be a replacement. What does it do?
    Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
------------------------------------------------------------------------------
6. What does this do?
    Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
------------------------------------------------------------------------------
    Selection.Replace What:="" & Chr(2) & "", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="" & Chr(3) & "", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Similar code removed
    Selection.Replace What:="" & Chr(254) & "", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="" & Chr(255) & "", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

We also missed ','

I also assume that the lower case letters in the vendor names like "APEX RESOURCE TECH, INC dba/ INANYCASE.COM" are OK.

And we missed "!"

I have provided answers to your questions below.  Also, attached in a Word Doc.  I cannot believe we overlooked the "!".  Simply oversight. The lower case are okay.  If we could "UPPER" them, that would make everything on the sheet uniform.  Your thoughts?



1. What does this do?
Sheets("IDC Working Form").Cells.Select
    ActiveSheet.Range("$A:$T").RemoveDuplicates Columns:=4, Header:= _
        xlYes
A.)  This removes any duplicate part numbers on the IDC Working Form.
------------------------------------------------------------------------------
2. Do I need these? Note that the 3rd one would change the ' I mentioned to FOOT
Sheets("IDC Working Form").Cells.Select
    Selection.Replace What:=",", Replacement:=";", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="""", Replacement:=" INCH", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="'", Replacement:=" FOOT", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=Falses
A.)  Since you have created a better validation approach, I don’t think we need these.
------------------------------------------------------------------------------
3. This one and similar look like they are trying to reduce a string of blanks to a single blank. Is that needed?
    Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
A.)  Mulitple “spaces” or “blanks” were a problem before.  I think your validation should address this though, correct?
------------------------------------------------------------------------------
4. This one seems to remove ™ (trademark). Do I need to do that?
    Selection.Replace What:="™", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
------------------------------------------------------------------------------
     Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
A.)  Most definitely!  These special characters are not recognized by our Oracle database.
------------------------------------------------------------------------------
5. This one doesn't seem to be a replacement. What does it do?
    Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
A.)  Actually, this was an incomplete portion on my end.  It was the same as finding any “ and adding INCH but of course should have added FOOT.
------------------------------------------------------------------------------
6. What does this do?
    Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
-----------------------------------------------------------------------------
    Selection.Replace What:="" & Chr(2) & "", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="" & Chr(3) & "", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
A.)  I’m not quite certain here?  It can be removed.

Similar code removed
    Selection.Replace What:="" & Chr(254) & "", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="" & Chr(255) & "", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
A.)  This was addressing special characters by their corresponding ASCII codes.
Questions.docx

1. OK I'll include that code
2. Will not include
3. No actually it doesn't. I will add code to reduce any string of spaces to a single space.
4. This is interesting. In the code the symbol shows up in uppercase letters. I'll add the code and see if I can test it.
5 & 6. Understood

You didn't answer this question of mine.

In looking at the 'Macro' workbook I see that some other things are done as well including formatting the output sheet. I assume that I don't have to do that. True?

No sir.  I just assumed it would look like the one we had before.  That it would send that same sheet and its format.

Oh, about the lowercase letters. I'd prefer to leave them alone unless you object. However it's no big deal if you want me to uppercase them.

I'm okay with lowercase.  I guess sometimes the military still comes out in me.

BTW, I was stationed at Fort Benning, GA for the biggest part of my career.  I was a Ranger, a unit within the Special Operations Community.  Blackhawk Down is a movie about us.  Maybe you've seen it?  Anyways, I broke my back in a parachuting accident and had to take a desk job as a Recruiter in Virginia Beach, VA. Big change!  So I took a medical retirement I guess you could say. Now on career number two.

WOW!!! Thank you for your service my friend!

Check this out.
No-userform-Jan-14.xlsm

Thank you for that Marty.  I appreciate it.

I formatted a few things on the form Marty. I have listed below:

1.) Consolidated all tabs with "ID's" and placed them on tab named "Form Codes."

2.) Formatted the picture and header row.  

3.) Formatted the 'IDC Working Form' by disallowing grid lines to be seen and placing grey lines for used columns.

Also, I have a couple of questions.  I tested the form and it isn't validating.  It sent with blank rows and incorrect values.  

Is it possible to have the drop down list of values we currently have to perform like a combo box or list box?  IE: First letter of word brings you closer or scrolling with the mouse.

Thanks again for all the effort and help here my friend.

Can you attach your version please?

Also, I have a couple of questions.  I tested the form and it isn't validating.  It sent with blank rows and incorrect values

That's hard to believe. I ran dozens of tests for one reason or another and the validation always happens. Did you possible use the wrong WB?

Also, I have a couple of questions.  I tested the form and it isn't validating.  It sent with blank rows and incorrect values

I, currently at least, don't know how to do that? I couldn't even find a way to have the list auto-dropdown as it did in fmSingle but I'll see what I can do.l

Oops, sorry.  I meant to attach.  Forgive me
January-14-No-User-form--Chris-.xlsm

I entered just a description in the first row of the sheet of the New Item Upload Form and pressed the submit button and all the rest of the cells in that line except for NSN showed errors, so the validation is working. Did you do something different?

There's a problem however with your WB. When I went to try to correct the errors by selecting an entry from several of the combos, there were no entries in the list. I then selected the country of origin column and went to the data validation and it told me that there was more than one kind of validation. AFAIK it should tell me that the column contains some cells that don't have data validation (the headings) and ask me if I want to extend the validation and I say 'no'. I then selected the first cell in the data for that column as shown by the green arrow and when I went to data validation it shows the the error at the red arrow. It looks like either the source sheets are missing or the ranges are changed.

BTW the Vendor Name combo is working properly.

This is because I deleted the other tabs that had the row source.  I can correct this.

What about your "I tested the form and it isn't validating" comment?

You should probably have these two icons. email.png is the original and IDC Email.png is my modification.
email.png
IDC-Email.png

I downloaded your newest sheet again and it worked.  My guess is that I must have had an earlier version.  The newest form worked as planned.  I am having issues when I attempt to open the submitted attachment on the email. I found that if I already have an instance of excel open, then it causes excel to run into errors and then close.  It opens if excel is closed but very slow.  It's not a very large file at all either...? (105 KB) What are your thoughts?

What version of Excel do you have? I use 2010 and I've opened the attachment several time without incident so I can't say what's going on. Try rebooting and see if it clears up.

Send me a version with your modified headings ([cry] I really liked mine[/cry]) because there's a tiny modification (to protect the code from your users) that I need to make.

In one of the posts above (we have about 90!) you said

I tested the form and it isn't validating.  It sent with blank rows and incorrect values.

 
Is that still the case?

You also said

Is it possible to have the drop down list of values we currently have to perform like a combo box or list box?  IE: First letter of word brings you closer or scrolling with the mouse.

Perhaps someone cleverer than I can find a way but unfortunately it doesn't seem possible using the forms comboboxes. It would be possible with ActiveX comboboxes like we used on fmSingle but there are issues with those on a sheet.

The form validation cleared up.  I believe I had an earlier version of the sheet (before you touched it up). It does not send with blank rows or incorrect values now.

Okay, I can search around and see if I can find a solution to this.  Thanks Marty!
January-15-No-User-form--Chris-.xlsm
IDC-Working-Form-15-Jan-13-8-53.xlsx

Now that we coming to a conclusion with this portion, you think you're up for the task of handling the Kit-BOM portion?

Comment ID: 38770854

When you've verified that everything is 100% okay with the current workbook and you send me the corrected workbook that contains your headings, I'd be happy to work on the Kit-Bom as another question.

Another thing to consider. It looks to me like what happens now is that after one of your users Submits the data, that they get an email that they are supposed to send to 'parts'. I believe we could eliminate that last step and have the email sent automatically.

Yes sir.  That last step really isn't necessary.  It is a simple change of .Display to .Send in the Mail_Module, correct?  Also, did you get the file with the headings?  Thanks

That last step really isn't necessary.  It is a simple change of .Display to .Send in the Mail_Module, correct?  

I assume so.

Also, did you get the file with the headings?

I misse the fact that you had posted it but I have it now.

Here's the file updated with the tiny modification. It's actually not any change to the code or sheet, I just protected the code. You may know how to do that but here are the steps just in case.

1.    Go to Visual Basic
2.    Choose the Tools|Visual Basic Properties menu item
3.    Select the 'Protection' tab
4.    Check 'Lock project for viewing'
5.    Enter your desired password (case sensitive)
6.    Close and reopen the WB

I set the password to 'Marty'.

Different subject; do you understand about the "Environ" codeing I talked about a while back?
January-15-Protected.xlsm

No sir, not so much.  I tried to Google it for a better grasp.  I will dig a little deeper tonight.  Sorry about that.

In the code below you'll see my Windows user name (Martin Liss) and a second user name "Chris". If you change "Chris" to your real user name what will happen is that after you click submit the workbook won't close. As it is now I assume the workbook closes after a submit. I did that to make tersting easier.

Sub Submit()

    UCaseDescription
    
    If ValidateData Then
        CleanUP
        TransferToIDC
        Application.Cursor = xlWait
        Mail_IDC_Sheet
        Application.Cursor = xlDefault
        MsgBox "Submission Successful"

        Select Case Environ("USERNAME")
            Case "Martin Liss", "Chris"
                ' Don't close
            Case Else
                Application.DisplayAlerts = False
                ThisWorkbook.Close
        End Select
        Exit Sub
    End If
    
    
End Sub

Select allOpen in new window

Ahhhh. I see.  That makes sense.  So it identifies the user and responds as told.  For you and I, it doess not close, for others, it will.  That makes sense! Nice

Hello my friend.  Good morning to you My San Jose friend (whom I envy slightly more just by typing 'San Jose').  I hope all is well.  I just wanted to alert you that I am encountering an error when the submit button is clicked.  It states:

Run-time '1004':

Method 'Copy' of object'_Worksheet' failed

The code it identifies is below:

Sub Mail_IDC_Sheet()
'Working in 2000-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim SourceWb As Workbook
Dim SourceWs As Worksheet
Dim DestWb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Set SourceWb = ActiveWorkbook
    Set SourceWs = SourceWb.Sheets("IDC Working Form")
    ActiveWorkbook.Unprotect
'Copy the sheet to a new workbook
    With SourceWs
        .Copy
    End With

    Set DestWb = ActiveWorkbook

'Determine the Excel version and file extension/format
    With DestWb
        If Val(Application.Version) < 12 Then
            'You use Excel 2000-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2010, we exit the sub when your answer is
            'NO in the security dialog that you only see when you copy
            'an sheet from a xlsm file with macro's disabled.
            If SourceWb.Name = .Name Then
                With Application
                    .ScreenUpdating = True

Select allOpen in new window

And here is the screen shot pic:



I have also attached the workbook I was using just in case you want to see.  I am aware of the incorrect NSN format in G5, I was testing validation. Sorry for the long period of silence.  I have the worst of luck.  Had a collision last night on my way to Grad School.  Roads were wet and I rear-ended someone.  Fortunately, it was minor and there were no injuries.  The officer didn't even give me a citation due to "extremely hazardous driving conditions". I digress.  We are almost there with this project portion!  Yaaaay! Thanks again Marty!
Chris-Version.xlsm

I need to know your code-protection password.

Just in case you missed the above I can't look at the problem without knowing your code-protection password.

I am so sorry Marty.  I just made it home.  Please forgive the careless mistakes lately. I feel horrible for putting you through the ringer and you graciously give your time.  Please forgive me.  I will resend without the password.  My apologies again Marty.  :(
Chris-Version.xlsm

No problem. I haven't looked at the code yet but you mentioned you mentioned NSN. Is there a problem with it?

The workbook you just uploaded is missing the Vendor Name With ID sheet. I can fix that.

I combined all sheets into one.

Okay, good to know:)

No problem with the NSN - I just wanted to point out that in the attached workbook, I was aware of an invalid value in the NSN field.  That was a test value.

I fixed the problem you mentioned in post 38783631. The IDC form has to be visible in order for it to be copied so I added code that makes it visible temporally.

I also found that somehow there was no macro assigned to my submit button. I don't know how that happened but we both need to make sure that the Submit macro is always assigned to it.

An important question for you. The way I have it now, the input data will be deleted from the New Item Upload form once the iDC for is mailed. Is that what you want to have happen?

Yes sir, please.  The data can be cleared but ultimately the form should be closed.  I am still debating on whether to give them the option of saving or simply have a message display that states that their form has been submitted and they can find any needed copies in their Outlook sent folder. They then hit 'OK' thus closing the message box and New Item Upload Form.  What are your thoughts?  

Another idea I'd like to throw your way.  The data validation lists are not the friendliest when it comes to data entry with super long lists to select from.  I have been searching for a way around this and found some code that we can possibly use.  I wanted to run it by you for your thoughts,  Thanks Marty!

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
        
Dim tb As ListObject
Dim lCols As Long
Dim lCol As Long
Dim lRows As Long
Dim lRow As Long
Dim lColStart As Long
Dim lRowStart As Long

On Error Resume Next
Set tb = ActiveCell.ListObject
lCols = tb.ListColumns.Count
lCol = tb.ListColumns(lCols).Range.Column
lRows = tb.ListRows.Count
lRow = tb.ListRows(lRows).Range.Row
lColStart = tb.ListColumns(1).Range.Column
lRowStart = tb.ListRows(1).Range.Row - 1
        
    'Hide combo box and move to next cell on Enter and Tab
    Select Case KeyCode
        Case 9 'tab
            If ActiveCell.Column = lCol Then
                If ActiveCell.Row = lRow Then
                    tb.Resize Range(Cells(lRowStart, lColStart), Cells(lRows + 2, 3))
                End If
                ActiveCell.Offset(1, -(lCol - lCols)).Activate
            Else
                ActiveCell.Offset(0, 1).Activate
            End If
        Case 13 'enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationLists")
Application.EnableEvents = False
Application.ScreenUpdating = False

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
  
  
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    Application.EnableEvents = False
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If


errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

End Sub

Select allOpen in new window

Okay, but remember to copy your data because once it is transferred the the IDC form and mailed, it's gone.

The combobox code looks very interesting but I'd like to wait until we have any and all problems fixed with the current version of the WB before we do any modifications like that.

The attached workbook seems to work. Please look at the ChangeLog module to see the changes I made.

To keep track the workbooks I'd like to suggest that we name them with our name and a date, and if there's more than one on the same date a letter would be added. For example the workbook I've attached is Marty0116 and if there's a second one today it will be Marty0116a.
Marty0116.xlsm

Great Idea.  I will adhere to that format moving forward.

I had the time to look at the combobox code you posted and unfortunately it refers to ActiveX controls. What we have on the sheet are neither ActiveX controls or forms controls but rather just a validation list.

For some reason, I cannot get the last two columns to get populated (CATEGORY ID, CATEGORY SET ID).  Also, the formats in the pricing columns vary, I am going to take a shot at fixing that.  Is this an easy fix?  Thank you so much my friend!
Chris0116.xlsm
IDC-Working-Form-16-Jan-13-21-45.xlsx

Why not let me do the coding and you do the testing?

What format do you want for the prices?

The problem has to do with the change that put all the data on the "Form Codes" sheet. How do I make that sheet visible so I can look at it?

Oh, would it be a massive undertaken for me to substitute the validation lists with the ActiveX controls?  From the language of the web link I found, it appears that they can be used one in the same....?  


http://blog.contextures.com/archives/2010/11/10/combo-box-drop-down-for-excel-worksheet/

http://www.ozgrid.com/forum/showthread.php?t=69918

http://www.contextures.com/xlDataVal11.html

I don't have a password on it so I simply go to REVIEW then Unprotect Workbook

Oh, would it be a massive undertaken for me to substitute the validation lists with the ActiveX controls?

Yes! Consider that every row would have to have it's own comboboxes like fmMultiple did. How would you handle it if the user pasted 100 values into the column?

Is the way the sheet is working now so bad that you want to make such a large change?

Oh, would it be a massive undertaken for me to substitute the validation lists with the ActiveX controls?

That doesn't work for me. MY WB indicates that it's not protected. Did you ever protect the Forms Code sheet?

Okay I figured it out. ADS and MSRP now have the same format and I believe the proper data is going to T, U and V (but check).
Marty0116a.xlsm

Oh, no not at all Marty.  I absolutely love the sheet.  I was only asking that because I wanted to see if I could practice.  I like trying new things like that in order to equip myself for future projects.  I never know when I'll need it and plus I like to stay "smarter" than everyone else on my team, therefore, if I could figure something like that out, I'd be a little further ahead.  Please forgive me my friend.  I did not mean to come across as unappreciative or unimpressed with this workbook.  It is incredible man.  I'm truly sorry!

No problem. I was upset about something at home so I came on a little strong. Is the data in T, U and V correct? If not please tell me which columns in Form Codes I should use for the values.

I'm working on ways to make the finding of the the Vendor Name a little easier. My first try does the following.

If you double-click a vendor name cell, the value in the cell will be set to the first value on the Form Codes sheet that is greater than the value in the cell. For example if the vendor name cell contains 'AMERICAN MEDICAL DEPOT', double-clicking that cell will set it to 'B & B HOSE AND RUBBER CO., INC' and double-clicking again will set it to 'C & S ANTENNAS INC', etc. You can then use the scrollbar to find the one starting with 'A' (or 'B' or 'C') that you want. Unfortunately when you start the cell is blank so double clicking gives you '142ND FIGHTER WING COMMUNITY FOUNDATION' and continuing to double click gives you the first one that starts with a '2', then '3', etc. I could have it so that if blank it will give you 'AMERICAN MEDICAL DEPOT'.

I'm going to also try another approach. Will keep you posted.

That is exciting my friend. The BUYER ID is not populating now.  I did, however, adjust the format for the pricing cells.  Now they are in "0.00" format.  I tried to fix the Buyer ID issue, but could not.  :(
Chris0117.xlsm
IDC-Working-Form-17-Jan-13-16-06.xlsx

Is the data in T, U and V correct? If not please tell me which columns in Form Codes I should use for the values.

You never answered the above but I think I figured it out. BTW double-click the vendor name:)
Marty0117a.xlsm

Here's another way of doing vendor name. Don't use this workbook for anything except to see what happens when you double-click vendor name.
Marty0117Demo.xlsm

These look amazing!  I am quite intrigued how you did these.  I am still going over them but I am impressed!

Thanks. If you have any questions, let me know.

The drop down looks great!  The Category ID and Category Set ID still are not showing.  The Category ID is a 3 digit number (TACTICAL.DEFAULT = 757).  The Category Set ID is a set number.  I made an attempt with the attached.
Chris0117b.xlsm

The Category ID and Category Set ID still are not showing

One of us either uploaded or looked at the wrong WB because I saw it working, but that's minor. The major thing is that one of the articles you pointed out works!!! The version I'm working on has a working typomatic combobox for Vendor ID and I think I should do it for Country as well.

This is absolutely amazing.  Works perfect.  The combo box shows on double click, the emailed/submitted form has all required attributes, and the validation works as designed.  Marty, I simply cannot think you enough for all you have done with this.  I am sincerely grateful for your help and am humbled by your generosity!  

Are you sure you are still up to the task of working with this Kit BOM portion?

Martinliss has been absolutely phenomenal with his support.  He has taught me so much throughout this project and has been exceptionally patient with me.  I have not been the easiest to work with, yet Martin has understood and mentored me to the next level in my understanding of VBA and its capabilities.  Not only has he supported me professionally, but I think I've made a friend as well.

Marty, I want to thank you for all of your help!

Marty, quick question my friend.  How can I prevent this from sending if nothing has been entered.  Currently, it will email if no data has been entered.

Correction attached.

Again thank you for the kind words. It's been a long and winding road but I've had fun and learned a bunch of stuff along the way, so thank you too.

Please describe in as much detail as you can what you need done in relation to the KIT BOM sheet, wait a few hours and then post a new question. Also please return this WB with that sheet attached.
Marty0118.xlsm

I checked the form and realized that it was populating the actual Country Name and not the corresponding ID on the emailed/submitted form.  That was a mistake on my end since I did not alert you to that.  However, I fixed it by adjusting the code "GetTableName.......Value, 6, 7)"  It worked.  Pretty proud of what I have learned thus far.  :)

Glad you were able to do that. Can you update the ChangeLog and annotate the change so they look similar to mine? Here's a link to an article I recently wrote that will help you do that if you want. You can also perhaps be my guinea pig and tell me if everything works.

I'd be honored to be your guinea pig.  In some ways, you were mine with this sheet.  Haha.  So I think I owe you one anyways.

It would not allow me to enter your article.

Experts Exchange Unauthorized Access
Permission Denied
This article is currently still in progress and yet to be approved.

Yeah sorry, I went to the article and changed a typo and that put it back in restricted mode. Unfortunately it will stay that way until it's approved again. I'll let you know when that happens.

I've got some good news for you. When you upload my workbook modified with the addition of the Kit-BOM sheet, I'll also update the code for the New Item Upload sheet so that the comboboxes open anytime you select them. In other words no double-click required! BTW the comboboxes are currently set up to display a maximum of 20 rows. That can be increased or decreased very easily so let me know if you want a different number.

Also, I have a question concerning the code below.  Currently, this will force the text to upper case for the description.  What can I do to have this occur for the Vendor Part Number and Manufacturer Part Number? Thanks

Public Sub UCaseDescription()

    Dim lngLastRow As Long
    Dim lngRow As Long
    
    lngLastRow = Range("A65536").End(xlUp).Row
    
    For lngRow = FIRST_ROW To lngLastRow
        Cells(lngRow, c.Description).Value = UCase(Cells(lngRow, c.Description).Value)
    Next

End Sub

Select allOpen in new window

That sounds awesome.  As soon as I can get these columns to auto Ucase then I can send the workbook to you with the KIT BOM sheet and write up of the project. Thanks Marty!!!

All you need to do is to add two rows like row 9 following that row, and in each one refer to the column that you want to change. Note then when you type 'c.' Intellisense will show you a list of names that refer to the columns and you can pick the one that you want. 'c' is what's called an Enum which is short for enumerated list and it's a way of having a set of constants that are easy to access. In this case they are constants for the column numbers. If you look for "Public Enum c" in the code you'll see the enum which has a bit of description above it.

I suggest you mass-change the procedure name from UCaseDescription to something more general since you'll now be doing it for more than just the description. I assume you'll also annotate the change.

The link in post 38793838 should work now.

Are you waiting for something from me?

Sorry, just got on.  I downloaded your files from your article.  So far, I havent had it work but I'm still on it.

If you have problems with it please post the details there.

I have everything together.  Want me to go ahead and post the next question?

Yes, but please first attach the workbook in which you included the changes to UCaseDescription.

I have attached the file as requested.
New-Item-Upload-Form-With-Kit-BO.xlsm

The code is passworded. So I need the password or an unprotected version.

Oh, and unless the details are in the WB, I need to know in detail at some point (perhaps in the new question) exactly what you need done.

The password is simply two spaces "  "

Okay I await your new question and the details of what you need to be done with KIT-BOM.

In modifying your recently updated WB I found that I had to delete your certificate in order to save the changes I made. I also saw that the Font Style for the Cambria font in the floating combobox was set to Underline. I didn't think you wanted that so I changed it to Regular.

Hi there friend.  My apologies for the long delay.  Troublesome times on my end.  My insurance company decided to declare my vehicle a total loss.  This forced my to spend a better part of my day off car shopping.  Not fun.  Sorry, I just had to vent. Thank you so much for making those changes.  I will review our change log to compare before and after.  I have learned a lot this way.  You have been my coding rabbi.  Haha

I will post the new question and provide links to this post.  I will also post a link to the new question here.  Thanks again Marty for all of the help my great friend!

Hi friend, I realized there was no attachment on your last post.  Would you provide the updated WB so I can attach with the new question?  Thanks Marty.

Here it is.
Marty0120.xlsm

Here is the link to the new question:
 - Next Question Concerning Kit and Bill of Materials ('BOM')