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 |
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 |