Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: protect and unprotect sheet in excel + hide password

    Hi,

    I HAVE TWO QUESTIONS:

    One, I use a pass in the Auto_Open routine but I want that pass to be "****" when put it in and also if I can have started "***" in the code as well. Is that possible??

    If Name = "RICHARD" Or Name = "Richard" Or Name = "richard" Then
    Pass = InputBox("What is the pass? ", "Pass for Richard")
    If Pass = 123456 Then ----> "*****"
    MsgBox "Welcome"
    Else
    MsgBox "Wrong Pass."
    Auto_Open
    End If
    End If



    two, I am using Activesheet.Unprotect and Activesheet.Protect when using some functionality in excel. this function works, because when I protect the sheet, I don't use a pass. How can I accomplish the same thing with a pass?

    Here is a sample:

    Sub Auto_Open()
    Dim Name As String
    Dim Pass As Long

    Dim clearSheet As String
    '***********************
    ActiveSheet.Unprotect
    '***********************
    Sheet2.Visible = xlSheetHidden
    Sheet3.Visible = xlSheetHidden
    Range("C1").Value = ""
    Range("H1").Value = ""
    Name = InputBox("What is your Name?:", "Input Name", "RICHARD")
    If Name = "RICHARD" Or Name = "Richard" Or Name = "richard" Then
    Pass = InputBox("What is the pass? ", "Pass for Richard")
    If Pass = 123456 Then
    MsgBox "Welcome"
    Else
    MsgBox "Wrong Pass."
    Auto_Open
    End If
    End If
    If Name <> "" Then
    With Range("C1").Font
    .Bold = True
    .Size = 15
    .Color = vbBlue
    End With
    With Range("C1")
    .Value = UCase(Name)
    .Interior.Color = vbYellow
    .Font.Color = vbBlue
    End With
    With Range("H1").Font
    .Bold = True
    .Size = 10
    .Color = vbBlack
    End Withl

    ElseIf Name = "" Then
    MsgBox ("A Rep. Name MUST be entered!")
    Auto_Open
    End If

    Sheet1.Range("B6:B29").Locked = False

    clearSheet = MsgBox("Do you want to clear the screen now?", vbOKCancel, "Warning...")
    If clearSheet = vbOK Then
    Dim myRange As Range
    For Each myRange In Range("B6:N30")
    myRange.Value = ""
    Next
    '*****************
    ActiveSheet.Protect
    '*****************
    ActiveWorkbook.Save
    Exit Sub
    Else
    open_call
    replace_active_cell

    ReadWrite
    '*****************
    ActiveSheet.Protect
    '*****************

    Exit Sub
    End If
    End Sub

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Right no 1.

    I think the best advice here would be to create a userform for your password box,
    With this you can put in a text box that will let you select a password character such as *, and you can use any functionallity you like here, all your Auto_Open event will do would be to load and show an instance of this userform,
    The user form itself can be set up like you would see any other password screen probably with a name box and a password box and a couple of buttons dependant on action, Ok and Cancel, I don't think you can hide the characters in code but instead you should password protect your code.
    you can also use Option Compare Text at the top of the Userforms Class Module this lets "AAA" = "aaa"

    #2

    if you password protect your sheet initally you can use
    Sheet1.Unprotect "MyPassword"
    and
    Sheet1.Protect "MyPassword"

    and finaly some notes about your code

    this
    Code:
    Dim myRange As Range
    For Each myRange In Range("B6:N30")
    myRange.Value = ""
    Next
    can be replaced by Range("B6:N30").Clear

    i always like to put the word Call before any module calls it just helps show exactly what your doing if you are just skimming over your code,

    finally it might be helpful to add coments to your code just to let you know what's happening, this helps when you have to go back into your code to change anything as it makes it easier to see what's going on(I know im guilty of this too)

    HTH

    Dave

  3. #3
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    All of what David says is true, noble and good advice. But if you're in a reasonably secure environment, I'd recommend dispensing with passwords altogether. By reasonably secure I mean a network whereby each user's login is unique and unchangeable--Richard can never log in as Julie and Julie can never log in as Richard, etc.

    If this applies to you, use the following:

    Sub Auto_Open()
    Dim AppUser As String
    AppUser = Application.UserName
    Select Case AppUser
    Case "Administrator"
    'Unprotect everything
    Case "XYZCORP\Richard"
    'Hide stuff you don't want Richard messing with'
    End Select
    'Do things you want done for everyone
    End Sub

    User satisfaction with an app drops precipitously when she's told she needs to create her thirty-seventh password!

    PS Recursively calling a sub (from itself) is occasionally beautiful (google Quicksort recursive), but almost always poor programming as it can consume gobs of memory. Consider using GoTo next time.
    Last edited by actuary; 08-28-04 at 17:49.

Posting Permissions

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