| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| View set to 'best fit' for User | Excel Discussion (Misc queries) | |||
| User Choosing Which Columns to View | Excel Discussion (Misc queries) | |||
| specific user view | Excel Discussion (Misc queries) | |||
| Limiting view area to user | Excel Programming | |||
| Limiting view area to user | Excel Programming | |||
Linear Mode
