View by User
 

I have a workbook that will be shared that contains several worksheets.
Depending on the user, I want different sheets hidden from view upon opening
the workbook. Is there an easy way to do this?

View by User
 

You can use the workbook open event. Launch VBE using Alt+F11. Double click
on 'This Workbook'.Drop down to find Workbook open event.

Private Sub Workbook_Open()
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

I have a workbook that will be shared that contains several worksheets.
Depending on the user, I want different sheets hidden from view upon opening
the workbook. Is there an easy way to do this?

View by User
 

How would I tie that to a user? For instance User 1 should see sheets A & B,
user 2 only A and user 3 only B.

"Jacob Skaria" wrote:

You can use the workbook open event. Launch VBE using Alt+F11. Double click
on 'This Workbook'.Drop down to find Workbook open event.

Private Sub Workbook_Open()
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

I have a workbook that will be shared that contains several worksheets.
Depending on the user, I want different sheets hidden from view upon opening
the workbook. Is there an easy way to do this?

View by User
 

Private Sub Workbook_Open()
strUser = Environ("Username")
Select Case strUser
Case "John"
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Case "Peter", "Mary"
Sheets("Sheet1").Visible = False
Sheets("Sheet3").Visible = False
End Select
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

How would I tie that to a user? For instance User 1 should see sheets A & B,
user 2 only A and user 3 only B.

"Jacob Skaria" wrote:

You can use the workbook open event. Launch VBE using Alt+F11. Double click
on 'This Workbook'.Drop down to find Workbook open event.

Private Sub Workbook_Open()
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

I have a workbook that will be shared that contains several worksheets.
Depending on the user, I want different sheets hidden from view upon opening
the workbook. Is there an easy way to do this?

View by User
 

The links below show how to use Windows API calls to get either the full name
or the login name of the user. You can put a call to one of these routines
in your workbook_open event and then compare that to your pre-defined list of
users to determine which sheets should be hidden or not.

http://www.mvps.org/access/api/api0066.htm
http://www.mvps.org/access/api/api0008.htm

Here is some code to help you get started. First, paste the code from the
above links into a regular code module in your workbook. Then, in the
Workbook module, enter the code below. This demonstrates how to get either
the login name or the full user name, along with some psuedo-code for setting
views based on which user has the file opened.

Private Sub Workbook_Open()
Dim theUser As String
'
' Use one or the other as required...
'
theUser = fGetFullNameOfLoggedUser()
MsgBox theUser
'
theUser = fOSUserName()
MsgBox theUser
'
Select Case theUser
Case "simpshj" ' login ID example
ActiveWorkbook.Sheets("Sheet1").Visible = True
ActiveWorkbook.Sheets("Sheet2").Visible = False
Case "Homer J Simpson" ' full name example
ActiveWorkbook.Sheets("Sheet1").Visible = True
ActiveWorkbook.Sheets("Sheet2").Visible = False
Case Else ' default view
ActiveWorkbook.Sheets("Sheet1").Visible = False
ActiveWorkbook.Sheets("Sheet2").Visible = True
End Select
'
End Sub

WARNING: I just thought of this. You may not be able to alter the
structure of the workbook (i.e. showing and hiding tabs) if it is a shared
file. You'll just have to experiment.

HTH,

Eric

"Pam M" wrote:

I have a workbook that will be shared that contains several worksheets.
Depending on the user, I want different sheets hidden from view upon opening
the workbook. Is there an easy way to do this?

View by User
 

var1 = Range("A1:A10")
For Each strval In var1
Debug.Print strval
Next

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Private Sub Workbook_Open()
strUser = Environ("Username")
Select Case strUser
Case "John"
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Case "Peter", "Mary"
Sheets("Sheet1").Visible = False
Sheets("Sheet3").Visible = False
End Select
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

How would I tie that to a user? For instance User 1 should see sheets A & B,
user 2 only A and user 3 only B.

"Jacob Skaria" wrote:

You can use the workbook open event. Launch VBE using Alt+F11. Double click
on 'This Workbook'.Drop down to find Workbook open event.

Private Sub Workbook_Open()
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

I have a workbook that will be shared that contains several worksheets.
Depending on the user, I want different sheets hidden from view upon opening
the workbook. Is there an easy way to do this?

View by User
 

Sorry this is posted wrongly..
--
If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

var1 = Range("A1:A10")
For Each strval In var1
Debug.Print strval
Next

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Private Sub Workbook_Open()
strUser = Environ("Username")
Select Case strUser
Case "John"
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Case "Peter", "Mary"
Sheets("Sheet1").Visible = False
Sheets("Sheet3").Visible = False
End Select
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

How would I tie that to a user? For instance User 1 should see sheets A & B,
user 2 only A and user 3 only B.

"Jacob Skaria" wrote:

You can use the workbook open event. Launch VBE using Alt+F11. Double click
on 'This Workbook'.Drop down to find Workbook open event.

Private Sub Workbook_Open()
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

I have a workbook that will be shared that contains several worksheets.
Depending on the user, I want different sheets hidden from view upon opening
the workbook. Is there an easy way to do this?

View by User
 

Thanks!

"Jacob Skaria" wrote:

Private Sub Workbook_Open()
strUser = Environ("Username")
Select Case strUser
Case "John"
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Case "Peter", "Mary"
Sheets("Sheet1").Visible = False
Sheets("Sheet3").Visible = False
End Select
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

How would I tie that to a user? For instance User 1 should see sheets A & B,
user 2 only A and user 3 only B.

"Jacob Skaria" wrote:

You can use the workbook open event. Launch VBE using Alt+F11. Double click
on 'This Workbook'.Drop down to find Workbook open event.

Private Sub Workbook_Open()
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Pam M" wrote:

I have a workbook that will be shared that contains several worksheets.
Depending on the user, I want different sheets hidden from view upon opening
the workbook. Is there an easy way to do this?