Combine Two Userforms Into One

This is a sequel to a question I previously asked concerning a workbook with two userforms that performed the basic task.  As per the recommendation of a new friend and current Expert, I'd like to delete one of the userforms ('fmMultiple') and code the remaining to provide an interface to allow both a single item or multiple items to be requested for upload.  Thanks for the help.

Link to previous question:
Previous Question (Answered)
New-Item-Upload-Form.xlsm
Christopher WrightDirector, Service DeliveryAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
This version has warning messages for txtDescription, txtVendorPN and txtManufacturerPN. It also allows the IDC 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.
One-Form-Upload-Jan-7a.xlsm
0
 
Martin LissOlder than dirtCommented:
Okay, working on it.
0
 
Martin LissOlder than dirtCommented:
Please comment on the looks of this.

Proposed design
The 'Email Request' button could say 'Done' instead, and I really like 'Cancel' instead of 'Close'.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
0
 
Martin LissOlder than dirtCommented:
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
0
 
Martin LissOlder than dirtCommented:
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?
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
0
 
Martin LissOlder than dirtCommented:
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
0
 
Martin LissOlder than dirtCommented:
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.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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!
0
 
Martin LissOlder than dirtCommented:
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.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Roger that!
0
 
Martin LissOlder than dirtCommented:
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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

Open in new window

0
 
Martin LissOlder than dirtCommented:
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.picture
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
This error occurs for me when I click the 'Enter Another' button.  Error Message
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
This occurs when I enter an item and hit the 'Enter Another' button.  Thanks Marty
0
 
Martin LissOlder than dirtCommented:
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'.
0
 
Martin LissOlder than dirtCommented:
Nevermind I was able to reproduce the error. It occurs when selecting the last entry in one of the comboboxes. I'll be back.
0
 
Martin LissOlder than dirtCommented:
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
If you don't mind me asking, what was the problem?
0
 
Martin LissOlder than dirtCommented:
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.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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!
0
 
Martin LissOlder than dirtCommented:
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.
0
 
Martin LissOlder than dirtCommented:
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?

Possible warning message
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
That is perfect!  Yes, please.  That is a very smart move.  Well played my friend!!
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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!

Newly Added Test-New-Item-Upload.xlsm
0
 
Martin LissOlder than dirtCommented:
Glad you like it so far and yes, a new question, would be nice. Post the URL here.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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!
0
 
Martin LissOlder than dirtCommented:
Always glad to help. But again let me know if you have any questions about the code.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.