libreofficelibreoffice-calclibreoffice-basic

If (Windows-)Username equals NAME then change Background-Color from Cell


I'm using LibreOffice-Calc and I want to "read" the Windows-Login username. If a user opens the spreadsheet, it should change the specific Cell-Background-Color to yellow.

In the Spreadsheet I have 12 tables (January...December or "0-11"). In every table there are many names (not the windows username) in column "A", like cell A2 = John Paulson.

If the (Windows-)User "johnp" opens the spreadsheet, the row A2 till Z2 should have the background color yellow.

This is a very basic start what I want to achieve:

Sub selectUserrow

    Dim oUser As String
    Dim oTable As String '??
    Dim oColor As String '??
    oUser = Environ("USERNAME")
    oTable = 0-11 '??
    oColor = '?? .fillcolor=rgb(255,255,0)
    If oUser = "johnp" Then
        [...change cell-bg-color to yellow (in all tables 0-11) from A2 till Z2...] '??
    ElseIf oUser = "susanm" Then
        [...change cell-bg-color to yellow (in all tables 0-11) from A3 till Z3...] '??
    Else
        [...do nothing...] '??
    EndIF   

End Sub

Thank you!


Solution

  • As @JohnSUN posted in a comment:

    ...it would be easier to move the table of name correspondences to an additional thirteenth sheet and make it hidden? In this case, you will only need the Environ("USERNAME") line in the simple UDF and conditional formatting using the formula with VLOOKUP()

    I created a new sheet with all Windows-Logon-Names in the column A. Beside, in the column B I wrote the full name of the user.

    In the "main" sheet I'm fetching all names with the VLOOKUP()-function. How this function works, is described here. I hide the column A with all win-names in the main sheet to avoid irritations. So the full name is displayed in column B.

    So on, I added following function in the macro-editor of the document:

    Function USERNAME()
    USERNAME = Environ("USERNAME")
    End Function
    

    With that function I can call the Windows-Logon-Name with =USERNAME().

    The last step was to add a conditional format to each row. I achieved that with the Formula is-Condition. And in the right field a wrote $A1=USERNAME(). For the second row it was $A2=USERNAME(), and so on. I choose the desired style and in the field Range I selected the row (eg. A1:Z1) which should have the background-color.