Multi Users in Excel

[TedMosby]

I have posted this thread in another forum, but having problem getting a reply to my question

http://www.excelforum.com/excel-programming/678771-multiple-users-to-use-excel.html

I have a table in my workbook that holds the UserID, Name, AccessRights & TimeDateStamp

What I want to do is when the workbook is opened, look in the table and perform the action against that user. Each User has an AccessRight of either ReadOnly or FullAccess.

I want to set the Workbook to ReadOnly if the User is ReadOnly in my table. If they are FullAccess then what I want to do is put a TimeDateStamp in the cell, but if there is someone already in the workbook who is FullAccess then I want to say that person x is in the database and they cant amend until person x has finished what they are doing. (I have forms within the workbook, so when they click Add for example put the TimeDateStamp in and once they click Save then it takes that TimeDateStamp out and another user can do same)

If you are confuesd and would be easier to attach the workbook I have done

[MikeBlackman]

Hi,

Maybe an On_Open event like this;

Private Sub Workbook_Open()
 
x = Replace(Environ("Username"), ".", " ")
 
Call Application.VLookup(x, Sheets("Map").Columns("B:C"), 2, 0)
Sheets("Map").Columns("B").Find(x).Offset(0, 2) = Date
 
End Sub

I have assumed that the sheet with the lookup is called Map. In your permissions you would need to place the Sub names and you could have ReadOnly and FullAccess and within these subs you could protect sheets, lock cells or even close the workbook.

Hope this helps.

[TedMosby]

Hi,

Maybe an On_Open event like this;

Private Sub Workbook_Open()
 
x = Replace(Environ("Username"), ".", " ")
 
Call Application.VLookup(x, Sheets("Map").Columns("B:C"), 2, 0)
Sheets("Map").Columns("B").Find(x).Offset(0, 2) = Date
 
End Sub

I have assumed that the sheet with the lookup is called Map. In your permissions you would need to place the Sub names and you could have ReadOnly and FullAccess and within these subs you could protect sheets, lock cells or even close the workbook.

Hope this helps.

Thanks for that, I need to undestand what its doing as am a little lost. Currently I have a table holding the UserID, Name, AccessRights & TimeDateStamp

What do you mean within my permissions?

[MikeBlackman]

Permissions = Access Rights, sorry.

[TedMosby]

Thanks, I have tried the code, and have got an error...

Run-time error 9
Subscript out of range

Private Sub Workbook_Open()
Dim LogInName As String
'AddIns("Analysis ToolPak").Installed = True
'AddIns("Analysis ToolPak - VBA").Installed = True
'AddIns("Lookup Wizard").Installed = True
LogInName = GetUserID
    Call LogIn
    Call Auto_Open
    ShowHaHMenu
'******************************************************************
    MsgBox LogInName
    LogInName = Replace(Environ("UserID"), ".", " ")
    Call Application.VLookup(LogInName, Sheets("tblUsers").Columns("N:P"), 3, 0)
    Sheets("tblUsers").Columns("N").Find(LogInName).Offset(0, 3) = Date
'******************************************************************
End Sub

The debug highlights the line below as the problem

    Call Application.VLookup(LogInName, Sheets("tblUsers").Columns("N:P"), 3, 0)

Where have I gone wrong?

[MikeBlackman]

Hi,

As far as I can tell you've not defined GetUserID so when you have this line of code;

LogInName = GetUserID

I believe all you are doing is setting LogInName to equal 0.

Hopefully this is the problem.

[TedMosby]

I have this function below to get the UserID, this seems to work ok, as the MsgBox at the start of the code, actually brings back my UserID

Function GetUserID() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    On Error Resume Next
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        GetUserID = Left$(strUserName, lngLen - 1)
        Else
        GetUserID = ""
    End If
    Exit Function
End Function

[MikeBlackman]

But then you re-define the LogInName here;

LogInName = Replace(Environ("UserID"), ".", " ")

If you already can retrieve your LogInName then you probably wont need this line.

[TedMosby]

Ive taken that line out and still the error appears. Can you explain what this line does?

Call Application.VLookup(LogInName, Sheets("tblUsers").Columns("N:P"), 3, 0)

[Bob Phillips]

It passes a loginname to the VLOOKUP and retrieves the value from column P.

Seems a bit pointless if the value is not captured.

[TedMosby]

Ok, So I have LogInName (UserID) in Column N, and the AccessRights in Column P, therefore I want to put the TimeDateStamp in Column Q if the AccessRights = FullAccess.

Will this do that?

   Call Application.VLookup(LogInName, Sheets("tblUsers").Columns("N:P"), 3, 0)
    Sheets("tblUsers").Columns("N").Find(LogInName).Offset(0, 3) = Date

[MikeBlackman]

Hi,

Probably something more like this to check the value of the access;

Call Application.VLookup(LogInName, Sheets("tblUsers").Columns("N:P"), 3, 0)
If Sheets("tblUsers").Columns("N").Find(LogInName).Offset(0, 3) = "Full Access" Then _
    Sheets("tblUsers").Columns("N").Find(LogInName).Offset(0, 4) = Date

[TedMosby]

Excellent, now thats worked. I have added code onto it to say if they are ReadOnly then switch the Workbook to ReadOnly.
How can I stop it asking my if I want to save before switching to ReadOnly. Also, how is it possible for if another User Opens the workbook who is a FullAccess user to look through the Column Q where the TimeDateStamps are and bring a msgBox to say that they cant edit at this given time because a FullAccess user currently has control

    Call Application.VLookup(LogInName, Sheets("Lookups").Columns("N:P"), 3, 0)
        If Sheets("Lookups").Columns("N").Find(LogInName).Offset(0, 2) = "Full Access" Then _
            Sheets("Lookups").Columns("N").Find(LogInName).Offset(0, 3) = Date
        If Sheets("Lookups").Columns("N").Find(LogInName).Offset(0, 2) = "Read Only" Then _
            ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly

[MikeBlackman]

Hi,

Possibly at the start of the code;

 
Application.DisplayAlerts = False

And at the end;

 
Application.DisplayAlerts = True

[MikeBlackman]

I assume this is a shared workbook? If thats the case i'm not sure, if it wasn't shared then the second person to go in would only get read-only anyway. Maybe this is the same with Shared workbooks?

[TedMosby]

It will be used by a number of people, but I am trying to get around using the SharedWorkook option in Excel as I want to determine who is ReadOnly and not when they Open the workbook.

Am I trying to do the impossible?

[MikeBlackman]

Not at all, Is this not a sharedworkbook then?

[TedMosby]

No its set as a Standard Workbook, but how I want to use the workbook is similar to Shared in the way that there maybe more than one user opening the workbook at same time, but the likelyhood that 2 people who have FullAccess will open at same time.

[MikeBlackman]

If a user opens a standard workbook it will open as read only for anybody else, when the first user closes it, it shouts at the 2nd user to tell them they can begin editing, are you not just re-creating what excel already does?

[TedMosby]

In a way I am, but what I am doing or want to do is determine that from the table of users instead of Excel doing it. eg if they are listed as ReadOnly then it sets them as ReadOnly regardless if anyone else is in it. My only problem is that when it comes down to the FullAccess Users.

I'll type out my idea for them.