We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x
Private

Add/change fields in Userform

High Priority
148 Views
Last Modified: 2020-04-22
Experts,

I need help making the following changes to my userform.

1. Add  a Discount % field
2. Add a Total Price Field
3. Change title of Original Term field to read as Order Term
4. Rearrange the fields under the Product Information in this order:  License Type, License Type 2, Start Date, End Date Original Term, Monthly Price, Qty, Discount, total Price, Renewal Date
5. Allow for the option to add more than one Service Item (SI) per quote
6. Remove Data Frame
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If we do 5 and 6 how do we do Quote Breakdown, Delete Service Item, Quotes by Month and Search? Also if we do 5, the userform will need to be very wide so as to accommodate all the field for an SI in one line.

Author

Commented:
Unfortunately I do not know the answer to that at present and  so I guess 5 and 6 can’t be done.  Hopefully the discount will work  base on our previous discussion 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You didn't attach the 29175348d.xlsm workbook which should be the basis of this question.

I also have a question and that is is there any maximum that a discount can be? Obviously it can't be more that 100%, but would, say, 50% be valid?

Author

Commented:
You didn't attach the 29175348d.xlsm workbook which should be the basis of this question.
29175348d.xlsm
https://filedb.experts-exchange.com/incoming/2020/03_w14/1448397/29175348d.xlsm
I also have a question and that is is there any maximum that a discount can be? Obviously it can't be more that 100%, but would, say, 50% be valid? 
I have never seen anything above 25% so 50% would probably be a good max.

Author

Commented:
Here is what I would like to see as the layout.

Restructure Userform.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In your userform you show six service items. What if you need seven?
...have the combobox list as a selection for each line item...
I don't understand what that means.

Adding those six SIs and having the SI data all in one line like that might cause you problems with the userform height and/or width. Can you show me a picture of your computer when you have the current userform open so that I can see how much room you have?

Here's a different approach. Let's assume that I have already added the discount percent and total price to the current userform and that you have successfully added the first SI for a new quote. What I propose is that when you check Add New for the second SI, that instead of the code clearing everything on the userform as it does now, that it would only clear the Product Information from the first SI. Would that give you everything you want?

I assume you want the Discount % to be stored on the Original Quotes and RAW_DATA sheets. Please let me know if that's true. If it is then in order to put it in a reasonable place like between columns 'M' and 'N' I'll need to shift some columns to the right which because of the way the code refers to them, is difficult and error-prone but I'm willing to do it. I also know that there are some columns that you no longer need, so please list them for me by column letter.

Author

Commented:
In your userform you show six service items. What if you need seven? 
In a previous My message conversation I asked if it's possible to insert a multipage userform to accommodate any SIs over 7. Where for instance, the customer information on one page and Product information on another.
Note: It's less likely to have more than 12 SIs on a quote
Example of a multipage userform:
...have the combobox list as a selection for each line item...
Adding those six SIs and having the SI data all in one line like that might cause you problems with the userform height and/or width. Can you show me a picture of your computer when you have the current userform open so that I can see how much room you have?


I assume you want the Discount % to be stored on the Original Quotes and RAW_DATA sheets. Please let me know if that's true. 
Yes that is true.
How will you build into the code the calculation for the discount on total price if I don't have the ability to select which SI Total price the discount should be applied to

If it is then in order to put it in a reasonable place like between columns 'M' and 'N' I'll need to shift some columns to the right which because of the way the code refers to them, is difficult and error-prone but I'm willing to do it. I also know that there are some columns that you no longer need, so please list them for me by column letter. 
Columns that I don't need are: K,U,V,W,Y,Z
Just curious as to why the code is so restricted where you can't insert a column in  without messaging up the code?

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How will you build into the code the calculation for the discount on total price if I don't have the ability to select which SI Total price the discount should be applied to
Maybe I don't understand your question but the Discount% will be stored with the SI on the worksheets just like Quantity or Monthly Price, etc.  


Was that question a response to this?

