Adding validation to Userform

Experts,

I need help with adding validations to my user form.  I have listed some validations below and added the URL to the workbook.

Field Validation Rules            
Add New or Edit Record            
      Ref ID#, Date Quote Received, Quote#      
1      The ref ID # must be a numeric in value      
2      The ref ID # must pickup the next consecutive number for a new add      
3      When the user enters a Quote number it must Lead with 1 letter character following by hyphen and then 8 numeric number values      
4      Each quote # must relate to one Ref# . "user should be flag when the Ref ID # and Quote numbers are out of sync      
5      When adding entering the Quote # it must must be the same numeric number for all  quotes with the same quote # regardless of the number of quote line items      
           
Customer Information            
1      When adding or updating a record the following fields can be blank (CTR#, Asset ID, Org ID Business Unit Contact and Term)      
2      Customer Information fields CTR# Asset ID, Org ID and Business Unit Contact have valid list and should be validated off of the Project name list      
           
Product/Services Fields            
      Monthly Price, Original Term, Term and Quantity, License Type, License Type 2      
1      Monthly Price, Original Term, Term and Quantity must be numeric fields and none can be negative      
2      Monthly Price, must show as currency $21.25 and user ask " are you sure price is correct"?      
3      There are no absolute limit on monthly price and qty      
4      Original term must not exceed 48.00      
5      Manual changes can be made to all fields except for License Type 2, but user should be prompted with a "are you sure you want to proceed with change"? (All fields can be manually)      
           
      Dates      
1      Start and End date must be valid and formatted MM/DD/YYYY      
2      Start date cannot be in the past more than one year      
3      End date must not be before Start Date      
4      End Date can  have a future date of no more than 5 years

https://www.experts-exchange.com/questions/29138866/Preselect-listbox-items-and-map-to-another-listbox.html#a42827444
MelbutAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I have several questions and I'll most likely have more later:)
ref ID #
1) I understand that for Add New that the value has to be the next higher value, but what about Update? Could 1 be changed to 2? Could 1 be changed to 99?

Quote#
2) You say 8 numbers but shouldn’t that be 7 numbers since that's what existing Q-7657895 has?
3) For Update can the quote number be changed? If yes then what ref id do I check against. And what do I check against for Add New?
4) You say “When adding entering the Quote # it must must be the same numeric number for all” but how can I tell what the user’s intention is? Couldn’t the second or subsequent Add-New be intentionally for a different quote?

CTR#
5) Can there be more than one CTR# for the same project?

Monthly Price
6) You say “must show as currency”. Why is the dollar sign necessary? If you really want it, we don’t need to do that in the userform. Instead the Monthly Price column could be formatted as currency.
7) You want the user to be asked “are you sure price is correct" Why is monthly price more important then term or price since they all affect the total price? Instead, how about at the end of the validations we ask the user something like “Total Price for this line item is $12,345,67. Is that correct?”

Numeric values
8) From your requirements it seems like Original term can have two decimals. Can any other numeric fields have decimals? Is 2 the limit for number of decimals?
Martin LissOlder than dirtCommented:
Forget about question 1. In Add New it is automatically set to the correct value and I just won't let it be changed.
MelbutAuthor Commented:
ref ID #
1) I understand that for Add New that the value has to be the next higher value, but what about Update? Could 1 be changed to 2? Could 1 be changed to 99?

The way I view the update button functioning is if the Quote it self was revised from the vendor causing an  update change, but this should not impact the change in Ref ID#, however, the comment section would be use to note the quote was revised or a change of some sort was needed due to a qty change or additional item add etc...

Quote#
2) You say 8 numbers but shouldn’t that be 7 numbers since that's what existing Q-7657895 has?
3) For Update can the quote number be changed? If yes then what ref id do I check against. And what do I check against for Add New?
4) You say “When adding entering the Quote # it must must be the same numeric number for all” but how can I tell what the user’s intention is? Couldn’t the second or subsequent Add-New be intentionally for a different quote?

