Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User Specific Functionality (2003)

    I am trying to code a workbook that functions differently for specified users. I want the code to recognize the network UserID of the person accessing the workbook. The workbook opens to a specified worksheet for those users and hyperlinks within the workbook redirect differently for the specified users.

    I have attached a workbook to play with.

    Here is some wrong code for the UserID. I read over the a couple of the UserLog entries, but couldn't discern how to leverage them.

    Private Sub Workbook_Open()
    Dim strUserName As String
    If strUserName = name1, name2 or name3 Then
    Worksheets("SpecificUser").Activate
    End If
    Worksheets("GeneralUser").Activate
    End Sub

    I did find some code that creates a UserLog within MS Access:

    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" _ (ByVal lpBuffer As String, nSize As Long) As Long

    Function fOSUserName() As String
    On Error GoTo fOSUserName_Err

    Dim lngLen As Long, lngX As Long
    Dim strUserName As String

    strUserName = String$(254, 0)
    lngLen - 255
    lngX = apiGetUserName(strUserName, lngLen - 1)

    If lngX <> 0 Then
    fOSUserName = ""
    End If

    fOSUserName_Exit:
    Exit Function
    fOSUserName_Err:
    MsgBox Error$
    Resume fOSUserName_Exit
    End Function

    Either way, I would like the object hyperlink (Return) in the Data1 and Data2 worksheets to hyperlink to the SpecificUser worksheet when the specified users are in the workbook and go to the GeneralUser worksheet for everyone else.

    Thanks
    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Specific Functionality (2003)

    Try this (after modifying the specific names):

    Private Sub Workbook_Open()
    Select Case Environ("username")
    Case "Amy", "John", "DaBoss"
    Worksheets("SpecificUser").Activate
    Case Else
    Worksheets("GeneralUser").Activate
    End Select
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Specific Functionality (2003)

    Hans,
    Much easier than the chicken scratch I offered up <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Can a similar type code be applied to the hyperlinked objects so that specific users return to the SpecificUser worksheet when the Return hyperlink objects are selected vice returning to the GeneralUser worksheet as they are set now?

    Also, does it matter if the SpecificUser worksheet is hidden?

    Amy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Specific Functionality (2003)

    See this version. It also makes sure that SpecificUser is visible. If you wish, you can add lines to hide the other sheet to by setting its Visible property to xlSheetHidden or xlSheetVeryHidden.
    <code>
    Private Sub Workbook_Open()
    Select Case Environ("username")
    Case "Amy", "John", "DaBoss"
    With Worksheets("SpecificUser")
    .Visible = xlSheetVisible
    .Activate
    End With
    Worksheets("Data1").Shapes("Rectangle 1"). _
    Hyperlink.SubAddress = "SpecificUser!A1"
    Worksheets("Data2").Shapes("Rectangle 1"). _
    Hyperlink.SubAddress = "SpecificUser!A1"
    Case Else
    Worksheets("GeneralUser").Activate
    Worksheets("Data1").Shapes("Rectangle 1"). _
    Hyperlink.SubAddress = "GeneralUser!A1"
    Worksheets("Data2").Shapes("Rectangle 1"). _
    Hyperlink.SubAddress = "GeneralUser!A1"
    End Select
    End Sub</code>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Specific Functionality (2003)

    Hans,
    Thanks. This worked, but pulling it out of the sandbox has prompted some questions.

    What is the A1 reference? Is that just making sure it opens to the top left view of the worksheet?

    Also, how do you determine the "Rectangle 1" reference. I have attached the workbook with your code, but added some other rectangles with narrative text. How do I determine the numbering of these objects so that they can be properly referenced in code?

    Upon workbook exit, I would like to hide the "SpecificUser" worksheet. That requires Private Sub Workbook_Close() commands after the WorkbookOpen() Sub I guess?

    This is fun. Thanks.
    Amy
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Specific Functionality (2003)

    I found some answers to my questions.

    Determining the object number (like "Rectange1") can be determined by right clicking on the object and slecting Assign Macros will reveal what number the object is.

    Any thoughts on the hide "SpecificUser" worksheet upon workbook exit are most appreciated.

    Thanks
    Amy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Specific Functionality (2003)

    Another way to find the name of an object is:
    - Select the object.
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Press Ctrl+G to activate the Immediate window.
    - Type
    <code>
    ? Selection.Name
    </code>
    - Press Enter.

    I'd use the Workbook_BeforeSave event in the ThisWorkbook module to hide the SpecificUser sheet:
    <code>
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    On Error Resume Next
    Me.Worksheets("SpecificUser").Visible = xlSheetHidden
    End Sub</code>

  8. #8
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Specific Functionality (2003)

    Hans,
    Thanks. That worked well..perhaps too well

    It hides the "SpecificUser" worksheet when a Save is executed. I would rather it be hidden upon exiting the workbook, with or without having saved, to ensure that when general users open the workbook, it is hidden.

    Amy

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: User Specific Functionality (2003)

    Problem is that hiding a worksheet counts as a change to the workbook. So if the user has only viewed the workbook, the Before Close code would mark the workbook as changed, causing the prompt "Do you want to save changes".
    Placing the code in the Before Save event ensures that it is only run when the workbook is saved. If the user closes the workbook without saving it, it shouldn't matter - the SpecificUser sheet is already hidden in the saved version.

  10. #10
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: User Specific Functionality (2003)

    Hans,
    Please forgive me. Thank you for that <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Amy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •