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

    Password a Command Button? (Excel 2002/2003)

    Hi

    Is it possible to password protect a command button, for example I want some columns hidden from some users, but allow Team managers to see those columns,

    ie to unhide them look at them and then hide them again.

    Any Ideas please
    Regards
    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Password a Command Button? (Excel 2002/2003)

    You could prompt for a password using InputBox, but it probably won't be very safe:
    - You'd have to password protect the VBA code so that users cannot look at your code to see the password.
    - Someone determined to crack this password can probably do this within a reasonable amount of time.
    - How are you going to ensure that the columns will be hidden again?

  3. #3
    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: Password a Command Button? (Excel 2002/2003)

    You could have it hidden and only display it when particular users opened up the file [using Environ("username") or perhaps Application.UserName] and possilbly prompts for a password. The code to check the user could be in the workbook Open event.

    Steve

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Password a Command Button? (Excel 2002/2003)

    Hi,
    I think you would have to protect the worksheet and have code behind the button that used the InputBox function to request a password; if it matches, unprotect the sheet and unhide the columns. You would also need the BeforeClose event to ensure that the columns have been hidden again - which can be tricky if the users have made other changes that they do not want to save.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Password a Command Button? (Excel 2002/2003)

    Hi Steve

    Could you possibly enlighten me a little to the use of Environ ("username") and Appliction.Username I am not familiar with these.

    Regards

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

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

    Re: Password a Command Button? (Excel 2002/2003)

    Hi Hans

    The VBA code is already protected, and the rehiding would be useful but not too important.

    Brieflly the Salesman fills in the necesarry data then sends it to the administrator who would then look at the hidden columns, the Salesman would not see the workbook again once it had been submitted.

    I know nothing is infalable but if the salesman went to lenghts to unhide the columns the book would not be accepted.

    Regards

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

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

    Re: Password a Command Button? (Excel 2002/2003)

    Hi Rory

    It looks like the ImputBox function seems the way to go, as I said in my replies to Steve and Hans. We just want make the effort to try and protect and hide these columns as best we can, no guarentees.

    Regards

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

  8. #8
    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: Password a Command Button? (Excel 2002/2003)

    You could use something like these (change the command button as neccessary and the list of "approved users"

    for Environ("username")
    <pre>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</pre>


    For application.username
    <pre>Option Explicit
    Private Sub Workbook_Open()
    Dim sUsers As String
    sUsers = "Robert April, Christopher Pike, James Kirk"
    Worksheets("Sheet1").CommandButton1.Visible = _
    (InStr(ucase(sUsers), ucase(Application.UserName)) <> 0)
    End Sub</pre>


    A problem with application.username is that it can be edited easily via tools-options so is "less secure". many people do not switch the username from the default company name.
    the Environ one is the computer username and I think is less readily switchable. At our company the one from envirion is our "Concern Wide ID".

    Steve

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

    Re: Password a Command Button? (Excel 2002/2003)

    Hi Steve

    Many thanks for your help, I will experiment with yur code.


    Regards

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

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

    Re: Password a Command Button? (Excel 2002/2003)

    Hi Steve

    I am really green here, this is as far as I can get, I put the code in the Workbook_Open() but I don't know what to do next.

    Sorry if I appear a bit dumb on this one.

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

  11. #11
    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: Password a Command Button? (Excel 2002/2003)

    Add a command button on the sheet named "CommandButton1" on "Sheet1". Add the usernames of all who should be able to see the button when the workbook opens

    When the workbook opens if the environmental username is in the list, it will be visible, if not it will be hidden.
    If you change the sheet name and/or command button name, you must change the reference in the code.

    You can add code to the command button to do as you desire (or is this part of your question). I was only answering about using environ to hide/display a commandbutton...

    Steve

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

    Re: Password a Command Button? (Excel 2002/2003)

    HI Steve

    I have got to grips with it now thanks very much. Is it possible to hide worksheets this way aswell as buttons?.

    Many Thanks

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

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

    Re: Password a Command Button? (Excel 2002/2003)

    Instead of

    Worksheets("Sheet1").CommandButton1.Visible = ...

    to show or hide a command button, you can use

    Worksheets("Sheet1").Visible = ...

    to show or hide a worksheet. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Password a Command Button? (Excel 2002/2003)

    Hi Hans

    Thank you very much for your prompt reply.

    I am most grateful.

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

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

    Re: Password a Command Button? (Excel 2002/2003)

    Hi Hans

    I am trying to adapt the environ code to hide a sheee instead of a comand button, I changed the code but get an expected array error <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    please see attached.

    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
  •