2) Remember the data you received from the workbook was fictitious data to field in the fields to present some data to work off to build the userform.  I do have an update workbook that has more realistic data and shows the exact quote string/number.
3) No, the Quote number can not be changed.
4) What I was trying to convey is that if a quote is received with multiple line items.  The quote number needs to be applied to each of those line items and lets say if the next higher value Ref ID # number is 100.  The 100 has to be applied to all the line items under that quote.

CTR#
5) Can there be more than one CTR# for the same project?

5) Haven't seen this happen yet, likelihood is slim, but possible

Monthly Price
6) You say “must show as currency”. Why is the dollar sign necessary? If you really want it, we don’t need to do that in the userform. Instead the Monthly Price column could be formatted as currency.
7) You want the user to be asked “are you sure price is correct" Why is monthly price more important then term or price since they all affect the total price? Instead, how about at the end of the validations we ask the user something like “Total Price for this line item is $12,345,67. Is that correct?”

6) Agree
7) That makes sense as well and overall works out better :)

Numeric values
8) From your requirements it seems like Original term can have two decimals. Can any other numeric fields have decimals? Is 2 the limit for number of decimals?

8) Monthly price, Total Price and Original term are the only ones that should have 2 decimals and 2 should be the limit for the number of decimals
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Martin LissOlder than dirtCommented:
In the workbook you linked to, you said quotes Q-7657895 and Q-8796546 were real data but they have only 7 digits. So please verify that you actually want 8.
MelbutAuthor Commented:
Yes the numeric digit should be 8 and not 7.  I fail to add the full digit length, so the 7 is not correct.
Martin LissOlder than dirtCommented:
Are there lists of known Asset Types, Org ID, and Business Unit Contacts like there are for License Type? If so what are they?
MelbutAuthor Commented:
I can provide that work book later this evening.  That information is included in the cleaned up data version.
MelbutAuthor Commented:
Are there lists of known Asset Types, Org ID, and Business Unit Contacts like there are for License Type? If so what are they?

The updated workbook, includes the Asset ID, Org ID and Business unit contact list located on the list sheet.  I also added  4 realistic quotes and  deleted some columns that are no longer needed and rearranged the license type 2 column.

The other workbook is the original workbook, but includes the list sheet reference in your quoted comment, as well as 4 realistic quotes all columns are still intact and no rearrangement of any columns
29138866i.xlsm
Updated-Workbook.xlsm
Martin LissOlder than dirtCommented:
...and  deleted some columns that are no longer needed...
AS we talked about before, removing (or adding) columns invalidates a lot of the existing code so I can't use anything from that workbook.

Thanks for the new tables in the other workbook. Do you want those tables to show up as comboboxes on the userform?

BTW, in both workbooks the quote numbers have only 7 digits. So I ask again, is 8 correct?
MelbutAuthor Commented:
Understood.  The new attached work book should have the quotes with 8 digits and yes those tables are very similar to the license Type and license type 2.  Once the Project name is selected it should pick the Asset ID, Org ID and Business unit name as they are related to the project name
Martin LissOlder than dirtCommented:
Here is a preliminary workbook. It does not attempt to validate Asset ID, Org ID or Business Unit. Please carefully check the validations for all the other fields. It's very possible that I've misunderstood or missed some requirements so please test the fields top to bottom right to left for both Update and Add New, and if there are problems report them this way.

Ref ID # Update
Problem 1 - description
Problem 2 - description

Ref ID # Add New
Problem 1 - description
Problem 2 - description

Date Quote Received Update
Problem 1 - description
Problem 2 - description

Date Quote Received Add New
Problem 1 - description
Problem 2 - description

