• User Specific Functionality (2003)

    Author
    Topic
    #451345

    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

    Viewing 0 reply threads
    Author
    Replies
    • #1110874

      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

      • #1110891

        Hans,
        Much easier than the chicken scratch I offered up cheers

        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

        • #1110897

          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.

          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

          • #1110983

            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

            • #1110987

              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

            • #1111176

              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

              ? Selection.Name

              – Press Enter.

              I’d use the Workbook_BeforeSave event in the ThisWorkbook module to hide the SpecificUser sheet:

              Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
              On Error Resume Next
              Me.Worksheets("SpecificUser").Visible = xlSheetHidden
              End Sub

            • #1111186

              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

            • #1111187

              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.

            • #1111188

              Hans,
              Please forgive me. Thank you for that blush

              Amy

    Viewing 0 reply threads
    Reply To: User Specific Functionality (2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: