View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
N E Body N E Body is offline
external usenet poster
 
Posts: 34
Default 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