Solved

Combine Two Userforms Into One

Posted on 2013-01-06
33
1,025 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
  • 18
  • 14
33 Comments
 
LVL 45

Expert Comment

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

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
 
LVL 45

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 45

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 45

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 45

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 45

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 45

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 45

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 45

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 45

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 45

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 45

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 45

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 45

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 45

Accepted Solution

by:
Martin Liss earned 500 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 45

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 45

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
 

Author Comment

by:Christopher Wright
ID: 38756662
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now