VBA to change tab colour using the palette
Results 1 to 7 of 7

Thread: VBA to change tab colour using the palette
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2013
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to change tab colour using the palette

    Hi folks,
    I'm trying to write a macro to allow users to change the colour of a selected tab on a protected worksheet.
    My plan:
    • Use an input box to get the tab name from the user and assign it to a variable
    • Present the user with the colour palette to select the colour (standard or custom) and assign it to a variable
    • Set the tab to the selected colour
    I've been using...
    Code:
    ColorCode = Application.Dialogs(xlDialogEditColor).Show(1, 26, 82, 48)
    ...to present the palette and assign the selected value to ColorCode but it refuses to be equal to anything other than -1.
    I haven't worked with the palette in Vba previously so what I'm trying is probably not correct.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,661
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA to change tab colour using the palette

    Show just returns either False, if the user cancelled, or True. It will not return the selected colour. I think you'd need to create a userform with buttons for each colour.

  3. #3
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to change tab colour using the palette

    If you were happy with 56 colours, then you could use a separate worksheet as your "Pallette" and, in the sheet's _selectionChange event temporarily use:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Interior.ColorIndex = .Row
    End Sub
    select any cell in row 1, then use the down arrow to scroll to 56. This will give you your "Pallette."

    Then build a simple userform with a listbox (or combo) populated with all the sheet names, and the _click event coded to bring up your "Pallette" worksheet (might need to make the userform Modeless for this).
    User selects colour, and row number can be passed back to the userform code, to finish with:
    Code:
    activesheet.tab.colorindex= colourfromsheetselectionvariable
    etc.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #4
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,237
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA to change tab colour using the palette

    You need to retrieve the color from the first colorindex in your workbook

    This worked for me:
    Code:
    If Application.Dialogs(xlDialogEditColor).Show(1, 26, 82, 48) Then
      ActiveSheet.Tab.Color = ActiveWorkbook.Colors(1)
    End If
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  5. #5
    Board Regular
    Join Date
    Feb 2013
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to change tab colour using the palette

    Hi folks,
    thank you all for your efforts on this.
    Jaafar's solution looks to be the simpler one and it does work.
    I do have one question. As I'm not familiar with using the palette in this way does this have a lasting impact on my palette or does it remain unaffected? Probably a silly question but I want to be sure I'm not pemanently changing a red RGB to green or something like that.

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,237
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA to change tab colour using the palette

    The code as is, would affect the color palette of the workbook if the latter is saved... It shoudn't affect the excel default palette for new workbooks.

    You could adapt the code as follows to make sure the workbook palette remains unchanged:
    Code:
    Sub Test()
    
        Dim lColor As Long
        
        On Error GoTo errHandler
        
        'store existing first colorindex color.
        lColor = ActiveWorkbook.Colors(1)
        
        If Application.Dialogs(xlDialogEditColor).Show(1, 26, 82, 48) Then
          ActiveSheet.Tab.Color = ActiveWorkbook.Colors(1)
        End If
        
        
    errHandler:
        'restore initial color
        ActiveWorkbook.Colors(1) = lColor
    
    End Sub
    Last edited by Jaafar Tribak; Jul 17th, 2019 at 05:48 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  7. #7
    Board Regular
    Join Date
    Feb 2013
    Posts
    137
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to change tab colour using the palette

    Excellent,
    thanks again for your help Jaafar.
    Much appreciated.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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