Combine Two Userforms Into One

Okay, working on it.

Please comment on the looks of this.


The 'Email Request' button could say 'Done' instead, and I really like 'Cancel' instead of 'Close'.

This looks great.  I agree with using 'Cancel' instead of 'Close'

Maybe use 'Submit' instead of 'Email Request'  ...?

Quick Question, will there be a way for the user to see all items before the list is emailed?






 Doc4.docx

Yes the user can see the items, but so that I can design it in the best way I have two questions.

1. When might he want to do this, and

2. Why would he want to do that?

Here's my 1st attempt at the single form approach. It doesn't include the solution to your "quick question" of course but otherwise it seems to work. If you look at the code you'll see that it's much shorter and I think much easier to understand than what you had originally. Take a look at the code line by line, and if there's anything you don't understand just ask.
One-Form-Upload-Jan-6.xlsm

Another question is would he want to see just the actual data he typed in, or would he want to see all the data from the IDC sheet?

I am receiving an error message when I attempt to Submit the form.  I attempted several times to replicate but to no avail.  I have provided a screen shot of the error.

I also made an attempt to tailor this myself by deleting the first tab and hiding all other tabs except for the 'IDC Working Form'.  This would allow the user to see the data populated as they hit 'Add Another' button.  Unfortunately, I received an error at the Open Event.  I have also provided a screen shot of this as well.  

Allowing a user to see the  would be a great interface for the user to see all of the items listed.  This would allow the user to review all of their work before hitting the submit button.  By doing this, however, would it be beneficial to have a button allowing the user to 'Edit' their work on the 'IDC Working Form' tab if a mistake is noticed?  Thanks Marty
Error-Message.docx
Chris-Version.xlsm

I don't know why the OLEObjects don't work for you but to get around the problem for now just change the .Top and .Left lines to

.Top = Application.Top + 280
.Left = Application.Left + 258

is the 'Open" event that you are talking about the Workbook_Open event?

In Chris-Version you don't show sheet1 at all. I think that's a great idea and I'll do that in my future version.

Yes sir.  I mean the Workbook_Open Event.  Forgive my lack of specification.  I purposely deleted sheet 1 as it was not needed.  Thanks Marty!

BTW, it hasn't been a big problem but if there's a picture you want to show me it's easier for both of us to just use the little button just to the right of the < > button.

Roger that!

New version.
One-Form-Upload-Jan-6a.xlsm

It provided an error:

Error-Message-2.docx

Option Explicit
Private Sub UploadSingle()
Dim Ctrl As Control
With Sheets("IDC Working Form")
    NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & NR).Value = Me.txtDescription.Text
    .Range("B" & NR).Value = "103"
    .Range("C" & NR).Value = cboVendorName.List(cboVendorName.ListIndex, 1)
    .Range("D" & NR).Value = Me.txtVendorPN.Text
    .Range("E" & NR).Value = cboCountry.List(cboVendorName.ListIndex, 1)
    .Range("F" & NR).Value = Me.cboBerry.Value
    .Range("G" & NR).Value = Me.txtManufacturerPN.Text
    .Range("H" & NR).Value = "ITP"
    .Range("I" & NR).Value = Format(Me.txtNSN, "0000-00-000-0000")
    .Range("J" & NR).Value = "N"
    .Range("K" & NR).Value = cboVendorName.List(cboVendorName.ListIndex, 3)
    .Range("L" & NR).Value = Me.txtADS.Value
    .Range("M" & NR).Value = Me.txtMSRP.Value

Select allOpen in new window

Can you detail the steps that led to the error and can you tell me which line it happens on because it never happens to me.

Again to show me a picture use this button.

This error occurs for me when I click the 'Enter Another' button.  

This occurs when I enter an item and hit the 'Enter Another' button.  Thanks Marty

I have no clue right now why that is happening since I've never had it happen to me so I need to ask a bunch of questions.

What version of Excel are you using?

Are you using the WB named "One Form Upload Jan 6a.xlsm" without having modified it in any way?

Do you do anything different than this?
1.    Open the workbook.
2.    Enter data in all fields.
3.    Click 'Enter another'

If you can remember (or do it again) please tell me exactly what data and which values you enter/choose prior to clicking 'Enter another'.

Nevermind I was able to reproduce the error. It occurs when selecting the last entry in one of the comboboxes. I'll be back.

Fixed.
One-Form-Upload-Jan-7.xlsm

If you don't mind me asking, what was the problem?

No, I don't mind at all. In the current code you'll find the following lines and notice that the cboCountry combobox was being indexed by the cboVedorName combobox selection.


    'new
'    .Range("E" & NR).Value = cboCountry.List(cboVendorName.ListIndex, 1)
    .Range("E" & NR).Value = cboCountry.List(cboCountry.ListIndex, 1)

I believe that what happened was that when I found that the code was using some textboxes as temporary storage for some combobox values and I changed that to use the value directly from the comboboxes, I made a mistake and used the wrong combobox.

Ahh, I see.  I was thinking that I needed to reference something in my Microsoft Object library.  I was waaaaaay off.  Thank you for teaching me Marty!

This points out one aspect of testing that I obviously ignored and that is that when working with listboxes, comboboxes, arrays and etcetera that you should always test the first and last values.

The ControlTipText for both txtVendorPN and txtManufacturerPN both say "35 Character Limit" and the MaxLimit for both controls is 35. That's all good but if you enter a string that's longer than 35 characters there's no error message (which is normal) but since 35 characters is longer that the textbox you may not notice (speaking from experience) that the 36th and later characters are being truncated. Would you be interested in this happening?

That is perfect!  Yes, please.  That is a very smart move.  Well played my friend!!

Another question, suppose someone hits 'Enter Another Item' but then realizes they do not have anymore items, they are forced to add another fake item since the userform will not allow them to simply hit submit unless there are values on the userform.  How can we work around that?  Thanks Marty

This looks amazing Marty.  A couple of questions, however. Is it possible to have the columns highlighted in Blue Hidden? Also, I have added two additional (reference only) columns in yellow.  This is only to allow the user to see the actual value of the Vendor and Category they entered to show if they made a mistake or not. Would it also be possible to un-hide all of the blue columns when the form is Submitted (Emailed)?  Would this code go in the Mail_Mode...?  I have provided a screen shot pic and attached a matching workbook.

All of the validations work perfect!  The form works very quick and is virtually seamless in its email transition.  Also, I added code that would close the workbook altogether once they have hit the submit button and emailed it on.  I am contemplating other "protection" measures on the actual working form (ie: uneditable header fields, column widths, etc.)  That I can do on my own but I may bounce the code your way to scold me on my terrible skills.  Haha, I don't mind, I am quite certain I am learning from a true EXPERT.  God bless you Marty!

If you want, I can close out this question and add the hidden and additional columns as a new question.  Just let me know.  Thanks again for all of your help with this Marty!

Test-New-Item-Upload.xlsm

Glad you like it so far and yes, a new question, would be nice. Post the URL here.

Hands down an amazing Expert.  Marty has taught me a lot in our pursuit to create this interface project.  He has shown not just that he has mastered his skills, but has the wisdom to let me learn along the way.  I am actually eager to see each message come across because I know I am getting another bit of knowledge to take away.  Thank you so much for everything Marty!

Always glad to help. But again let me know if you have any questions about the code.

Marty, below is the link to the new question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27988918.html