Excel 2007 error with code for creating passwords

[CMSS]

I am making a macro that when a workbook opens, an inputbox runs that asks for a password. Valid passwords are certain names, and what name you enter determines what type of access you have in the document (i.e., 'bob' allows data to be entered, 'mary' hides certain rows and columns, etc.). I do this by having on another worksheet in the workbook the valid names in one column and next to them code words that determine the permissions allowed for that name. When a name is entered the code searches for the name, and if it is valid it sets the corresponding code word located next to it as a variable. I want an error message to appear if a name that is not on the approved list is entered, the default entry in the inputbox is left in the user input line, or if the user input line is blank, and cycle back to the original inputbox. Also, I only want the user to have three chances at entering a valid name before the workbook closes automatically. In addition, if the user hits 'cancel' on the inputbox, I want the workbook to close.



Here is my code thus far:

    Dim name As String

    Dim permission As String

    Dim i As Long

    On Error Resume Next

    Application.DisplayAlerts = False

      Sheets("Sheet1").Unprotect Password:="c"

      Sheets("Sheet1").Visible = xlSheetVeryHidden

  For i = 1 To 3

    name = Application.InputBox("Please Enter Name", "Login", "Enter Name Here", Type:=2)

    Sheets("Sheet3").Select

    If permission = Range("A1:A4").Find(name, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1) Then

        Select Case permission

Case "admin": Sheets("Sheet1").Visible = True

              Sheets("Sheet1").Select

              Range("A1:M23").Locked = False

              Range("A1").Select

              ActiveSheet.Protect Password:="c"

              ActiveSheet.EnableSelection = xlUnlockedCells

Case "finance": Sheets("Sheet1").Visible = True

                Sheets("Sheet1").Select

                Columns("A:B").Hidden = True

                Rows("5:10").Hidden = True

                Range("C1").Select

                ActiveSheet.Protect Password:="c"

Case "superuser": Sheets("Sheet1").Visible = True

                  Sheets("Sheet1").Select

                  Range("F1:F3, F5:F23").Locked = False

                  Columns("B").Hidden = True

                  Rows("4").Hidden = True

                  Range("A1").Select

                  ActiveSheet.Protect Password:="c"

                  ActiveSheet.EnableSelection = xlUnlockedCells

Case "user": Sheets("Sheet1").Visible = True

             Sheets("Sheet1").Select

             Columns("A:D").Hidden = True

             Rows("20:23").Hidden = True

             Range("E1").Select

             ActiveSheet.Protect Password:="c"

        End Select

      Exit Sub

    Else

        MsgBox ("Invalid Name Entered!")

    End If

    Next i

    Application.Quit

I either get an error message saying I have an 'End If' without an 'If', or a 'Next' without a 'For', or an 'Else' without an 'If'.

[Andrew-R]

I assume that the i loop is there to allow three attempts at a valid password. Without a copy of your sheet to work with I'd try to avoid conditionally exiting a sub within a For...Next loop.

Perhaps try:

Dim ValidLogin as Boolean
Dim ReTries as Integer

ValidLogin=False
ReTries=0

While Not(ValidLogin) And ReTries<3
  name = Application.InputBox("Please Enter Name", "Login", "Enter Name Here", Type:=2)

  Set permission=Sheets("Sheet3").Range("A1:A4").Find(name, LookAt:=xlWhole)

  if permission is nothing then
    MsgBox ("Invalid Name Entered!")

    Retries=Retries+1

  else
     ValidLogin=True

        Select Case permission.offset(0,1).value

Case "admin": Sheets("Sheet1").Visible = True

              Sheets("Sheet1").Select

              Range("A1:M23").Locked = False

              Range("A1").Select

              ActiveSheet.Protect Password:="c"

              ActiveSheet.EnableSelection = xlUnlockedCells

Case "finance": Sheets("Sheet1").Visible = True

                Sheets("Sheet1").Select

                Columns("A:B").Hidden = True

                Rows("5:10").Hidden = True

                Range("C1").Select

                ActiveSheet.Protect Password:="c"

Case "superuser": Sheets("Sheet1").Visible = True

                  Sheets("Sheet1").Select

                  Range("F1:F3, F5:F23").Locked = False

                  Columns("B").Hidden = True

                  Rows("4").Hidden = True

                  Range("A1").Select

                  ActiveSheet.Protect Password:="c"

                  ActiveSheet.EnableSelection = xlUnlockedCells

Case "user": Sheets("Sheet1").Visible = True

             Sheets("Sheet1").Select

             Columns("A:D").Hidden = True

             Rows("20:23").Hidden = True

             Range("E1").Select

             ActiveSheet.Protect Password:="c"

        End Select

  End If

Wend

If Not(ValidLogin) Then
    Application.Quit
End If

[shg]

You code compiles fine for me if I put it inside a sub.

'Twere I, I'd use a table in the VeryHidden sheet to encapsulate the permissions, rows/columns to hide, etc. for each user. Then the code just interprets the table.

[CMSS]

@Andrew: I tried incorporating your code into mine, but I get multiple object errors.

My code now works to an extent. It will close after three failed attempts, but if a correct name is entered, it gives the incorrect name error message.

[Andrew-R]

OK, I actually tried putting the code in and testing it this time. It doesn't like you using either Name or Permission as variables, but a quick bit of renaming (and some trimming so I didn't need your whole workbook) gave this code, which works...

Sub Login()

Dim ValidLogin As Boolean
Dim ReTries As Integer
Dim Perm As Range
Dim UName

ValidLogin = False
ReTries = 0

While Not (ValidLogin) And ReTries < 3
  UName = Application.InputBox("Please Enter Name", "Login", "Enter Name Here", Type:=2)

  Set Perm = Sheets("Sheet3").Range("A1:A4").Find(UName, LookAt:=xlWhole)

  If Perm Is Nothing Then
    MsgBox ("Invalid Name Entered!")

    ReTries = ReTries + 1

  Else
     ValidLogin = True

     Select Case Perm.Offset(0, 1).Value
       Case "admin": MsgBox "Admin"
       Case "finance": MsgBox "Finance"
       Case "superuser": MsgBox "Superuser"
       Case "user": MsgBox "User"
       Case Else: MsgBox "Unknown user group"
     End Select

  End If

Wend

If Not (ValidLogin) Then
    Application.Quit
End If

End Sub

Want to work from there?

[CMSS]

Thanks for the help, I fixed the problem!

[romperstomper]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this.

Note: X-post here, appears to be solved now.