Problems with select case
Hi Tom
Sorry to be a niusance but I am still stuck!
I cut and pasted the msgbox code but receive a compile error - argument not
optional message (the word msgbox is highlighted blue)
also where would I put the conversion to lower case (I am using a 4 digit
number for the password!)
Kenny
"Tom Ogilvy" wrote in message
...
I didn't see anything wrong with the code, so perhaps it is the values.
Put
in a msgbox before the select case to see what you have
msg = "-" & LogEntry.Textbox6.Value & "<--" & _
Mypassword1 & "<--" & Mypassword2 & "<-- & _
Mypassword3 & "<-"
msgbox
Select Case LogEntry.TextBox6.Value
Case MyPassword1
also you might want to convert all to lower case
Select Case lcase(LogEntry.TextBox6.Value)
Case lcase(MyPassword1)
Case lcase(MyPassword2)
Case lcase(MyPassword3)
Case else
End Select
--
Regards,
Tom Ogilvy
"N E Body" wrote in message
...
Thanks Tom
I have tried it but it did not work! All I get is the messagebox from
Case
Else
Could you check my code?
TIA
Kenny
Private Sub CommandButton8_Click()
Dim MyPassword1 As Variant
Dim MyName1 As Variant
Dim MyPassword2 As Variant
Dim MyName2 As Variant
Dim MyPassword3 As Variant
Dim MyName3 As Variant
MyPassword1 = Sheets("Lists").Range("AB2").Value
MyName1 = Sheets("Lists").Range("AA2").Value
MyPassword2 = Sheets("Lists").Range("AB3").Value
MyName2 = Sheets("Lists").Range("AA3").Value
MyPassword3 = Sheets("Lists").Range("AB4").Value
MyName3 = Sheets("Lists").Range("AA4").Value
If LogEntry.TextBox2.Value = "" Then
MsgBox "There is nothing to sign off"
MyClearBoxes
Exit Sub
End If
If ActiveCell.Offset(0, 14).Value < 0 Then
MsgBox "This defect has already been signed off!"
MyClearBoxes
Sheets("Data").Range("A2").Activate
Exit Sub
End If
Select Case LogEntry.TextBox6.Value
Case MyPassword1
ActiveCell.Offset(0, 14).Value = MyName1
LogEntry.TextBox5.Value = Format(Now(), "D MMM YYYY HH:MM")
ActiveCell.Offset(0, 12).Value = LogEntry.TextBox5.Value
ActiveCell.Offset(0, 11).Value = LogEntry.TextBox4.Value
ThisWorkbook.Save
MyClearBoxes
Case MyPassword2
ActiveCell.Offset(0, 14).Value = MyName2
LogEntry.TextBox5.Value = Format(Now(), "D MMM YYYY HH:MM")
ActiveCell.Offset(0, 12).Value = LogEntry.TextBox5.Value
ActiveCell.Offset(0, 11).Value = LogEntry.TextBox4.Value
ThisWorkbook.Save
MyClearBoxes
Case MyPassword3
ActiveCell.Offset(0, 14).Value = MyName3
LogEntry.TextBox5.Value = Format(Now(), "D MMM YYYY HH:MM")
ActiveCell.Offset(0, 12).Value = LogEntry.TextBox5.Value
ActiveCell.Offset(0, 11).Value = LogEntry.TextBox4.Value
ThisWorkbook.Save
MyClearBoxes
Case Else:
MsgBox "Password failed - Unable to show as RECTIFIED"
ActiveCell.Offset(0, 14).Value = ""
ActiveCell.Offset(0, 12).Value = ""
ActiveCell.Offset(0, 11).Value = ""
Response = MsgBox("Do you want to enter another record?", vbYesNo)
If Response = vbYes Then
ThisWorkbook.Save
MyClearBoxes
Else
Unload LogEntry
ThisWorkbook.Save
Application.Quit
End If
End Select
End Sub
|