PDA

View Full Version : Multi Users in Excel



TedMosby
04-07-2009, 05:48 AM
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 (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
04-07-2009, 06:06 AM
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
04-07-2009, 07:20 AM
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
04-07-2009, 07:46 AM
Permissions = Access Rights, sorry.

TedMosby
04-08-2009, 01:35 AM
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
04-08-2009, 01:41 AM
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
04-08-2009, 01:50 AM
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
04-08-2009, 02:21 AM
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
04-08-2009, 02:28 AM
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
04-08-2009, 03:10 AM
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
04-08-2009, 03:29 AM
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
04-08-2009, 04:59 AM
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
04-08-2009, 06:02 AM
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
04-08-2009, 06:08 AM
Hi,

Possibly at the start of the code;



Application.DisplayAlerts = False


And at the end;



Application.DisplayAlerts = True

MikeBlackman
04-08-2009, 06:09 AM
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
04-08-2009, 06:19 AM
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
04-08-2009, 06:35 AM
Not at all, Is this not a sharedworkbook then?

TedMosby
04-08-2009, 07:06 AM
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
04-08-2009, 07:11 AM
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
04-08-2009, 07:21 AM
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.

MikeBlackman
04-08-2009, 07:57 AM
Hi,

Maybe a loop like this for the second user?


If Sheets("Lookups").Columns("N").Find(LogInName).Offset(0, 2) = "Full Access" Then
Do Until ActiveWorkbook.ReadOnly = False
ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite
Loop
End If

I'm still not convinced its a good idea to re-create something thats already there :)

TedMosby
04-08-2009, 02:20 PM
I dont really want to recreate what is already there, I just want to give certain people access levels for the workbook, due to the requirements of the project I have been given.

When it comes to a FullAccess User I am looking to do is this. When a FA User opens the workbook it scans the column Q to see if there is a date in the TimeDateStamp, if there is then I want it to say they cant edit until the other user has finished. I am then looking to have a refresh so every couple of minutes the refresh runs to see if there is any FA User still in the workbook, if not allow the other FA User to revert from the ReadOnly into the FullAccess so that they can edit/amend etc :)

TedMosby
04-12-2009, 01:53 PM
Does anyone have any ideas on this?