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

Question

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!

Answer 1

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.

Answer 2

A possible solution is to provide the value of an environment variable via a macro. This makes such values accessible to the spreadsheet's formulas.

Function Env(Name as String)
    Env = Environ(Name)
End Function

My idea behind this function is to provide a universal access to all environment variables of the system.

Then add a column with the user name to each table to format. You might want to hide it to avoid irritations. Whether these user names are manually entered or obtained by other means, does not matter.

This might look like this (due to my laziness I used column A, you can use any you want):

Finally add a single conditional format for your complete range to color, in my example:

Condition 1:

• "Formula is" - $A1=ENV("USERNAME")
• choose the prepared style

Range:

• A1:Z1048576

This works, because the $A1 in the formula is a column-absolute and row-relative address. For cell A1, it is actually A1, for A2 it is A2, and so on. Since the column has the dollar, it is A1 for cell B1, and cell C1, and so on. Last example, for cell H7 the $A1 of the formula addresses A7.

---

Note: To keep my privacy, I used ENV("SESSIONNAME"). Therefore the yellow background is in the row with "Console"...