Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

A Fix to the Check Boxes and Math

EE Pros,

Martin Liss has helped me construct a great WS with several Macros.  We are on the last part of this WS "adventure" and it has to do with value recognition.  Presently, if you check the black check boxes, a check mark will appear.   This checkmark signifies that the values in the cells at the bottom of the table (e.g. Cells E12:G12 and E21:G21) is the sum total of only those rows that are selected (not in black).  To see the problem currently, with all check boxes unchecked (not selected) the values in the sum Cells should be -0-.   Check a box, and that value is added to the sum Cells.  Change a value in the table, it should automatically update the sum cells.

That's it!

Thank you in advance.

B.
D--Data-Data-Temp-WS-28820120b.xlsm
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

The problem with the wb is that the bottom totals are wrong to begin with. What I mean is that if you check all of the rows in the top use case the total should be 46% of 200,000 which is 92,000 and not 184,000. Clicking the Update! button corrects the total and it stays corrected. Attached is your wb where I did that for both use cases.
28826919.xlsm
Avatar of Bright01

ASKER

Thanks Martin.

I now think I understand the problem we are having.

The sum value at the top is independent of the formula that creates the sum value at the bottom.  The sum value at the bottom of the table should be calculated using each row's value (% or $).  In other words, it's only the row values of checked rows that get added / summed at the bottom.  So in your example, if all the boxes are checked, the value should = the same as the % sum on the top line * the Question Value.  If none of the boxes are checked then the value would be -0-.  Change a % in the table of an unchecked box, and the top line will reflect it.  Change a % or $ in the table of a checked box and it would be reflected in both.  

B.
Is the sheet supposed to be protected? If so, which cells besides the checkbox cells should the user be able to change?
The sheet does not require protection.  There was a password on it for a time (
"pass") but I can handle protecting the sheet later.  To directly answer your question, when I put password protection on, I will insure that the table is not effected.  

Here is what will not be impacted by protection:

Each Row or Use Case Description (B6:B12 and B14:B21)
The Black Checkboxes (in Col. A)
The value from the question (at the end of the Use Cases), i.e. C12, C21.
The %/$ table (E8:G11 and E16:G20)
The Priority check box  (In Red)

B.
In addition to my questions in the previous post I want to point out out a few things:

The sum value at the top is independent of the formula that creates the sum value at the bottom.
 
There's no formula at the bottom, it's created via code.

The sum value at the bottom of the table should be calculated using each row's value (% or $).  In other words, it's only the row values of checked rows that get added / summed at the bottom.  .
That is the way it's working now.
So in your example, if all the boxes are checked, the value should = the same as the % sum on the top line * the Question Value
It will be if the bottom total is correct when you open the workbook, but as I said above the original totals are wrong.
If none of the boxes are checked then the value would be -0-.
Again,it will be if the initial total is correct.  
Change a % in the table of an unchecked box, and the top line will reflect it.  Change a % or $ in the table of a checked box and it would be reflected in both.
I asked the question in my previous post because based on the fact that in a couple of places the code unprotects and then re-proptects, the sheet I assumed that the user could not manually change the percents or values.
We are cross-posting but if I understand what you are saying, the user can change the E8:G11 and E16:G20 ranges. My code wasn't prepared for that and I'll fix it now if my current understanding is correct.
Another possible problem for another question. Currently the individual percents or dollars or the sum of the percents or dollars can exceed 100% of, or the amount of, the annual cost. Should that be allowed?
Now I'm confused... ;-)

Let me see if we can sort this out  (ANSWERS IN CAPS):


In addition to my questions in the previous post I want to point out out a few things:

The sum value at the top is independent of the formula that creates the sum value at the bottom.
 
 There's no formula at the bottom, it's created via code.

YES.... I SHOULD HAVE SAID CODE INSTEAD OF FORMULA.  BUT THE WAY IT NEEDS TO CALCULATE THE SUM (VIA CODE) IS BY LOOKING TO SEE IF THE ROW HAS BEEN SELECTED.  IF SELECTED, IT THEN ADDS THE VALUE CREATED BETWEEN THE % AND THE BASE $ THAT HAS BEEN INPUTTED (E.G. C12 OR C21).  


