Problems with select case

N

N E Body

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 = ""
 
T

Tom Ogilvy

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
 
N

N E Body

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
 
T

Tom Ogilvy

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
 
N

N E Body

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
 
N

N E Body

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
 
T

Tom Ogilvy

Send a sample workbook with the pertinent parts and code to (e-mail address removed)
and i will see if I can straighten it out for you. I don't have the time or
imagination to try to decipher your layout and to try to recreate your
workbook to test your code.
 
T

Tom Ogilvy

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime error only with Office 97 - advice required 2
Case Statement Help 3
2 Questions about inserting rows. 3
Problem with Select Case construct? 1
problem with macro - its case sensitive! - help please 8
Inserting Rows Above Other Rows. 5
Editing a Row 1
compile error in hidden module: sheet1 4

Top