Here's a different approach. Let's assume that I have already added the discount percent and total price to the current userform and that you have successfully added the first SI for a new quote. What I propose is that when you check Add New for the second SI, that instead of the code clearing everything on the userform as it does now, that it would only clear the Product Information from the first SI. Would that give you everything you want?

Author

Commented:
Was that question a response to this? 
Yes to some degree and yes that would be helpful but the issue is how do I apply the discount to the total price, if the second SI that I enter requires a discount to be applied to the first SIs Total price?

Here's a different approach. Let's assume that I have already added the discount percent and total price to the current userform and that you have successfully added the first SI for a new quote. What I propose is that when you check Add New for the second SI, that instead of the code clearing everything on the userform as it does now, that it would only clear the Product Information from the first SI. Would that give you everything you want?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are any of the 11 scenarios in 29175348d.xlsm like that?

...how do I apply the discount to the total price, if the second SI that I enter requires a discount to be applied to the first SIs Total price?
The main thing I don't understand is how having the ability to add multiple SIs at the same time helps you solve that problem.

Author

Commented:
Are any of the 11 scenarios in 29175348d.xlsm like that?

Q-6666666, Row 33 SI percent was obtained by applying .15% to row 32 SI Total price of  9000 = 1350

The main thing I don't understand is how having the ability to add multiple SIs at the same time helps you solve that problem.
No, it doesn't  help me solve the problem.  But I thought it would be a basis to start with in regards how the code for this formula could be written to capture the discount on total price on the userform.
I was thinking that I need to somehow be able to apply the discount through some sort of selector, (e.g., a checkbox perhaps, so that when an SI is entered that has a discount it gets applied to the SIs  total priced indicated with a check mark.

In addition, I thought that by having  all the SIs from one quote entered on the userform would also give me a complete visual of the entire quote prior to adding to the Original Quote sheet. A long with that, I thought if using the checkbox as a mechanism to select the total price changing the layout this way makes sense.

How else will I know which SI the discount needs to be applied against. Of course always open for better and more efficient ways.

I hope you see where I was going.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I know I must be missing something, and I know that that quote may have been created manually, but why couldn't it be created row by row in the userform?

Author

Commented:
I know I must be missing something, and I know that that quote may have been created manually, but why couldn't it be created row by row in the userform? 

I am not saying that it can't as long as I have the ability to apply a discount to a specific Total price SI under the same quote and the result of the discount is on its on SI Row. Are able to come up with a way to make this work without it the row by row?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are [you] able to come up with a way to make this work without it the row by row?
Maybe, but it will be a lot of work. You also however want (and I agree we need) some columns removed and of course I need to enable the ability to enter the discount percent, so can we hold off on the multiple row entry until the other two are done?

Author

Commented:
Are [you] able to come up with a way to make this work without it the row by row?
Just throwing something out there.
How about repurposing the Total price field that you added to the userform. I can enter in the Total price value and have the percentage get applied to what's entered into that field and if there if the code can be written that says something like this.

"IF discount field has a percent value, check to see if Qty field is empty, [if not empty]
    then apply percentage to Qty and Enter result to the Total Price Field.... [if empty]
    then check to see if Total price field is empty, [if not empty]
    then apply percentage to Total Price value and enter result to the Total Price for       this SI on both original Quote and RAW_DATA"

And yes we can hold off on the multiple row entry

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How about repurposing the Total price field that you added to the userform. I can enter in the Total price value and have the percentage get applied to what's entered into that field...
I don't see a need to do that if you know beforehand what the discount pct should be.

Author

Commented:
Then how would you know which SI to apply the percent against?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What I'm planning on doing is to give you the ability to create a new quote like Q-66666666. Why won't that work?

Author

Commented:
29175348d_Q-6666666.xlsm

Ok, if it works, then great, lets do it. :)

How will the code handle if the % is to be applied to two different Total price SIs.  See Q-6666666 in the attached
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In the attached workbook that has the new column layout. Please show me what the new Q-66666666 should look like on the Original Quotes sheet assuming that the quote was entered using the userform, and on that form 'Total Price' is a read-only field.
Quote6.xlsx

Author

Commented:
and on that form 'Total Price' is a read-only field.

