?
Solved

Combine Two Userforms Into One

Posted on 2013-01-06
33
Medium Priority
?
1,307 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
32 Comments
 
LVL 50

Expert Comment

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

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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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 50

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 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