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
Bright01Asked:
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:
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
0
Bright01Author Commented:
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.
0
Martin LissOlder than dirtCommented:
Is the sheet supposed to be protected? If so, which cells besides the checkbox cells should the user be able to change?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Bright01Author Commented:
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.
0
Martin LissOlder than dirtCommented:
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.
0
Martin LissOlder than dirtCommented:
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.
0
Martin LissOlder than dirtCommented:
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?
0
Bright01Author Commented:
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.
0
Martin LissOlder than dirtCommented:
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).
0
Martin LissOlder than dirtCommented:
Here's the update; give it a whirl. BTW IMO the black color you are using is too dark.
28826919a.xlsm
0
Bright01Author Commented:
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.
0
Martin LissOlder than dirtCommented:
If at first you don't succeed, test, test again.
28826919b.xlsm
0

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
Bright01Author Commented:
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.
0
Martin LissOlder than dirtCommented:
Yay!

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

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

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

B.
0
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 Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.