The sum value at the bottom of the table should be calculated using each row's value (% or $).  In other words, it's only the row values of checked rows that get added / summed at the bottom.  .
That is the way it's working now.

IT CAN'T BE.  IF I UNCHECK ALL THE BOXES (DE-SELECT THE ROWS) THE SUM DOESN'T REGISTER AS -0-.  EVEN WHEN I UPDATE IT, IT ISN'T -0-.

So in your example, if all the boxes are checked, the value should = the same as the % sum on the top line * the Question Value

 It will be if the bottom total is correct when you open the workbook, but as I said above the original totals are wrong.

If none of the boxes are checked then the value would be -0-.

 Again,it will be if the initial total is correct.  

Change a % in the table of an unchecked box, and the top line will reflect it.  Change a % or $ in the table of a checked box and it would be reflected in both.
I asked the question in my previous post because based on the fact that in a couple of places the code unprotects and then re-proptects, the sheet I assumed that the user could not manually change the percents or values.

We are cross-posting but if I understand what you are saying, the user can change the E8:G11 and E16:G20 ranges. My code wasn't prepared for that and I'll fix it now if my current understanding is correct.

YES.  AND YOUR CODE FOR THE TOP VALUE WORKS FINE.  IT'S THE BOTTOM VALUE THAT NEEDS TO BE FIXED.

Another possible problem for another question. Currently the individual percents or dollars or the sum of the percents or dollars can exceed 100% of, or the amount of, the annual cost. Should that be allowed?

NOW THIS IS A FANTASTIC QUESTION!  YES; MY THINKING IS IT CAN EXCEED 100% SIMPLY BECAUSE IN THE FUTURE, THE VALUES MAY NOT BE COSTS BUT MAY BE CHANGES TO, FOR EXAMPLE, REVENUE.  IF SO, IT COULD EASILY BE OVER 100%.  YOU ARE CORRECT HOWEVER, THAT WHEN TREATING COSTS AS A COST SAVINGS, YOU SHOULD NOT BE ABLE TO GO OVER 100%.  IN THIS CASE, I'M COUNTING ON SMART USERS ;-)

B.
I'm not sure I understand your reply, but let's discuss it more after I post an updated workbook.

BTW another looming problem. The cells in column F in, for example, F8:F11 are currently calculated via a formula and if the user changes them, the formula will be replaced. So either those cells need to be protected, or code can be added to calculate them (an easy fix).
Here's the update; give it a whirl. BTW IMO the black color you are using is too dark.
28826919a.xlsm
Martin,  I've done a fair amount of Testing on this and it is very close.  There is still a problem with the selection (Check Mark Process) and I think it has to do with some error checking.  

Regardless of selecting a row (via the Check Box), the table should update itself consistently (i.e. all % or $) if any change happens to the table itself.  It appears that when you unselect a row, and you update! the table, it updates but it gets out of sync.  In other words, if the table hasn't updated because a row hasn't been selected and you fire the Update Macro, the $ and % are not consistent.

Can you test that and see what can be tweeked to make it consistent?  Please let me know what you change.

Also, after we get this bug fixed, I will be dropping another ASK that provides a way to reset all of the selections ("P", checks) to blank when you reset the model.

Thanks again for all the help here.

B.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Martin,

YES!  Yes! Yes!   That works!  Thank you very much!   Can you tell me which code you changed?  I'm integrating the macros into my production version.

I'm going to author another question to restart the model by removing the check marks.

I'll send you the URL.

Thanks again!  

B.
Yay!

Do you mean what I changed from the a to be versions? If so I believe all I changed was the Toggle sub.
The "Sub Toggle Display"?
No. There are two subs in Module1; one is called Toggle and the other ToggleDisplay and I'm talking about the former.
Got it.  Fixed it.  Works fine.

Here is the next Ask..... should be far easier ;-)

https://www.experts-exchange.com/questions/28835979/Clearing-a-set-of-Ranges-in-Excel.html

B.