Page 1 of 4 123 ... LastLast
Results 1 to 15 of 52
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    492
    Thanks
    43
    Thanked 0 Times in 0 Posts

    Windows validation

    Hi
    Is it possible to use windows validation for users to grant access to individual spreadsheets in a common workbook
    cheers

    Phil Carter

  2. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,353
    Thanks
    49
    Thanked 275 Times in 253 Posts
    This is the closest I found on Windows Authentication. Tried it but it needs some tweaking

    http://vbnet.mvps.org/index.html?cod...itycontext.htm

    There is an interesting hack at http://datapigtechnologies.com/blog/...-applications/ using permissions to a share point folder but I don't see why this could not be applied with permissions to a shared network folder as well

    HTH,
    Maud
    Last edited by Maudibe; 2013-07-21 at 19:50.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,499
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Phil,

    I don't know what you mean by Windows Validation but you can do it with a little VBA as long as each user has their own logon account.
    You just need to check the Environment Variable: UserName then unprotect the appropriate sheet and make sure you protect it on close or reprotect all sheets on open. BTW: You could also accomplish this by giving each sheet a different password. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,499
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Phil,

    Here is some sample code:

    In a module:
    Code:
    Option Explicit
    
    Sub Auto_Open()
    
       Dim zUserID  As String
       
       zUserID = Environ("UserName")
       
       '*** Add a Case statement for each UserName and the appropriate Sheet
       '*** Unprotect command with Password - I'd recomment All Upper Case PWs
       Select Case zUserID
             Case "Bruce"
                 Sheets("Sheet2").Unprotect "TEST"
             Case Else
                MsgBox "The User Named: " & zUserID & vbCrLf & _
                       "Is not Authorized to use this Workbook.", _
                       vbOKOnly + vbCritical, _
                       "UnAuthorized Access Attempt"
                Application.Quit
       End Select  'Case zUserId
    End Sub
    In the Workbook Object:
    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
       Dim zUserID  As String
       
       zUserID = Environ("UserName")
       
       '*** Add a Case statement for each UserName and the appropriate Sheet
       '*** Protect command
       '*** Note: The password must be set manually once and the workbook saved!
       Select Case zUserID
             Case "Bruce"
                 Sheets("Sheet2").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
       End Select  'Case zUserId
    
    End Sub
    Notes:
    • In the attached sheet you'll need to get into the VBA and change the UserName "Bruce" to your UserName before you can test it. Change it in BOTH places! In you production code you should a password on the VBA module and select the Hide Code option to keep the curious from finding your PWs.
    • I placed the reprotect code in a Workbook_BeforeSave event since if they don't save it nothing will have changed.
    • Sheet1 is protected and the code does not unprotect it. Sheet2 will be unprotected when you change the UserNames in the code.



    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    bonriki (2013-07-22)

  6. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,353
    Thanks
    49
    Thanked 275 Times in 253 Posts
    RG's code will grant access to anyone logged in under a specific account. However, if you will be asking for the Window's Authenticated password (to match the user logged in) to access the sheet, then you will need to dig deeper than RG's code.

  7. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    492
    Thanks
    43
    Thanked 0 Times in 0 Posts
    RG
    Thanks for that it is pretty much what I was looking for.

    Just one point I can't unprotect Sheet1 with "TEST"!
    cheers

    Phil Carter

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,499
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Phil,

    That was by design to show the code worked. The password to Sheet1 is TEST1, original huh? Glad it is what you needed.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    bonriki (2013-07-22)

  10. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    492
    Thanks
    43
    Thanked 0 Times in 0 Posts
    Exactly what I need
    Now the work starts, sheet 1 = general information, sheet = template for records. 2 files 50 sheets each all with different names!! Therefore 2 files with 50 case statements each!
    Should keep me busy for ???????????????
    cheers

    Phil Carter

  11. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,499
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Phil,

    Hopefully the two workbooks use the same UserNames so you can copy that Case stmt!

    Better yet you can set it up so that all the macros are in one workbook and that workbook opens the ones with the named sheets then you only have one code base to maintain. This is how I set up many of my projects.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  12. #10
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    492
    Thanks
    43
    Thanked 0 Times in 0 Posts
    Hi
    Unfortunately they bare 2 separate workbooks with different user names.
    I am interested in how you maintain or create and maintain a master workbook
    cheers

    Phil Carter

  13. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,499
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Phil,

    The basic setup is a Workbook with all the code (VBA) that runs a series of data workbooks which are all setup identically. I usually use this setup where there is data which is stored by year, e.g. restarts every year w/no need to look at the data across years. The user opens the master workbook which immediately opens a dialog to allow them to select the desired data workbook.
    Selector.JPG
    I've actually developed a standard code workbook that has all the standard functions like selection dialog box as shown above, application specific menu, sort code, etc. It just takes a few customizations (usually setting variables) to adapt it to a new data setup. Of course you do have to rewrite the data specific code/menus but all the repetitive stuff is just reused and there is only one version of the code to maintain. When I make a change to one of the standard modules it's just a matter of copying it into each project after it has been fully tested. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  14. The Following User Says Thank You to RetiredGeek For This Useful Post:

    bonriki (2013-07-23)

  15. #12
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    492
    Thanks
    43
    Thanked 0 Times in 0 Posts
    Retiree hi
    I am having trouble with the code.
    I have in the Auto_Open module this :
    Sub Auto_Open()

    Dim zUserID As String

    zUserID = Environ("UserName")

    '*** Add a Case statement for each UserName and the appropriate Sheet
    '*** Unprotect command with Password - I'd recomment All Upper Case PWs
    'Set Focus = Sheets("Instructions")

    Select Case zUserID
    Case "pgc"
    Sheets("Phil Carter").Unprotect "hradmin"
    Sheets("Instructions").Visible = xlSheetVisible
    Sheets("Phil Carter").Unprotect "hradmin"
    Case "dmw"
    Sheets("Dian Wilkinson").Visible = xlSheetVisible
    Sheets("Instructions").Visible = xlSheetVisible
    Sheets("Dian Wilkinson").Unprotect "hradmin"

    and in the Workbook this:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim zUserID As String

    zUserID = Environ("UserName")

    '*** Add a Case statement for each UserName and the appropriate Sheet
    '*** Protect command
    '*** Note: The password must be set manually once and the workbook saved!

    Select Case zUserID
    Case "pgc"
    Sheets("Phil Carter").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Case "dmw"
    Sheets("Dian Wilkinson").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    I also have this code in the workbook to hide all sheets except "Instructions":
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In Me.Worksheets
    If ws.Name <> "Instructions" Then ws.Visible = xlSheetHidden
    Next ws
    Me.Save
    End Sub

    Can't seem to get the zUserID recognised
    cheers

    Phil Carter

  16. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,499
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Phil,

    I always force the userids to upper case then compare against upper case just to eliminate problems.
    zUserID = UCase(Environ("UserName"))

    I think you mis-typed in the first line of Case "pgc" shouldn't that be like the 1st line of Case "dmw"?

    If I may ask why are you
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  17. #14
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    492
    Thanks
    43
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Phil,

    I always force the userids to upper case then compare against upper case just to eliminate problems.
    zUserID = UCase(Environ("UserName"))

    I think you mis-typed in the first line of Case "pgc" shouldn't that be like the 1st line of Case "dmw"?

    If I may ask why are you
    You are right. Not sure why I changed it!

    I still can't get it to work, which is frustrating as I was hoping to roll it out today!!!
    cheers

    Phil Carter

  18. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,499
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Phil,

    Could you possibly post a test workbook?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Page 1 of 4 123 ... LastLast

Posting Permissions

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