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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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:
I'll finish this tomorrow unless someone else gets here first.
Martin LissOlder than dirtCommented:
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.
Bright01Author Commented:

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.

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Bright01Author Commented: is the file with the additional/new Range Names.

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

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.

Martin LissOlder than dirtCommented:
In the attached all rows maintain the same format. I added a new wrinkle; when the row isn't checked the cell values are grayed. It's done via this code in the sheet's double-click event and you can modify it as instructed to lighten or darken the color, or delete those lines all-together.
        ' Grey out unchecked rows
        If Cells(Target.Row, "A") = "P" Then
            ' White
            Range("E" & Target.Row & ":G" & Target.Row).Interior.Color = RGB(255, 255, 255)
            ' Light gray. To make the gray darker, decrease the numbers (black is 0,0,0) but
            ' keep the three values the same.
            Range("E" & Target.Row & ":G" & Target.Row).Interior.Color = RGB(230, 230, 230)
        End If

Open in new window

If you like we could go a step further and have it look like L10.

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:

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.

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

Bright01Author Commented:

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