ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Problems with select case (https://www.excelbanter.com/excel-programming/313066-problems-select-case.html)

N E Body
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 = ""

Tom Ogilvy
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 = ""

N E Body
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

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

N E Body
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

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

N E Body
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

Tom Ogilvy
Problems with select case
 
Send a sample workbook with the pertinent parts and code to
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.

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

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


All times are GMT +1. The time now is 12:16 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com