Finalizing a Macro for Selection and Conversion
EE Pros,
Martin Liss has been gratious in working with me through a very complex Macro. He has advanced this to the next stage.
Here is what I need.
There are two separate Macros.
One macro "watches" to see if a row is selected by a doubleclick that results in a check mark. If it is selected, then the math is to take the cell in the % / $ Table and apply the $ value to the bottom cell at the base of the Table.
The second Macro simply, at the push of the Button (blue), converts all the table values from % to $ and back again with another push of the button.
Currently, the selection process in column A results in not converting the % to $ (and vice versa) in the table; nor does it update the Sum at the bottom.
B.
D--Data-Data-Temp-Macro-Fix-for-288.xlsm
Martin Liss has been gratious in working with me through a very complex Macro. He has advanced this to the next stage.
Here is what I need.
There are two separate Macros.
One macro "watches" to see if a row is selected by a doubleclick that results in a check mark. If it is selected, then the math is to take the cell in the % / $ Table and apply the $ value to the bottom cell at the base of the Table.
The second Macro simply, at the push of the Button (blue), converts all the table values from % to $ and back again with another push of the button.
Currently, the selection process in column A results in not converting the % to $ (and vice versa) in the table; nor does it update the Sum at the bottom.
B.
D--Data-Data-Temp-Macro-Fix-for-288.xlsm
Martin Liss
I'll finish this tomorrow unless someone else gets here first.
Actually here's a first draft. Double-click the cell(s) in column A and tell me what it's doing wrong. I'm not sure if I should change the format of E:G when that happens.
28820120.xlsm
28820120.xlsm
ASKER
Martin,
When I change the status of Column A, I'm not sure what it is doing in the table. Also, when I fire the Button, I it doesn't change the values consistently across all table values.
I have an idea here. Let's break this down into two EE Asks. This being the first. After we get this completed, I'll close this out and do a separate ask on how to treat the selection in column A. That may make it simpler.
First disable the Macro that deals with selecting the rows in Col. A, with the doubleclick and checkmarks. The selection and what we do with the values selected will be part of the second ask.
I have added two range names to make this both easy and adaptable in the future. These two range names are "Table1 and Table2" and represent only the values we are working with for this macro.
Here is the first Ask; The User is going to enter values in either % or $ into each Table. The Cells at the top of the Table (E6, F6 and G6) will be the SUM of the values below, down to the last cell which is not part of the Tables. All values will be in the table in either $ or % consistently displayed as you fire the Macro back and forth. When you fire the Macro Button, it should simply change the values entered to a % or a $ .... back and forth as you press the button. There should be no condition where part of the table is in % and the other part is in $. That's it.
Thank you very much for hanging in there on this. We'll get this done.
B.
When I change the status of Column A, I'm not sure what it is doing in the table. Also, when I fire the Button, I it doesn't change the values consistently across all table values.
I have an idea here. Let's break this down into two EE Asks. This being the first. After we get this completed, I'll close this out and do a separate ask on how to treat the selection in column A. That may make it simpler.
First disable the Macro that deals with selecting the rows in Col. A, with the doubleclick and checkmarks. The selection and what we do with the values selected will be part of the second ask.
I have added two range names to make this both easy and adaptable in the future. These two range names are "Table1 and Table2" and represent only the values we are working with for this macro.
Here is the first Ask; The User is going to enter values in either % or $ into each Table. The Cells at the top of the Table (E6, F6 and G6) will be the SUM of the values below, down to the last cell which is not part of the Tables. All values will be in the table in either $ or % consistently displayed as you fire the Macro back and forth. When you fire the Macro Button, it should simply change the values entered to a % or a $ .... back and forth as you press the button. There should be no condition where part of the table is in % and the other part is in $. That's it.
Thank you very much for hanging in there on this. We'll get this done.
B.
ASKER
Sorry....here is the file with the additional/new Range Names.
B.
D--Data-Data-Temp-Copy-of-28820120.xlsm
B.
D--Data-Data-Temp-Copy-of-28820120.xlsm
I hope you don't mind but I'd rather fix my code for your original request instead of going off on a new route, so tell me if this is better.
28820120a.xlsm
28820120a.xlsm
I don't know if you've tried the above yet but when you do you'll notice then when you click the macro button only the rows that are checked change their format. Is that correct or should all rows change their format? If it's the latter than it should be an easy fix.
ASKER
Martin,
Great work! And I don't blame you for not using my code ;-)
All rows should change their format. Only the total at the bottom should change when a row is selected. And I think you've got that.
The change I had made in my code was to add two range names for the tables so if I added a line/row, it would automatically work.
Finally, I will author another question after this one. For one other capability.
Much thanks! If you could fix the "all rows", that would be great.
b.
Great work! And I don't blame you for not using my code ;-)
All rows should change their format. Only the total at the bottom should change when a row is selected. And I think you've got that.
The change I had made in my code was to add two range names for the tables so if I added a line/row, it would automatically work.
Finally, I will author another question after this one. For one other capability.
Much thanks! If you could fix the "all rows", that would be great.
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,
Great work! This continues to be a very complex request but you have handled it extremely well. Here is what I found in testing;
1.) The conversion in the table works correctly. It's great. When you add a value to the table ($ or %) it properly converts it.
2.) I like your idea of a better grayed out box. I'll modify it accordingly. Thanks for putting in the comments!
3.) The Sum is updated only when the Button Macro is fired. This is fine. I'll be authoring another question to refine this.
4.) There is a "Button" in gray that's on the sheet. What's it for? May I erase / delete it?
5.) The check buttons don't completely work correctly yet…. But as I said, let's close this one out and I can give better requirements for the next iteration.
As always, you are a real pleasure to work with.
B.
Great work! This continues to be a very complex request but you have handled it extremely well. Here is what I found in testing;
1.) The conversion in the table works correctly. It's great. When you add a value to the table ($ or %) it properly converts it.
2.) I like your idea of a better grayed out box. I'll modify it accordingly. Thanks for putting in the comments!
3.) The Sum is updated only when the Button Macro is fired. This is fine. I'll be authoring another question to refine this.
4.) There is a "Button" in gray that's on the sheet. What's it for? May I erase / delete it?
5.) The check buttons don't completely work correctly yet…. But as I said, let's close this one out and I can give better requirements for the next iteration.
As always, you are a real pleasure to work with.
B.
4) The button was in the workbook you originally posted in this thread. A that time it said "Summary" and when clicked it collapsed the detail rows. So it doesn't affect anything I did.
5) What's wrong???
5) What's wrong???
ASKER
So something went wrong with the "Button". The Button was originally there to collapse an outline. Somehow, it generated 9 levels of "Buttons" (not sure how). Now if I delete all 9 levels, back down to my original button, I get an error when I fire the Macro Button you set up for me. Not sure how to fix that.
I have to do some more testing on the selection process but I wanted to close the question out for you since you have done such a great job with getting this to where it is.
B.
I have to do some more testing on the selection process but I wanted to close the question out for you since you have done such a great job with getting this to where it is.
B.
ASKER
Martin,
Here is the post.
I wanted to have the Ranges so I could add rows. Isn't that the correct way to do it without adding more code to the Macro? Somehow this screwed up the original Macro for expanding and collapsing the outline format.
https://www.experts-exchange.com/questions/28824879/Fixing-a-Macro-Button.html
Here is the post.
I wanted to have the Ranges so I could add rows. Isn't that the correct way to do it without adding more code to the Macro? Somehow this screwed up the original Macro for expanding and collapsing the outline format.
https://www.experts-exchange.com/questions/28824879/Fixing-a-Macro-Button.html