Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and exploring the core foundations for app libraries.

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

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.

"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.

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.

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.

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).

28826919a.xlsm

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.

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 trialYES! 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.

Do you mean what I changed from the a to be versions? If so I believe all I changed was the Toggle sub.

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.

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

28826919.xlsm