Not following this statement, but I have attached what Q-6666666 should look.
Quote6.xlsx
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
That's not what I meant but let's ignore that for now.

Instead try this workbook and see if you can add a quote like Q-66666666 (or any quote) and be able to do what you want. Don't try the quote breakdown or any other function except for adding or updating a service item.
29177895.xlsm

Author

Commented:
I had no problems adding a typical quote with no discount, however, I was not able to apply any of the discount scenarios. see attached workbook original quote sheet and print screen sheet that shows the discount not working when I enter only the quantity and discount%

Also,  I like previously how the start and end dates would continue to populate automatically for each SI after the first one has been entered and if I need to update or change one of the dates I  can just click on the unlock button.  Will this feature be added back?

29177895.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I was not able to apply any of the discount scenarios.
I'll try to fix that. One thing I should have mentioned is that I set it up so that a discount of 10% for example would be entered as 10 and not .10. Which way do you prefer? Let me also ask you this - when a discount percent is present, should a monthly price be allowed at all?

I will fix the start and end dates.

Author

Commented:
 One thing I should have mentioned is that I set it up so that a discount of 10% for example would be entered as 10 and not .10. Which way do you prefer? 
Would it not convert like the following if I enter
1. 10 will convert to 1000%
2. .10 will convert to 10%
3. .010 will convert to 1%
4. .0010 will convert to 0.1% etc
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Normally in math when converting from percent to decimal, a percent like 10% is divided by 100 and the precent sign is removed and so we get .1, but I can have it do whatever you want it to do, so just tell me how you want to enter 10%.

Author

Commented:
I prefer to just be able to enter the number
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
So by that do you mean that if you want a 10 percent discount you would enter 10?

You didn't answer this:
when a discount percent is present, should a monthly price be allowed at all?
and by that I mean is it valid to have a quantity and a monthly price and a discount in the same SI?

Author

Commented:
So by that do you mean that if you want a 10 percent discount you would enter 10?
Yes, correct.

when a discount percent is present, should a monthly price be allowed at all?
and by that I mean is it valid to have a quantity and a monthly price and a discount in the same SI?
No it's not valid. There will never be a monthly price present when there is a discount.
Remember with a discount only items present will either be a quantity or total price these are the only two values the discount will calculate against

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are you also saying then that when there's a discount that you can either have a quantity or a total price but not both?

Author

Commented:
Are you also saying then that when there's a discount that you can either have a quantity or a total price but not both?
No, not correct. You can have a discount on both quantity or total price within the same quote. but the discount will be two seperate SIs under the same quote.(one SI for discount on Qty and another SI with discount on total price).  But you should never have have a monthly price or a term month value present for both. 
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm talking abut the validation of a single SI and so please describe any restrictions on other fields when there is a discount percent present and/or any restrictions of fields that can not be present if an other field is present.

Author

Commented:
I'm talking abut the validation of a single SI and so please describe any restrictions on other fields when there is a discount percent present and/or any restrictions of fields that can not be present if an other field is present.
Here what i can think of at the moment.
Rule #1: Quote Service item (SI) Discount cannot exceed 50%

Rule #2: When a value is entered for monthly price, a number qty and order term must have a value, and discount percent must be blank

Rule #3: When no value is entered for monthly price, a checkbox for “Qty or Total Price” must be checked triggering a value entry for one or the other, the discount percent field must have a percent value, and order term must be blank.

Rule #4: If Qty is selected based off rule #3, qty must have a value, discount percent must have a value, Monthly price must be blank, total price must be blank, and order term blank

Rule #5: If Total price is selected based off rule #3, total price must have a value, discount percent must have a value, monthly price must be blank,  Qty must be blank, and order term blank
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Thanks. For Rule #3 I don't think a checkbox is necessary if the rule is that when there is a discount, either there must be a Qty or a Total Price, but not both.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Again try this workbook and see if you can add a quote like Q-66666666 (or any quote) and be able to do what you want. Don't try the quote breakdown or any other function except for adding or updating a service item. You'll find that I did take the checkbox approach because it turned out to be easier. If you run into problems please be specific about what went wrong including details about any error or unexpected validation errors that pop up.
29177895a.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I just noticed some problems with that workbook which I'll try to fix.

