Multiple Users

V

vecia

Hi,

I have a workbook with two sheets in it. There are two sets of peopl
who use the workbook and I would like to be able to determine whic
sheet a user can see when they open the workbook (wouldn't matter i
they could see the other sheet as long as they couldn't modify it).

I thought about having two seperate workbooks and linking them bu
there are a lot of formulas in the sheets which would rely on the othe
workbook being open, so it's much easier if they are separate sheets i
the same workbook.

It also needs to be not too complicated for the users as they know eve
less about excel than I do.

Please help
 
F

Fherrera

There are a couple of ways to approach this... the way I see it. Yo
can create an on workbook_open() event which is run whenever th
workbook is opened, check which user is accessing the workbook and fro
there either hide whichever workbook needs to be hidden.

You can use either the login name of the user (winnt login name o
winxp) by getting the string:

Code
-------------------

Function GetUser
GetUser = Environ("Username")
End Function

-------------------


more here
http://www.tek-tips.com/search.cfm?action=Search&spid=707&requesttimeout=75

To learn about events, try this page:

http://www.cpearson.com/excel/events.htm

You should save a backup before trying this just in case...

Essentially, you'd like to go into the visual basic editor (ALT-F1
key) Double click on the ThisWorkbook on the top left (projec
explorer window)

In the main code window there will be a (General) drop down menu and
(Declarations) drop down menu. From the General one choose workbook.
Then from the declarations choose open (or it may already be chosen).



Code
-------------------

Private Sub Workbook_Open()
Dim uName As String
uName = Environ("USERNAME")

If uName = "fherrera" Then
Worksheets("Sheet1").Visible = xlVeryHidden ' hide it
Worksheets("Sheet2").Visible = True ' show it
ElseIf uName = "username2" Then
Worksheets("Sheet1").Visible = True ' show it
Worksheets("Sheet2").Visible = xlVeryHidden ' hide it
Else ' show both
Worksheets("Sheet1").Visible = True ' show it
Worksheets("Sheet2").Visible = True ' show it
End If

End Sub

-------------------


Here the Environ("USERNAME") would correlate to the log in id (to ge
into windows) alternatively you can use this:

uName = Application.UserName

which would then give the name Excel is registered to.. the user..

In anycase you would change the two if statements to be the name o
these two users in question. and sheet1 and sheet2 to the respectiv
sheets that you want to hide/show.

Hope this helps
 
M

Mikki

I actually use (and prefer) 2 separate workbooks for this
situation. I have one workbook that has about 15
different worksheets and about 3 people that need to use
them. Each month, I start with my ORIGINAL which has all
of the worksheets. I create a new workbook for each user
and just DRAG (not copy) the tab they need into their own
workbook. All the links readjust and it doesn't matter
if any of the workbooks are closed. I keep the ORIGINAL
because I have a summary sheet that pulls totals from all
of the sheets. It still goes out and makes that link (at
least I haven't noticed that there is trouble). And, at
the end of the month when everyone is done, I just drag
the tab back into my original and the links readjust
again. We used to set it to a shared access, but then
only one person at a time could make and save changes.
That caused many problems. This has worked out so much
better, just as long as there is one person overseeing
the entire operation.
 
Top