Get Color from Color Fill on Formatting toolbar

  • Re: Get Color from Color Fill on Formatting toolbar


    I still think you are off-track guys.


    The OP said, and I quote, ... It will highlight the active cell upon its selction. I can set the code to whatever color I want, but I would like it to use the color set on the menu bar. Does anyone know how to do this? ...


    In other words, he wants to pick up the fill colour on the menu bar, on some worksheet change event. By building a solution that uses a colour in some other cell, you are making it static when he asks for it dynamic. You might say, make the loading of that cell dynamic, but how? There is no event that traps a change in the colour in the Fill color button, so you can't.


    The only solution that I can see is by using my function, or something like it, and call that from the event code. Not pretty, as it needs to iterate through the colours, and it assumes the palette will not be modified because that creates the horrible 'Color Scheme' tooltiptext, but it is dynamic, and can pick up the current colour regardless of that being changed.


    That is my point, namely that RoyUk's suggestion was driving away from the answer, which has been added to. Adding this function to this alternative solution is pointless IMO, what does it gain to go via some routine that stores it in a cell rather than get it directly.

  • Re: Get Color from Color Fill on Formatting toolbar


    Bob, no one has said that the suggestions are away from the original intent of the OP. And I think we all agree it's not a pretty thing to do but if that's the way he/she wants to do it, then there seems to be limited ways of doing it.


    When you've been here for awhile you'll see that some things get chewed around a bit because it gets everyone thinking about alternative ways not only to accomplish what the OP wants but better or different ways.


    The OP is free to pick your solution, or Barry's solution, or Roy's solution, or none of them.


    Now, if the OP was paying us to give him code then the customer is definitely right. You would discuss with them why it's probably not a good idea, you might suggest alternative ways to do what they trying to do, but in the end, whoever is paying the bills has the last say.


    Any of you "guys" seen any money from this thread yet? :)

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Get Color from Color Fill on Formatting toolbar


    Et al.


    Well unless someone posts before I do I'll be post 43. The last post from the originator was 20, and that was 1 day ago. When he/she comes back there will be plenty of suggestions to select from. Or he/she will just have to repost the question and be a bit more succient.


    Bob both of our methods are essentially static. Your function does get him the color that is currently selected in the color fill button. It then will return the color index of the last picked color. Of course you noted its limitation if someone has loaded a different color palatte. Of course you'll note that my post said that this was one of the two methods but I didn't know how to accomplish it.


    In the end, IMHO, like Barbara or was it Roy there's more than one way to peel an orange ( trying to go easy on the cats here ). While you're suggestion works as long as the color palatte hasn't been altered, it fits the users question as asked.


    However, what would happen if/when the workbook is opened with some other color palatte than the default. It wouldn't work. With a reference cell set to the color, like my method it will always work. The only issue is that it doesn't use the Color Fill control as asked.


    So suffice to say that here at Oz if a question isn't clear be prepared for a deluge of opinions. I suggest we just wait for the original poster come back and take his/her pick.

  • Re: Get Color from Color Fill on Formatting toolbar


    Quote from iwrk4dedpr

    Bob both of our methods are essentially static. Your function does get him the color that is currently selected in the color fill button. It then will return the color index of the last picked color. Of course you noted its limitation if someone has loaded a different color palatte. Of course you'll note that my post said that this was one of the two methods but I didn't know how to accomplish it.


    iwrk4dedpr,


    Firstly, I prefer to use names, but as I don't know yours I have to use your handle. I apologise for that.


    Secondly, this will be my last post on this topic as it is going nowhere IMO, but of course that is my view, you are obviously free to take your own counsel.


    I disagree with you that the OP has not been clear. I originally mis-interpreted him, but he took pains to re-explain, and even provided a littel sketch (flawed, I agree, but quite clear in his intent). Andy Pope also commented on Barbarr's analysis, drawing the same conclusions as I did.


    Mine works, but could give an inconclusive result if the colour palette is changed. It wasn't meant as a solution, but as a technique to deploy in the solution, read my posts you should be able to see that. RoyUK's and yours puport to be soluition, but only work whilst that activecell reflects the current fill colour. So that still leaves the problem of getting the fill colour to that cell, which is not addressed. AS such, this is a diversion, a mis-leading diversion that should be pointed out in case the OP thinks it works for him. Alternative solutions are good I agree, there are always multiple solutions, but alternatives should provide a different way to get to the end position, not getting to a completely different position.

  • Re: Get Color from Color Fill on Formatting toolbar


    iwrk4dedpr's name is clearly in his signature - Barry.


    As I said in Post #40 Bob's code appears (we don't know unless the OP gets back to us) to get the colour as requested. Combining with Barry's Shange_Event code might well be how the OP wanted to use the code.


    I would lock this post but it would be nice for the OP to get back to us.

  • Re: Get Color from Color Fill on Formatting toolbar


    Quote from royUK

    iwrk4dedpr's name is clearly in his signature - Barry.


    So it is, apologies to Barry.

  • Re: Get Color from Color Fill on Formatting toolbar


    WOW!


    I cant believe all the help, ideas and concepts you guys have come up with to help me out.
    My apoligizes on not clearly defining my question and intent better at the beginning of the post. I am currently still working on the solution and will have my add-in ready by the end of the day. With all of your help I think I have it figured out.


    Sorry for the 2 day lapse, but I am sure ya'll understand that "life happens."
    Thank all of you very much.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Get Color from Color Fill on Formatting toolbar


    Will do! Haven't started yet, it got re-priortized by the "man."

    [SIZE=2]I should change my name to STUMBED![/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!