| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with select case
Hi everyone
I am having problems modifying a Select Case structure - My existing code runs fine (included below) but I cannot modify it to do what I want. I have a textbox on a Userform. A password is added by the user then a CommandButton clicked to run the code. 3 different passwords can be used which run 3 different pieces of code If I put the passwords directly into the code it works, however I want to refer to the passwords as MyPassword1, MyPassword2 and MyPassword3 because then I am referring to cell values (which can be changed without the need for altering code!) I use MyPassword1 = Sheets("Lists").Range("AB2").Value and declare MyPassword as a variable Can anyone help? Kenny using Win Me, 2000 and XP with Excel 97 and 2000 Heres part of the code Select Case LogEntry.TextBox6.Value 'Case Sheets("Lists").Range("AB2").Value: ActiveCell.Offset(0, 14).Value = MyName1 ' this line failed! 'Case MyPassword1: ActiveCell.Offset(0, 14).Value = MyName1 ' this line failed! Case "1312": ActiveCell.Offset(0, 14).Value = MyName1 'this line works! 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 "002": ActiveCell.Offset(0, 14).Value = "Karl Eason" 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 "2505": ActiveCell.Offset(0, 14).Value = "Andrew Cooper" 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 = "" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with select case
MyPassword1 = _
Sheets("Lists").Range("AB2").Value MyPassword2 = _ Sheets("Lists").Range("AB4").Value MyPassword2 = _ Sheets("Lists").Range("AB4").Value 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 = "Karl Eason" 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 = "Andrew Cooper" 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 = "" End Select -- Regards, Tom Ogilvy "N E Body" wrote in message ... Hi everyone I am having problems modifying a Select Case structure - My existing code runs fine (included below) but I cannot modify it to do what I want. I have a textbox on a Userform. A password is added by the user then a CommandButton clicked to run the code. 3 different passwords can be used which run 3 different pieces of code If I put the passwords directly into the code it works, however I want to refer to the passwords as MyPassword1, MyPassword2 and MyPassword3 because then I am referring to cell values (which can be changed without the need for altering code!) I use MyPassword1 = Sheets("Lists").Range("AB2").Value and declare MyPassword as a variable Can anyone help? Kenny using Win Me, 2000 and XP with Excel 97 and 2000 Heres part of the code Select Case LogEntry.TextBox6.Value 'Case Sheets("Lists").Range("AB2").Value: ActiveCell.Offset(0, 14).Value = MyName1 ' this line failed! 'Case MyPassword1: ActiveCell.Offset(0, 14).Value = MyName1 ' this line failed! Case "1312": ActiveCell.Offset(0, 14).Value = MyName1 'this line works! 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 "002": ActiveCell.Offset(0, 14).Value = "Karl Eason" 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 "2505": ActiveCell.Offset(0, 14).Value = "Andrew Cooper" 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 = "" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with select case
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with select case
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with select case
I think that would be:
MsgBox msg Regards Trevor "N E Body" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with select case
Thanks Trevor
After correcting my syntax error and then Type mismatch I got the messagebox to appear The messagebox displays all the correct info but after closing the box it still does not perform as it should. It should have run case1 but ran case else instead (case2 and 3 had no effect either) Any ideas??? Kenny "Trevor Shuttleworth" wrote in message ... I think that would be: MsgBox msg Regards Trevor "N E Body" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with select case
|
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problems with select case
Or you can try this
Private Sub CommandButton8_Click() Dim MyPassword1 As String Dim MyName1 As String Dim MyPassword2 As String Dim MyName2 As String Dim MyPassword3 As String Dim MyName3 As String MyPassword1 = Sheets("Lists").Range("AB2").Text MyName1 = Sheets("Lists").Range("AA2").Value MyPassword2 = Sheets("Lists").Range("AB3").Text MyName2 = Sheets("Lists").Range("AA3").Value MyPassword3 = Sheets("Lists").Range("AB4").Text 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 -- Regards, Tom Ogilvy "N E Body" wrote in message ... Thanks Trevor After correcting my syntax error and then Type mismatch I got the messagebox to appear The messagebox displays all the correct info but after closing the box it still does not perform as it should. It should have run case1 but ran case else instead (case2 and 3 had no effect either) Any ideas??? Kenny "Trevor Shuttleworth" wrote in message ... I think that would be: MsgBox msg Regards Trevor "N E Body" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| select case | Excel Discussion (Misc queries) | |||
| Select case | Excel Discussion (Misc queries) | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| Select Case | Excel Discussion (Misc queries) | |||
| select case help please | Excel Programming | |||
Linear Mode