If you get more than two or three (hopefully I'm not being too optimistic) then stop and I'll work on those.

Besides the validations, this workbook includes:
  • A fix for the textbox selection problem
  • You can now use the tab key to move through the fields in order, top to bottom, left to right
  • Dates are no longer blanked out if no selection is made from the calendar
29140609.xlsm
MelbutAuthor Commented:
Ok, thanks Martin!
MelbutAuthor Commented:
Ref ID # Update
Problem 1 -  If I try to update/chang the project name I get - Run-time error 5: Invalid procedure call or arguement - MsgBox lbl3.Caption & " must be " & strMsg & " or blank." & colCTR(1) & " selected", vbOK & vbInformation, "Validation Error"description
Problem 2 -  This not necessarily a problem, but it you make a change and at the end of the validation you decide not to proceed with change should it not revert back to the original setting.  For example, if I change the pricing from say 750 to 4.75 and decide not to accept the change I think it should go back to the initial price amount before the change.may not remember what was initially in that field.

Ref ID # Add New
Problem 1 - If I try to add a complete new record I get - Run-time error 5: Invalid procedure call or arguement - MsgBox lbl3.Caption & " must be " & strMsg & " or blank." & colCTR(1) & " selected", vbOK & vbInformation, "Validation Error"
Problem 2 - description
Martin LissOlder than dirtCommented:
Ref ID # Update Problem 1 is happening in the validation of CTR# where it looks to find all the existing  CTR#s for the project name. The error happens because the code expects that there is at least one and with a new project there are none. How should I handle that?
  1. Tell the user "No CTR# found for project name" and stop validations at that point?
  2. In addition to #1 suggest to the user that CTE# should be blank?
  3. In addition to #1 change it to blank and tell the user what was done?
  4. Or?
Martin LissOlder than dirtCommented:
Ref ID # Update Problem 2:
In the case of pricing you are asked if it's correct. If the answer is no I can easily change it back and I probably should, but if it's one of the other fields I'm sure you don't want to revert all of them back just because the user decides that one is wrong (which is equivalent to the user closing the userform and starting again). Bottom line, after being asked if every thing is OK, can't the user just correct what he wants?

BTW for Ref ID # Add New Problem 1, just saying "Same as Update" is good enough.
MelbutAuthor Commented:
Ref ID # Update Problem 1 is happening in the validation of CTR# where it looks to find all the existing  CTR#s for the project name. The error happens because the code expects that there is at least one and with a new project there are none. How should I handle that?
Tell the user "No CTR# found for project name" and stop validations at that point?
In addition to #1 suggest to the user that CTE# should be blank?
In addition to #1 change it to blank and tell the user what was done?
Or?

1) I think that right now there should be no validation on the CTR# and allow the user to enter, but maybe evoke an alert to ask the user to confirm correct entry before proceeding.
Martin LissOlder than dirtCommented:
In an Update since there is only one Asset ID associated with a project, I don't see any reason why the user should be able to change the Asset ID, and I should make it so that they can't. The same holds true for Org ID and Business unit contact. Do you agree?

In Add, should they be able to add a brand new project name? If so would you like me (after verifying with the user that all values are correct) to add to the List sheet tables. If so I'll do that later.
MelbutAuthor Commented:
[quoteIn an Update since there is only one Asset ID associated with a project, I don't see any reason why the user should be able to change the Asset ID, and I should make it so that they can't. The same holds true for Org ID and Business unit contact. Do you agree?][/quote]

I agree.

]
In Add, should they be able to add a brand new project name? If so would you like me (after verifying with the user that all values are correct) to add to the List sheet tables. If so I'll do that later.

Yes, they should be able to add a new project name.  Yes, add it to the list sheet tables.
Martin LissOlder than dirtCommented:
This is just a little test project to see if you are OK with this way to stop a user from changing a field. If so I would do it for all fields when they should not be updated.
Test.xlsm
MelbutAuthor Commented:
Martin,

