Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sheet Tab Colour (Excel 2007)

    Hi,

    I am trying to change the tab colour with a macro and the Appl.Dialogs command. Please help...its not working?!

    Sub Test()
    myC = Application.Dialogs(xlDialogColorPalette).Show
    ActiveSheet.Tab.Color = myC
    End Sub

    How do I get the colour I choose in the dialog to become the tab colour?

    TX
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sheet Tab Colour (Excel 2007)

    Lots of problems:
    1. <LI>You haven't declared the variable myC! <img src=/S/scold.gif border=0 alt=scold width=50 height=15>
      <LI>The result of the Show method of a dialog is True or False, depending on whether the user clicked OK or Cancel. It does *not* return a color or whatever.
      <LI>The xlDialogColorPalette dialog corresponds to the Color tab of the Options dialog. It is not intended to select a color, and hence there is no way to find out which color the user clicked.
    Try Excel Developer Tip: A Color Picker Dialog Box from John Walkenbach.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sheet Tab Colour (Excel 2007)

    Wow...you are strict!!! <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    This was a test macro...just a sample "Mr Vogelaar...my master and teacher". I thought it OK not to Dim???

    OK, jokes aside... Is there no way to choose a colour and have the Tab become that colour? It seems really harsh to have to create / download a custom user form to do this??? What is the value of accessing the dialogs then?
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sheet Tab Colour (Excel 2007)

    If you have set "Require variable declaration" in the Editor tab of Tools | Options..., *each* new module will automatically start with the line Option Explicit, forcing you to declare all variables.

    If you have *not* set "Require variable declaration" in the Editor tab of Tools | Options..., you have been ignoring all my previous warnings!

    The xlDialogColorPalette dialog is *not*, I repeat *not* a color picker. It is the equivalent of the Color tab of the Options dialog. The VBA object model for Excel does not expose a general-purpose color picker, so you'll have to provide it yourself.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sheet Tab Colour (Excel 2007)

    Tx.

    PS: I would *never* ignore your advice. <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>
    My Require variable declaration option is on. I just did not copy that part of the module.
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sheet Tab Colour (Excel 2007)

    Let me just ask (from a different angle)...

    Is there any way in VBA to get a colour picker? (Besides the link that Hans posted above.)

    What about a User Form. Is there a contol that can be added to the toolbox that allows a colour picker to be built onto a user form?

    TX
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sheet Tab Colour (Excel 2007)

    Rory's <!post=Autofilter Highlighter Add-in (All),605725>Autofilter Highlighter Add-in (All)<!/post> contains a module named basColour with a function ShowColor that lets the user pick a color, using the Windows API function ChooseColorA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •