Bright01
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
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
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.
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?
ASKER
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.
"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:
There's no formula at the bottom, it's created via code.
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 ValueIt 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?
ASKER
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.
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).
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
28826919a.xlsm
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Do you mean what I changed from the a to be versions? If so I believe all I changed was the Toggle sub.
ASKER
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.
ASKER
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.
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.
28826919.xlsm