VBA environment variables

  • I'm trying to create a worksheet that when filled out, will compared a cell labeled "Managers Signature" to the current logged in user Environ("UserName"). I'm using the following code:

    Code
    Private Sub Worksheet_signature()
        Dim UserName As String   'the name of the current user
        UserName = LCase(Environ(UserName))   'on opening, find out who this is and convert to lower case
        If LCase(Cells(34, 4).Value) <> UserName Then   'compare username to username in signiture field
        MsgBox ("Manager Signature does not match the user filling out this form.")   'Display error message box
        End If
    End Sub


    I'm new to VBA in excel, so I'm probably missing something simple. I'm use to C++, so trying to learn VBA code. File is saved as an xlsm file.

  • Re: VBA environment variables


    The ENVIRON setting names are strings, exactly as writte in your message text...

    Code
    UserName = LCase(Environ("UserName"))


    You omitted the delimiters in the code.


    This is a good example of why you do not name variables (Username) the same as VBA keywords or other 'System' settings - it gets a little confusing trying to sort errors like that


    You also seem to realise VBA is case sensitive by default. Converting everything to upper/lower case to compare strings is a PITA and one day, in the most important procedure of your programming life, you're going to forget to convert and your wonder application is going to fall over - probably while it is being demonstrated to the MD.


    You can make string comparisons case insensitive by including

    Code
    Option Compare Text

    in the General Declarations section of your module/userform/class module. See Option Compare Statement

  • Re: VBA environment variables


    Ok, so I got the code working, now i want to allow myself and select other people (IT) to be allowed to save the document with the managers field blank since we update the document. As it is right now, I have it setup the following way to allow me to update it.


    Code
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Dim UserName As String   'the name of the current user
        UserName = LCase(Environ("UserName"))   'on opening, find out who this is and convert to lower case
        If UserName = "MyUserName" Then   'checks if user is MyUserName
        Cancel = False   'If the above is true, the allow save while signiture field is blank
        ElseIf LCase(Cells(9, 4).Value) <> UserName Then   'compare username to username in signiture field
        MsgBox ("Manager Signiture does not match the user filling out this form.")   'Display error message box
        Cancel = True    'prevent save if signature doesn't match
        End If
    End Sub


    Is there an easier way to do this check (lines 4 and 5) or do I need to add each user in there with or statements?


    Lastly, I want to make certain fields required to be filled out. I'm thinking I could do something similar to the above and allow certain usernames to allow a save, but how do I go about creating a global array that all of my code can read from then and be able to store a list of usernames in? I don't want to have to edit the usernames in each set of code each time something changes.


    If there is an easier way to require a field to be filled out and allow IT to save them blank, that would be helpful as well.

  • Re: VBA environment variables


    You need to decide how these users who can save without filling in the data are to be identified.


    It could be as simple as asking for a password if the cell(s) are blank when the file is saved or as complex as you can dream up; but, in general, it would be best to avoid any hard-coded lists of users which have to be edited every time someone leaves or is hired.

  • Re: VBA environment variables


    I have employed a lookup worksheet which lists users and their roles. You could, depending on your paranoia level, make the sheet "very hidden".
    Then you can code the behaviour based on the user's role.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!