transparent command button vba

  • When I create a command button manually and set it as transparent it is. But the code below does not make it transparent, even though the properties say it is.


  • Re: transparent command button vba


    You have put your creation code inside the Worksheet Activate Event procedure. This means it will be called each and every time you activate that worksheet (providing you are not in Design Mode), each time adding a new command button in the same location. You rename each button created to CommandButton1. I would have expected this to cause an error since it is creating multiple objects with identical names. However this does not throw an error, but the consequences of this you see later.

    When you access the OLEObjects collection with the name "CommandButton1" it only returns the first one. Which it correctly changes the BackStyle and Caption for.

    Each subsequent button created each time you Activate the Worksheet is created over the top of the first, but only the very first one has the BackStyle and Caption changed.

    It is not good practice to create an object each time you Activate a Worksheet unless you also delete it or set a limit to the number of objects you create. It is also not good practice to rename objects to the same name even if it is not causing an error in VBA.

  • Re: transparent command button vba


    Thnaks for your reply. I only had the code in the worksheet activate event for testing. I deleted the button after each run. In properties, it says transparent but it is not. If I create the button manually it is transparent. Would someone mind running the code and see if you get the same result. I am using Excel 2003 with all service packs, looks like a bug to me.

  • Re: transparent command button vba


    I tested your code before I gave the explanation of why it could fail. Loaded into a new Workbook it runs exactly as written.

    The only other error that will occur with your code as posted is if you don't have the reference set for the Microsoft Forms 2.0 Object Library, but that would be reported as an error by debug as a Compile error of "Variable not defined", with fmBackStyleTransparent highlighted. Which you did not mention.

    If there is any control on your sheet with the name CommandButton1, even if it has been created manually, then the last two lines of this code will only change that control again giving the appearance that it is not working. Keep in mind that unless it already exists, CommandButton1 is the default name for the first CommandButton created for that session.

  • Re: transparent command button vba


    Hi numcrun,
    Recorder writes.


    Regards, junho

  • Re: transparent command button vba


    I just ran the code on a new workbook on a different PC running Excel 2000, and got the same result, a white commandbutton instead of transparent. Then I created a commandbutton manually and it was transparent when set to be so. Transparent means you can see the cells behind the commandbutton. Can someone please try it on Excel 2000 or 2003 like me.

  • Re: transparent command button vba


    I just found the answer on another forum it's Shaperange.Fill.Transparency = 1#. Which is also in Junho's code above. Not sure what the fmBackStyleTransparent does. Thanks to both of you.

Participate now!

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