While I'm fixing things, column 'O' currently says "Deploy (+/0/-)". Would you like me to change that?

Author

Commented:
Here are my findings:
  29177895a.xlsm 
1 When clicking on the Add new check box.  I notice that the user-form doesn't clear previous SI data. See Screen shot with and without the Add New box checked. 
2 I had to manually remove the previous quote data before a new entry can be added in that field
3 The Tab functionality doesn't work.  (I thought I had the ability to Tab as well as use my mouse to make a field selection on the record I want to add/update
4 The field text color doesn't show up when entering the discount % value
5 I was not able to get the correct result when I tried to enter 0.049 into the discount field.  The calculation should be 0.049 * 1500000 = 73500. But if I add a term of 1 and add the 0.049 has a monthly price then the calculation worked.
6 The discount Total price does not show on the Original quote sheet after clicking the add service button.
7 if the discount is being applied to more than one total price  SI; am I to total together manually both SI total price items and then enter it into the total price field? For example, If I have a 25% discount to be applied to row 9- (9000) and 11- (2250) of the Original Quote sheet will have to add these to total prices together and then enter them into the user-form total price field




Author

Commented:
While I'm fixing things, column 'O' currently says "Deploy (+/0/-)". Would you like me to change that? 
You can change it to "Service Item Status"
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Here's an update that I believe correctly writes the discount SIs to the Original Quotes worksheet and correctly shows them in the userform. I also noticed that when an SI was deleted that the quote breakdown still reflected the SI, so I added code that deletes that SI's quote from the RAW_DATA worksheet.

I still haven't made the necessary changes to the quote breakdown code and I can't do that until you tell me that what I've done so far is OK.
29177895b.xlsm
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
We cross-posted. Damn!
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
1. You said you wanted it to be easier when adding several SIs for the same quote so that's why most of the data in the userform is not cleared; the Product Information is cleared.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I hate to ask you to recreate your recent test quotes in the "b" version but try one and then let me know if there are problems.

Author

Commented:
 1. You said you wanted it to be easier when adding several SIs for the same quote so that's why most of the data in the userform is not cleared; the Product Information is cleared.
Yes that is correct.  However, if I am adding a completely new quote i would think that the form would clear and once the first SI for that quote has been added then the form no longer clear after the first add.


1 See my response When clicking on the Add new checkbox.  I notice that the user-form doesn't clear previous SI data. See Screen shot with and without the Add New box checked.
2
I had to manually remove the previous quote data before a new entry can be added in that field
3
The Tab functionality doesn't work.  (I thought I had the ability to Tab as well as use my mouse to make a field selection on the record I want to add/update
4 Issue corrected The field text color doesn't show up when entering the discount % value
5
I was not able to get the correct result when I tried to enter 0.049 into the discount field.  The calculation should be 0.049 * 1500000 = 73500. But if I add a term of 1 and add the 0.049 has a monthly price then the calculation worked.
6
The discount Total price does not show on the Original quote sheet after clicking the add service button.
7
if the discount is being applied to more than one total price  SI; am I to total together manually both SI total price items and then enter it into the total price field? For example, If I have a 25% discount to be applied to row 9- (9000) and 11- (2250) of the Original Quote sheet will have to add these to total prices together and then enter them into the user-form total price field
8
If there is a discount on Qty or total price.  Shouldn't the discount amount show in the userform total price field.
9.
If I close the userform and open up to add to an existing quote the Team name should default to the associated Quote#.  See screen shot.

