Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Combine Two Userforms Into One

Posted on 2013-01-06
33
Medium Priority
?
1,248 Views
Last Modified: 2013-01-08
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
0
Comment
Question by:Christopher Wright
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 14
33 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38748973
Okay, working on it.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38748999
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
 

Author Comment

by:Christopher Wright
ID: 38749077
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:Martin Liss
ID: 38749130
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38749193
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
 

Author Comment

by:Christopher Wright
ID: 38749232
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38749243
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38749247
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
 

Author Comment

by:Christopher Wright
ID: 38749262
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38749275
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
 

Author Comment

by:Christopher Wright
ID: 38749292
Roger that!
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38749364
0
 

Author Comment

by:Christopher Wright
ID: 38749459
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38749489
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
 

Author Comment

by:Christopher Wright
ID: 38750762
This error occurs for me when I click the 'Enter Another' button.  Error Message
0
 

Author Comment

by:Christopher Wright
ID: 38750764
This occurs when I enter an item and hit the 'Enter Another' button.  Thanks Marty
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38751284
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38751559
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38751791
0
 

Author Comment

by:Christopher Wright
ID: 38751836
If you don't mind me asking, what was the problem?
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38751892
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
 

Author Comment

by:Christopher Wright
ID: 38752037
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38752105
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38752743
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
 

Author Comment

by:Christopher Wright
ID: 38753154
That is perfect!  Yes, please.  That is a very smart move.  Well played my friend!!
0
 

Author Comment

by:Christopher Wright
ID: 38753162
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
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 38753284
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
 

Author Comment

by:Christopher Wright
ID: 38754625
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38755755
Glad you like it so far and yes, a new question, would be nice. Post the URL here.
0
 

Author Closing Comment

by:Christopher Wright
ID: 38756626
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38756652
Always glad to help. But again let me know if you have any questions about the code.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

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

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

Join & Ask a Question