Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    for Environ('username') (Excel 2002/2003)

    Hi

    I was very kindly given this code by steve and I have used to success, my question is can it be adapted to stop the workbook opening if the sUser is not in the code?, or would I need new code.

    Option Explicit
    Private Sub Workbook_Open()
    Dim sUsers As String
    sUsers = "abcde, fghij, klmno"
    Worksheets("Sheet1").CommandButton1.Visible = _
    (InStr(UCase(sUsers), UCase(Environ("username"))) <> 0)
    End Sub

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: for Environ('username') (Excel 2002/2003)

    Just close it:
    ActiveWorkbook.Close SaveChanges:=False
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: for Environ('username') (Excel 2002/2003)

    Hi Sammy

    I been trying with this for some time, I can't figure out where to put it in the code.

    Sorry to appear a bit dumb.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: for Environ('username') (Excel 2002/2003)

    You could use the following code in the ThisWorkbook module:
    <code>
    Private Sub Workbook_Open()
    Dim sUsers As String
    sUsers = "abcde, fghij, klmno"
    If InStr(UCase(sUsers), UCase(Environ("username"))) = 0 Then
    Me.Close SaveChanges:=False
    End If
    End Sub
    </code>
    Pleas keep in mind that this won't work if the user has macro security set to Medium and if (s)he disables macros, or if the user has macro security set to High.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: for Environ('username') (Excel 2002/2003)

    Hi Hans

    Thanks for that, I hear what you say about disabling Macros, this workbook would be pretty useless without the macros.

    It's really to be more annoying to anyone who tries to take the data to another company.

    I know nothing is infallable.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: for Environ('username') (Excel 2002/2003)

    Not great security, but another "annoyance":
    You may want to "very hide" all the sheets in the workbook and just have 1 splash screen visible with text indicating that the workbook is useless without macros enabled.

    If the workbook is opened with macros disabled this lone sheet is all that is visible. If macros are enabled, you can check the user list, if not on it, close the workbook (you could be nice and add a message) if on the list, hide the "splash screen worksheet" and unhide all the others. You should add code to the workbook close/save to very hide all the sheets and have only the splash screen visible so it is "reset" after saving...

    Steve

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: for Environ('username') (Excel 2002/2003)

    Hi Steve

    That sound like a very good Idea, but I wouldn't have a clue how or where to start that kind of code, I am a relative novice a VBA.

    Thanks for your input.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: for Environ('username') (Excel 2002/2003)

    (Steve, pardon me for jumping in.) Perhaps simpler than a splash screen is a single blank worksheet with the requirements about macros enabled and being a valid user; that may be what Steve means, since one worksheet must be visible anyway. All the data sheets can be veryhidden, as Steve noted. Here is my generic subroutine for veryhiding all selected worksheets, you'll need to use it just once on the sheets within the workbook you are trying to protect.

    Sub VeryHideSelSheets()
    Dim wksWS As Worksheet
    Application.DisplayAlerts = False
    For Each wksWS In ActiveWindow.SelectedSheets
    wksWS.Visible = xlSheetVeryHidden
    Next wksWS
    Application.DisplayAlerts = True
    Set wksWS = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: for Environ('username') (Excel 2002/2003)

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    I get a flash of the worksheet and then it closes....I have a feeling my NAV is playing tricks with it...Any ideas?
    Jerry

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: for Environ('username') (Excel 2002/2003)

    Hi Hans

    Thanks to you and John for your input. Ideally I would like to go down your route Hans, my only problem is I have all underlying data in sheets that I do not want viewed at all.

    So as far as hiding all sheet to start with is OK, I would not want to unhide the sheets containing the data. I would only want the sheets to show where the user enters the data.

    This is a Profit Model so they would only see the sheets where they would enter the Qty and the Code of the products.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: for Environ('username') (Excel 2002/2003)

    This is intentional - the workbook demonstrates how to prevent unauthorized users from getting in. Your name isn't in the list of allowed users. My reply to Braddy describes how to view the code so that you can edit this list.

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

    Re: for Environ('username') (Excel 2002/2003)

    You can edit the code to hide/show specific sheets instead of all sheets. If necessary, you can use a separate instruction for each sheet:

    Worksheets("Data1").Visible = xlSheetVisible
    Worksheets("Data2").Visible = xlSheetVeryHidden

    etc.

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: for Environ('username') (Excel 2002/2003)

    Hi Hans

    Thank you very much, Youv'e given me plenty to work with, I will endeavor to produce something I can use.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  14. #14
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: for Environ('username') (Excel 2002/2003)

    >This is intentional

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Well then I can say I tested it and it works perfectly <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: for Environ('username') (Excel 2002/2003)

    Hi Hans

    Just a question about the demo file you Kindly did for me, why when I close it does it go the the splash screen and ask me the questions attached?.

    I edited the file as per your instruction so if I enable macro's it's lets me in OK but when I close it does as described above.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 2 12 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
  •