the Test workbook looks to be empty.
Martin LissOlder than dirtCommented:
Click the star in this version.
Test.xlsm
MelbutAuthor Commented:
I like this feature. Is this a controlled feature that the administrator of the work book has? Just trying to get a better visualization of its function.
Martin LissOlder than dirtCommented:
What I was doing with textboxes that shouldn't be updated was to lock (coder language for "can't be updated")  them and because visually you can't tell them from unlocked boxes I was, after one of them was clicked,  issuing a beep sound and also displaying a short message. In the Test method I am totally disabling those textboxes. It's important to note that in either way of doing it, you can not copy from or paste to those cells. Can the administrator control that? No not unless the administrator is a programmer. Please let me know if I can do it the test way.
MelbutAuthor Commented:
understood.  Yes lets try it out.
Martin LissOlder than dirtCommented:
I think there are some conflicting requirements for asset ID. For Update, asset ID is disabled so let's ignore that but in Add New you say
When adding or updating a record the following fields can be blank (CTR#, Asset ID...)  
, but you also say
Customer Information fields CTR# Asset ID... should be validated off of the Project name list  
   

Please let me know how I should validate asset ID, if at all, when adding.
Martin LissOlder than dirtCommented:
While I'm waiting for your answer to my question about asset ID above, please test this for Update only.
29140609a.xlsm
MelbutAuthor Commented:
I mispoke regarding the Asset ID being left blank.  Typically a quote will be associated to a particular project name as well as an asset ID, Org ID and BU, however, there may or may not be a CTR# assigned.  If for example, on a new Add the user enters a completely new project name such MF-DUS. The MF-DUS gets an Asset ID, Org ID and BU assigned/associated to it and if another quote comes in with the same project name the associated asset id, org id and bu should populate because it already exist base off of an existing quote with the same project name .  Same whole true with the CTR# however, the exception is that the CTR# is not always provide on a quote.  We may get the CTR# after the fact or not at all.  The CTR# does completes the full picture of the quote but it does impact anything if its left blank.  

I hope this makes sens.
MelbutAuthor Commented:
The update field looks to be working great!

I updated the Date Quote received, the CTR# and corrected the start month based off of the validation alerts.  Of course its getting the same run-time error.
Martin LissOlder than dirtCommented:
Of course its getting the same run-time error.
What run-time error?
Martin LissOlder than dirtCommented:
If you haven't seen it please take a look at my question above.

In any case I've made a lot of progress and here's a workbook hopefully will be pretty good for both Update and Add New. Please test it thoroughly. To help you test Add New I've set it up temporarily so that you can choose to have most of the fields filled in with test data if you like, and from that point you can change or delete values while you are testing.
29140609b.xlsm
MelbutAuthor Commented:
What run-time error?
Could not find the specified object  "Frmeditrecord.show vbmodeless"

Will test the new workbook.  Thanks.
MelbutAuthor Commented:
Martin,

Remember that each quote can have more than one line item, therefore, the Ref# needs to be the same for each line item added from that quote.  If the quote have 5 line items then the ref# needs to be the same for all 5 line items.

So if was a little hard to test since I couldn't add a complete quote with more than one line item.
Martin LissOlder than dirtCommented:
Please let me know if you have completed your testing. I assume not.
Martin LissOlder than dirtCommented:
In this update I
  1. Corrected the ref# problem you mentioned by quietly changing it to the correct value. Instead of doing it quietly do you want a message telling the user I did that?
  2. Consolidated several of the final validation messages into one. Is it too complex? Please note that the difference in what it says it's going to do is slightly different depending on if it’s Add New or Update. If you want to change the wording of this consolidated message, or any message, let me know.
  3. Froze headings on RAW_DATA sheet
  4. Added a temporary "Fill with test data" button that you can click when you want to fill the boxes with test data
MelbutAuthor Commented:
Hi Martin,

No, I didn't complete the testing because of the Ref ID# issue.  Also, I can continue to test based off the latest updates if you provide the attachment.
Martin LissOlder than dirtCommented:
Sorry, thought I had.
29140609c.xlsm
MelbutAuthor Commented:
Testing results.  This all all have for now.

Ref ID # Add New
Problem 1 - Ref ID # is still not working properly.  The Add New will not allow me to change the Ref ID # if the Quote#'  are the same for each line time. Again if  the quote have more than 1 line items then the ref# needs to be the same for all  line items.  The quiet change currently is not working and if the quiet change is the method of use than yes the user should be notified telling them the change was applied.

Ref ID # Update
Problem 1 - When I update a field for and existing quote.  the validation for the Start and End Date should only apply if I am trying to make a change to that date otherwise it will need to assume the date are correct and allow the update
Problem 2 - No matter what valid field  I am changing.The date validation pops up and when i go to close it defaults to making the date range not to be more than one year in the past.

Start and End Date
Problem 1 - If I make a mistake when entering the End Date I am force to go back and re-enter the Start Date before t all allows me to correct the End Date.  
Problem 2 - There is no indicator or mouse cursor flash letting me know that I am in the correct cell. So it's hard to tell if I am in the Start  Date field or the End Date field

Project Name
Problem 1 - If the project Name is a new first time ever used name being created the code needs to allow the Asset ID, Org ID  to be entered manually.  This should populate as a new add to the list sheet.

License Type 2
Problem 1 -  If the License Type  is a new first time ever used license Type being created the code needs to allow the user entered manually the License Type and License Type 2.  This should populate as a new add to the list sheet.  Note:  Even though there may b project name
Problem 2 - Not necessarily a problem, but  just a observation.  Not sure how to handle the scenario.  Even though  a quote can come in with a new project name doesn't mean it will have a  new  license Type and can still be pulled from the  existing list and therefore, the License type 2 should also be pulled from the list.

Original Term and Term (This problem a bit difficult to explain)
Problem 1 - The script for the Original Term and Term looks to be reversed. Term is usually is 12 months and actually can be a fixed unchanged value.  Original Term will vary. But it looks like the calculation is picking up on the Term and not the Original Term and the validation is giving an incorrect value, but if I flip the number and put 12 mo in the original Term and a varied number in the Term the calculation is correct. Validation is working as expected
Problem 2 - When I select  proceed with Add New the term and and original term doesn't fall into the correct columns.
Martin LissOlder than dirtCommented:
For the Ref ID # Add New problem, are you saying that the Ref ID # should be editable, and if the user changes it that I should ask the user "All lines for Quote Q-12345678 need to have the same Ref ID #, should I change them all?). BTW in your company's usage, is there a better word than "line"?
MelbutAuthor Commented:
Martin,

The Project Name and list sheet
Problem: I notice when adding a project name regardless of the number of line  items it get copied to the list sheet. It only needs to replicate once not for each line item from a new Quote.  For example,  if a new quote is added with a new project name call  SF Hamp and the quote has two line items.  What I notice is the project name SF Hamp is being added to the list sheet twice.  I believe this is the case for all non existing item headers (License Type, License Type 2, Asset ID etc).
MelbutAuthor Commented:
For the Ref ID # Add New problem, are you saying that the Ref ID # should be editable, and if the user changes it that I should ask the user "All lines for Quote Q-12345678 need to have the same Ref ID #, should I change them all?). BTW in your company's usage, is there a better word than "line"?
Is this your solution?

I would say yes make it editable.  There is no mandatory company usage for the word "line"  However, instead of saying line we can simple call call it " services item"
Martin LissOlder than dirtCommented:
Okay I can do that but I still don't understand why what it's doing now isn't correct. Here's an example. Let's assume that there are several services items for Q-12345678 with Ref ID 3. Assumption: All the Quotes will have the same Ref ID. So here's what happens:
  1. User clicks the Add New checkbox
  2. The next available Ref ID number is placed in the Ref ID # textbox by the code
  3. User enters quote number Q-12345678
  4. The code looks in the RAW_DATA sheet and sees that the Ref ID for that quote is 3
  5. The Ref ID # textbox is changed to 3.
What is wrong with that?
MelbutAuthor Commented:
Okay I can do that but I still don't understand why what it's doing now isn't correct. Here's an example. Let's assume that there are several services items for Q-12345678 with Ref ID 3. Assumption: All the Quotes will have the same Ref ID. So here's what happens:
User clicks the Add New checkbox
The next available Ref ID number is placed in the Ref ID # textbox by the code
User enters quote number Q-12345678
The code looks in the RAW_DATA sheet and sees that the Ref ID for that quote is 3
The Ref ID # textbox is changed to 3.
What is wrong with that?

Nothing is wrong with the 1st initial add of Q-12345678 services item.  The problem arise when you try to add another services item against the same Q-12345678 with the same Ref ID 3.  The userform defaults to the next Ref ID # which will be 4.  Do I continue to enter the services item even though it has a the Ref ID # of 4.  When does the code change it to 3 after it's been added? I tried that and nothing changes.

I am not understanding #4 and #5 of your example.  Maybe this will work if Q-12345678 is already existing and I want to update, which that still doesn't make much sense either, but if a quote is new and have never been entered what is the code searching for on the RATA_DATA sheet?  Does all the service items have to be entered 1st and then the code locates the first Ref ID # that was entered and then change all like Quote # to that Ref ID #?  

I am sorry I am a bit confused on 4 and and 5 and principle behind its function.
Martin LissOlder than dirtCommented:
Nothing is wrong with the 1st initial add of Q-12345678 services item.  The problem arise when you try to add another services item against the same Q-12345678 with the same Ref ID 3.  The userform defaults to the next Ref ID # which will be 4

Yes, but it's changed to 3 again just like with the first one. I can make it less confusing if I don't fill in the Ref ID until the user enters the quote number.
MelbutAuthor Commented:
Ok, let's try that.  Overall i think everything is starting to come together nicely.
Martin LissOlder than dirtCommented:
Project Name Problem I don't understand what you mean because I assume that you would use 'Add New' to do that, and then all fields except Ref ID are editable.

I also just realized that while I add to the Project name, Asset ID, Org ID and Business Unit Name columns on the List sheet, those columns are not being used.
29140609d.xlsm
MelbutAuthor Commented:
Martin,

Project Name Problem I don't understand what you mean because I assume that you would use 'Add New' to do that, and then all fields except Ref ID are editable.

I may have to revisit the Project Name to see why I made that comment.

1. The Ref ID # looks to be working great! for the Add new.

2. Add new - With any new quote the project name may not be a completely new project name and therefore, the validation should check to see if the project name is already listed, and if so  the Asset ID, Org ID, BUN should auto populate accordingly to the list mapping

3. Same holds true for the License Type.  It should have the combo box that allows me to start typing to see if the license type is already existing on the list first.  If it doesn't find it then it would be considered a new Add and should get  added to the list

4. I think the Quote # should have a parameter added to pre-populate the quote field so that it can auto-fill after the first add new eliminating the need to retype the entire quote again.

5. Original Term is calculating correctly and the validations looks for that looks correct as well.

6. The new Add service item Ref ID# does not show in the view Column list

7.  The Quote breakdown is not working

8. The Search button does not work
Martin LissOlder than dirtCommented:
2. Done
3. Done
4. Please add that to your To Do list
6. What is a "view Column list?
7. Done
8. Done
29140609e.xlsm
MelbutAuthor Commented:
4. Please add that to your To Do list

Are you saying this should be a new question?

6. What is a "view Column list?
The list of quotes that shows in the dashboard on the useform.
Add New and Display record feature:
1. When adding all 490 official records into the data into the RAW_DATA sheet the user form record #Displays only 437 of 490.  
2. When I proceed to add a new quote that has a total of  two service items the 1st service item  doesn't appear in the dashboard, but the record # displays all 490 records except for the 1st service item I just added.  
3. When I proceed to add the 2nd service item from the same quote the record # displays correctly 492 items, but only shows the 2nd service item in the dashboard and not both new adds.
4.  What I notice is that the 1st add is not in order and displays out of order.

Search Button:  
The search works however,
1. It is not listing all 80 Ref ID# records that should be searchable and same with the Quote #
2. If I select in Ref ID# I get a run-time error '2147024809 (80070057: could not get the list property, Invalid argument Me("editstudent" & iX - 1).Value = Me.lbxData.List(lRw, iX)
3. If I manual enter the Ref ID#

Quote Breakdown
The quote breakdown is not picking up the number of lines and not calculating correctly.  

I added the following quote service items:
Service item 1 - Q-02526188; project name: Corp Communication, License Type: Additional Business Unit (+1), License Type 2: Marketing Add-Ons,Start Date: 3/22/19; End Date: 10/31/19, Mo. Price 850;  Original Term: 7.3, Term: 12, Qty: 1.  Total should be 6,205.00

Service item 2 - Q-02526188; project name: Corp Communication, License Type: Prmr Success Plan - ExactTarget, License Type 2: Marketing Add-Ons,Start Date: 3/22/19; End Date: 10/31/19, Mo. Price 170;  Original Term: 7.3, Term: 12, Qty: 1.  Total should be 1.241.00

Total for both service items - 7, 446.00

It is calculating base off of 7.32 versus just 7.3 which is the original term.  The calculation can not be greater than than the original term reason I have the rounding column to try to compensate for that. if using the 7.3 the total quote comes up to 7446.40 which is only a few cents but it has to match the quote of 7,446.00

Also, the subtotal under the Total price column is not add all the service items together for that given quote.
Martin LissOlder than dirtCommented:
6. The new Add service item Ref ID# does not show in the view Column list
Now that I know what you mean (and in the future you can just call it "the listbox") I can't reproduce the problem.

Yes, 4 should be a new question.

1. When adding all 490 official records into the data into the RAW_DATA sheet the user form record #Displays only 437 of 490.
Fixed
2. When I proceed to add a new quote that has a total of  two service items the 1st service item  doesn't appear in the dashboard, but the record # displays all 490 records except for the 1st service item I just added.  
3. When I proceed to add the 2nd service item from the same quote the record # displays correctly 492 items, but only shows the 2nd service item in the dashboard and not both new adds.
4.  What I notice is that the 1st add is not in order and displays out of order.
Fixed
The search works however,
1. It is not listing all 80 Ref ID# records that should be searchable and same with the Quote #
2. If I select in Ref ID# I get a run-time error '2147024809 (80070057: could not get the list property, Invalid argument Me("editstudent" & iX - 1).Value = Me.lbxData.List(lRw, iX)
3. If I manual enter the Ref ID#
I'm not sure what you are saying here, but I "fixed" it by not allowing manual entry into the search boxes (since the search boxes contain all existing values)
The quote breakdown is not picking up the number of lines and not calculating correctly.
Fixed by rewriting half of the code for the button.
29140609f.xlsm
MelbutAuthor Commented:
Thanks Martin.

Only problem I saw was the the service item 6 in the list box didn't show the Deploy (Active, or Expired ) or show the correct total cost.

Also, the user form stops working properly after performing one or two  task. for instance I can add a new quote and run the quote breakdown and it works great but when I try to search or update I will get some sort of run-time error.  Keep in mind that it's not always the same order  task performed.  This time I tried to update and got the following run-time error '94

Invalid Use of Null
 iCol = Choose(Me.cboFind.ListIndex + 1, 4, 2, 5, 16)
Martin LissOlder than dirtCommented:
Only problem I saw was the the service item 6 in the list box didn't show the Deploy (Active, or Expired ) or show the correct total cost.
It didn't show Deploy because the two service items were added manually, rather than via Add New. Had they been done via Add New, Deploy would have been calculated. If by 'total cost' you mean 'Total Price' on the RAW_DATA sheet then please tell me what should be shown there because I just used the numbers you provided.

About the Quote Breakdown. Please describe
  • What ref Id you changed
  • What you changed
  • What you chose in both search boxes
MelbutAuthor Commented:
About the Quote Breakdown. Please describe
What ref Id you changed
What you changed
What you chose in both search boxes

1. What ref ID I changed:  I removed Ref ID 6 and added it back in as a new add to test the userform and what I noticed
  a.  the column rows that are not on the userform did not get copied down, on the RAW_DATA sheet
  b.  Possibly due the rows not copying caused the data to not completely populate within the userform listbox
   
2. What I changed:
 a. I tried to update the date quote received.

3. What I chose in both search boxes/quote]:
a. I selected Ref ID  and then #6 and ran the search
b. I also tried selecting by Quote # Q-02526188
c. I also tried selecting Ref ID 6 and Quote # Q-02526188
d. The other search options doesn't seem to populate specifically License Type.
e. License Type 2 search populates but if you type in Community.  It populates all data with the word community.

But overall the search is functioning with the exceptions of those few quirks and for whatever reason I am having to close the workbook completely and reopen as the user-form gives a run-time error.
Martin LissOlder than dirtCommented:
1a: The data is not copying down because we decided that you just wanted to see only 19 columns (A to S) in the form, but I believe I've fixed that.
About the searching, I fixed several things that had been wrong since the time before I got involved when someone added 4 columns to the RAW_DATA sheet. The Qty total was also always(?) wrong because it was not only summing the Total Price but also kit was adding to that the sum of the Quantity.
29140609g.xlsm
MelbutAuthor Commented:
1a: The data is not copying down because we decided that you just wanted to see only 19 columns (A to S) in the form, but I believe I've fixed that.

If it works without the formulas being copied down no problem.  But I believe I was trying to say that I do not need to see A to S on the User form itself, but the column rows still need to be copied down to the next row on the RAW_DATA sheet once a new service item has been added.  Not sure how it can calculate correctly without it.
Martin LissOlder than dirtCommented:
I don't know what you are asking me to do. In the 29140609g.xlsm file when you Add New, columns A to S are filled from the listbox data and then T to Z are filled by way of formulas. Is that wrong?
MelbutAuthor Commented:
The list box scroll bars seem to be out of sync with the data when scrolling to the right.

The data is not correct still for quote 02526188 when compared to the other existing quotes/ref ID#

I Received a Run-time error '13 type mismatch
curTarget = curTarget + .Cells(lngRow, "N") - after running the quote breakdown

Here are the steps I took:
1.  I Searched on an existing quote 02526188 and then;
2. Selected that quote to be pulled back into the user form;
3. I click the search button to bring it back to the original view  and to show the quote break down button then;
4. clicked on Quote breakdown
MelbutAuthor Commented:
[quoteI don't know what you are asking me to do. In the 29140609g.xlsm file when you Add New, columns A to S are filled from the listbox data and then T to Z are filled by way of formulas. Is that wrong?][/quote]

I'm sorry I mispoke.  I meant that column T to Z should copy down.
Martin LissOlder than dirtCommented:
Why do I need to do that? Create a quote by way of Add New and then look in Raw_Data. T to Z are there.
Martin LissOlder than dirtCommented:
I can't reproduce the problem with quote 02526188 so I need more detail about what you did before the error occurred. For example
  1. Clicked the Quote Maintenance button
  2. Clicked the search button
  3. Selected Quote# in the first search box
  4. Selected  Q-02526188 in the second search box
  5. Clicked Search
  6. Closed the search by clicking the blue circle
  7. In the main listbox, selected the second services item for Q-02526188
  8. Clicked Quote Breakdown.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MelbutAuthor Commented:
[quote Run-time error '13 type mismatch
curTarget = curTarget + .Cells(lngRow, "N")][/quote]

Not sure what I did wrong, but was not able to reproduce steps that gave me the above run-time error '13.

So that's actually good news.
MelbutAuthor Commented:
Martins' work and work ethic has been outstanding.  I  have enjoyed working with this expert.  He easy to communicate with, supportive and patience in my lack of knowledge.
Martin LissOlder than dirtCommented:
Thank you for the kind words.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.