VBA to change tab colour using the palette

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
262
Office Version
365, 2016
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,100
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,660
Office Version
365
Platform
Windows
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.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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
 

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
262
Office Version
365, 2016
Platform
Windows
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.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
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
    
    [COLOR=#008000]'store existing first colorindex color.[/COLOR]
    lColor = ActiveWorkbook.Colors(1)
    
    If Application.Dialogs(xlDialogEditColor).Show(1, 26, 82, 48) Then
      ActiveSheet.Tab.Color = ActiveWorkbook.Colors(1)
    End If
    
    
errHandler:
    [COLOR=#008000]'restore initial color[/COLOR]
    ActiveWorkbook.Colors(1) = lColor

End Sub
 
Last edited:

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
262
Office Version
365, 2016
Platform
Windows
Excellent,
thanks again for your help Jaafar.
Much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,774
Messages
5,482,844
Members
407,365
Latest member
Leah Ashley

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top