Screen shot


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
1 and 2. How about if I rename the "Service Item" frame to say "Action" and add a new checkbox in that frame that says "Clear", which would clear all data from the userform when selected?
3. It works for me. Let's say I have a "Qty" type discount showing in the userform. If I place my cursor in say the CTR # box and press tab, the License Type box is selected because that's the next box that can be modified. If I tab again then the Quantity box is selected because it's the next box that can be modified, and the next tab goes to the Discount % for the same reason.
4. I don't know what that means.
5. You should enter it as a percent rather than a fraction, so 4.9 should be entered.
6. Here is a picture showing 3 discount service items. The 1st and 3rd are quantity based and the 2nd is total price based. Note that the discount values are percents and not fractions.
2020-04-15_08-37-06.png7. I'm not sure I understand but basically you would do whatever you did previously when creating a quote like Q-66666666 and (hopefully) the code will break it down properly.
8. Here is a picture of my second discount SI which is based on total price. It shows the total price before the discount is applied while in the OQ sheet it shows the total price after the discount is applied. The quantity based discounts don't currently show the total price because total price isn't involved in the calculation, but I can change that if you want.
2020-04-15_08-44-07.png9. When the userform is opened, the first SI is shown in the boxes and so it will show the team name for that SI. This picture was taken immediately after the userform was opened.
2020-04-15_08-54-08.png

Author

Commented:
1 and 2. How about if I rename the "Service Item" frame to say "Action" and add a new checkbox in that frame that says "Clear", which would clear all data from the userform when selected?
Ok, that will work.
 3. It works for me. Let's say I have a "Qty" type discount showing in the userform. If I place my cursor in say the CTR # box and press tab, the License Type box is selected because that's the next box that can be modified. If I tab again then the Quantity box is selected because it's the next box that can be modified, and the next tab goes to the Discount % for the same reason.
It's working now.  I guess I was doing something wrong.
 Here is a picture showing 3 discount service items. The 1st and 3rd are quantity based and the 2nd is total price based. Note that the discount values are percents and not fractions.
You are correct.  I meant to flag that as resolved.

7. I'm not sure I understand but basically you would do whatever you did previously when creating a quote like Q-66666666 and (hopefully) the code will break it down properly. 
I have been having a difficult time explaining this part.  So,currently I can apply a discount to only one of the total price SIs (eg., row 7 (9000), but I may have to apply that same discount two of the total price SIs (eg., row 7 and 9 (9000 and 2250). How can I accomplish this without having to add together the 9000+2250 and then enter that amount into the total price field. Plus I need to be able to see which SI total price the discount was applied in the formula on original Quote sheet.
8. Here is a picture of my second discount SI which is based on total price. It shows the total price before the discount is applied while in the OQ sheet it shows the total price after the discount is applied. The quantity based discounts don't currently show the total price because total price isn't involved in the calculation, but I can change that if you want.
I think showing the total price for all cases would be beneficial
  When the userform is opened, the first SI is shown in the boxes and so it will show the team name for that SI. This picture was taken immediately after the userform was opened. 
Not sure why it was not functioning for me.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
7. I think I may be able to do what you want. I’m thinking about adding a button in the Discount frame that would become active when a total price discount SI is shown in the userform. When clicked, that button would show you a list on the non-discounted SIs in the quote. So for example let’s assume you have row 13 selected. Clicking the button would show you SIs 7 through 10 and you would select 7 and 9. The code would then:
  1. Remove the quantity from rows 7 and 9
  2. Make the discount % for them to be 25
  3. Update their total price

I don’t understand however why you can’t make rows 7, 9 and 13 be 25% discounts when you first create them.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Alternatively (if I understand the process) I could
  1. Sum the total prices from rows 7, 9 and 13
  2. Update 13's total price with that sum
  3. Delete 7 and 9

Author

Commented:
I don’t understand however why you can’t make rows 7, 9 and 13 be 25% discounts when you first create them.
Because those rows are have their own SI row for the amount they represent which are based off of a typical Quote and then row 13 becomes a new SI row that shows the discount result amount of row 7 and 9
 Alternatively (if I understand the process) I could
  1. Sum the total prices from rows 7, 9 and 13
  2. Update 13's total price with that sum
  3. Delete 7 and 9

Row 13 will be the new SI that has the discount total price result that the 25% discount was applied against and therefore, will be added as a new SI row to show the discount percent as well as the result from row 7 and 9.
Rows 7 and 9 are service items with its own unique license type and cannot be deleted.
Note: that we have been using the same license type value just to simulate a completed quote but the license type has nothing to do with the calculation.  In reality you will not have a duplicate license type no matter how many SI rows.

*I am sure that probably confused you even more :)
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I am sure that probably confused you even more
Yup.

Is any of what I suggested for the new button helpful? If so can you build on what I suggested and tell me how it should operate?

Author

Commented:

When clicked, that button would show you a list on the non-discounted SIs in the quote. So for example let’s assume you have row 13 selected. Clicking the button would show you SIs 7 through 10 and you would select 7 and 9.
I like button idea especially being able to select the SI total price I need to apply the discount against 

1. Remove the quantity from rows 7 and 9 
This won’t work because your actually will removing SIs.  How about instead of removing the qty and deleting row 7 and 9, copy the total price  from row 7 and 9, add them together.

2. Make the discount % for them to be 25 
Keep the 25 discount % on its own row as a new discount

 2. Update their total price
The total price on rows 7 and 9 are added exactly as they should be and cannot be alter with a discount change. In order words Discount % will have their  own SI row and no other row value can be changed to reflect

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If the picture you posted here is the "before clicking the new button" state, please show me what it should look like after the button is clicked.

Author

Commented:
If the picture you posted here is the "before clicking the new button" state, please show me what it should look like after the button is clicked.
the link doesn't direct me to a picture so i am not sure if I am following what you want me to show exactly
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sorry. I meant this picture of yours.

Author

Commented:
Still not working
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What specifically is not working?

Author

Commented:
Sorry. I meant this picture of yours. 

The link here
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Okay. You posted this picture
2020-04-16_10-38-58.pngwhich I assume is a "before" picture. Please show me how those SIs should look after the new button is clicked.

Author

Commented:

Actually that was the after picture.  Unless I am misunderstanding what your asking here is the before and after.
Before = prior to the 25% discount
After = [row7_Total Price (9,000)] +[row9_Total Price (2,250)]* 25% discount = new discount SI row13 (2,812.50)


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Thanks for that. Now let's see if I can reproduce it:)

Author

Commented:
I'm hopeful you can reproduce it as well.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How do I know what the contents of the other cells (like the License Type) in the new row should be? Should I just copy what's in row 13?

What should I call this process (for the caption of the new button)?

Can I tell from looking at the SI that the discount calculation should not include row 8? If so then please tell me how. I assume I can't so I'd like the code to ask you to select the rows that should be involved by showing you enough data from the listview so you can be sure you are selecting the right ones. If I do it that way, what data should I show you?

Author

Commented:
How do I know what the contents of the other cells (like the License Type) in the new row should be? Should I just copy what's in row 13?
Why would you not treat line 13 as an "Add New".  That's how I have always handle the discount SIs.  The Quote itself has the discount SI row with the license type information just like a typical quote.  The only difference is that the quote  will have a discount % on Qty or a discount % on total price and note referenced to which SI total price row(s) the discount is applied against.

What should I call this process (for the caption of the new button)?

License Discount

Can I tell from looking at the SI that the discount calculation should not include row 8? If so then please tell me how. I assume I can't so I'd like the code to ask you to select the rows that should be involved by showing you enough data from the listview so you can be sure you are selecting the right ones. If I do it that way, what data should I show you?

Your assumption is correct. I would suggest listview should have at minimum the Ref#, License type name and Total price
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
...and note referenced to which SI total price row(s) the discount is applied against.
Please explain what you mean by that.

Author

Commented:
..and note referenced to which SI total price row(s) the discount is applied against. Please explain what you mean by that. 

I am basically saying that there is a note on the Quote providing information on which SI the discounts are being applied to.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Let me back up a minute and explain step by step how I envisioned quotes like Q-66666666 being created.
Referring to the side-by-side before and after picture above...
  1. You would create the "Before" SIs just like you do now
  2. With the 6th "Before" SI selected (the one with the Total Price of 73,500) you would click the License Discount  button.
  3. The code would then show you the key information from  the first 4 SI (Ref#, License type name and Total price). Each of them would have an associated checkbox that you can use to choose which ones are involved in creating the 7th SI shown in the "After" picture.
  4. When you click "OK", that 7th SI would be created for you. That's why I asked "How do I know what the contents of the other cells (like the License Type) in the new row should be?"
If there's anything that isn't clear the let's talk about it.

Note that I'm not locked into this process and so if you don't think that will work or you'd like it done differently, please explain step by step like I did, how you would like it to work.

Author

Commented:
Your envisioned steps makes sense and I think we should go forward with this.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I think this works, but I tested with my own quote rather that recreating Q-66666666 so let me know what you find wrong or that you don't like. You'll find that when you create the License Discount that a comment will appear in the userform that documents the rows that were used to create it. You'll also find that after creating License Discount that you can't change the discount information and so you'll need to delete and recreate it if you want to change something like the discount%. The reason I've done it that way is that, currently at least, the information on how the License Discount was created (in other words the other SIs that were summed) is not stored in the worksheet and so there's no way to tell what to apply discount to. If that explanation isn't clear then let's not worry about it.

Other changes include:
  • Added a 'Clear' checkbox to the userform
  • Made cosmetic changes to the userform "x of n" records
  • Allowed total price to be seen in userform for Quantity type discount
29177895c.xlsm

Author

Commented:
The changes you made looks to be functioning as expected. I had no issues.  

You'll find that when you create the License Discount that a comment will appear in the userform that documents the rows that were used to create it.

Are you referring to the comment in the screenshot? And if so I am not sure by reading the comment what the action is.

I do feel cosmetically that the Add new and Clear should be reversed. Clear first and then Add new.  I think for clarity purpose the Clear should be rename Clear form. Reason for the change is because when i cleared the form I was able to enter information up through the License Type only to realize after the fact that the Add new was not checked.


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Are you referring to the comment in the screenshot?
No, I was referring to this.
2020-04-20_06-33-38.png
I will make the changes you suggested. I also think that I can modify the code so that the License Discount is editable.

Author

Commented:
OK. Thanks Martin
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In this version I've
  • Reordered the checkboxes in the 'Action' frame and changed "Clear" to "Clear Form"
  • Corrected some listview problems where when adding and deleting rows, the focus was not going to the proper row
  • Made changes to the License Discount process including making its total price a formula and making its discount editable
If you approve of this version I will work on the quote breakdown and possibly other stuff next.
29177895d.xlsm

Author

Commented:
So far this is working out wonderfully.  I hope the breakdown will workout as well especially now that we have pretty much figured out how to apply the discounts. This will save me a tremendous amount of time.



Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In this version I've
  • Deleted the error processing that occurred if the listview was clicked during Add New. That was a very old message and it doesn't seem to be needed now.
  • Corrected bug where the License Discount message always showed all rows rather than just the selected ones
  • Added a new License Discount validation error message if no items were selected while creating a license discount
I could not reproduce the error you reported that involves add new so I need more details about what led up to that problem.
29177895e.xlsm

Author

Commented:
I could not reproduce the error you reported that involves add new so I need more details about what led up to that problem. 
I tested the work book and it still functioning as expected, but there seem to be  an issue with the Add new and the Add service Item buttons when applying total price.
I'm not sure if I can explain this without confusing you even more, but in any case here you go.


Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Remember when I said that the code would add the License Discount for you? So to create one what you should do is
  1. Do NOT click Add New
  2. In the listview, select the last service item in the quote where you want it
  3. Select the Total Price checkbox
  4. Click the ‘Create  License Discount’ button
  5. In the Select Service Items form (it used to say “Find Matches” because I copied the form from another workbook and forgot to change it) and select the SIs you want
  6. Enter the discount
  7. Click OK

After you do that the Total Price is disabled by design because the total is based on the values in other SIs. If you want to change the discount percent, change it in the main form and click Update button. If for some reason the total price still isn’t correct you will need to use the Delete Service Item button to delete it and then redo the steps above.

I think that if you follow the License Discount instructions that you won't have problems and I apologize for not stating them previously.

The attached workbook has some cosmetic changes.
29177895f.xlsm

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks Martin!  I just tested those instructions and discount on total price worked perfectly.  Thanks for the steps and so far everything looks to be working like a charm.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.