[Solved] Change Active Sheet Tab Color by Cell Value

[Gonzo714]

I located this macro here https://ask.libreoffice.org/t/how-to-se ... nt/64514/7
but it would be nice if it works for .Activesheet only instead of the entire file.

Code: Select all

sub set_tab_colour_by_cell_value()
    all_sheets = ThisComponent.Sheets ' get all sheets
    ' iterate over sheets, looks like in the weird Basic it's safer to do it this way
    for i = all_sheets.getCount()-1 To 0 Step -1
	    a_sheet = all_sheets.getByIndex(i) ' get a sheet
	    cell_location = "A1" ' cell value to be used to set the tab colour
	    cell_value = a_sheet.getCellRangeByName(cell_location).Value ' get the cell value
	    new_colour = -1 ' -1 is the default colour
	    if cell_value < 0 then
		    new_colour = RGB(255, 0, 0) ' red
	    elseif cell_value > 0 then
		    new_colour = RGB(0, 255, 0) ' green
	    endif
	    a_sheet.tabcolor = new_colour ' set the tab colour
    next
end sub

Also, instead of numberical values, text values like ORDER, PO, SHIPPED and INVOICE would be perfect, but changing cell value to "ORDER" etc doesnt do the trick.
I found this for VBA, but of course it wont work for LO

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H11").Value = "Order Sent" Then
    Me.Tab.Color = RGB(255, 192, 0)
ElseIf Range("H11").Value = "Order Confirmed" Then
    Me.Tab.Color = RGB(16, 124, 16)
Else
    Me.Tab.Color = RGB(212, 46, 18)
End If
End Sub

yes manual tab color change is possible, by why not be creative, right?

Debian 12 with LO 7.5

[Lupp]

You get the sheet you want to work with by

Code: Select all

mySheet = ThisComponent.CurrentController.ActiveSheet

You can get the nextTabColor from any cell accessing the .CellBackColor or .CharColor or as a result of the RGB() function, or ...

Code: Select all

mySheet.TabColor = nextTabColor

will then set the new tab color just for the active sheet.

[Gonzo714]

I altered as follows, with cell A1 being blank as its default color, but no matter the cell value, all I get i consistant Black for my tab color

Code: Select all

sub set_tab_color_by_cell_value()

mySheet = ThisComponent.CurrentController.ActiveSheet

cell_location = "A1" ' cell value to be used to set the tab color
cell_value = mySheet.getCellRangeByName(cell_location).Value ' get the cell value
new_color = "" ' the default color
if cell_value = "ORDER" then
new_color = RGB(255, 0, 0) ' red
elseif cell_value = "PO" then
new_color = RGB(0, 255, 0) ' green
elseif cell_value = "SHIPPED" then
new_color = RGB(255, 255, 0) ' yellow
endif
mySheet.TabColor = nextTabColor ' set the tab color

end sub

I even tried as default color
new_color = RGB(255, 0, 255) ' the default color
with no luck

[Lupp]

You didn't read the code.
In the code posted above nextTabColor is a variable without any assignment.
It will be interpreted with the value 0, and used as a RGB value this means black.
Nested IFs are a special problem. I would suggest to prefer a SELECT construct.
See attached example. It contains working code.

aoo110770sheetTabColors.ods

(11.7 KiB) Downloaded 250 times

[Gonzo714]

That works nicely, i can adjust to fit my needs,
I appreciate the effort.