Trouble with lots of IF THEN statements???

[Simon Lloyd]

Hi all below is some code for finding a worksheet then a particular range then check whether cells are empty or mot based on the values in comboboxes on a userform, the text coloured in blue i have just added (in order to check offsets according to which name appeared in ComboBox2) prior to that the lines i have stetted out worked perfect for finding the correct line and checking the offset's for values, now the code runs through without recognising the persons name so not checking the offsets.......Anyone know how to fix this?, i'm sure its the way i have used the IF THEN statements!

Regards,
Simon

Public Sub FindSlot()
Dim strFirst As Integer
Dim rng As Range
Dim w, vf, t, s As Variant
Dim r As Range
Dim mycell
Application.EnableEvents = False
w = UserForm2.ComboBox3.Value
vf = UserForm2.ListBox1.Value
s = UserForm2.ComboBox2.Value
Worksheets(w).Visible = True
Worksheets(w).Select
t = UserForm2.ComboBox1.Value
If t = "Tuesday" Then
Set r = Worksheets(w).Range("A4:A46")
ElseIf t = "Wednesday" Then
Set r = Worksheets(w).Range("A49:A94")
ElseIf t = "Thursday" Then
Set r = Worksheets(w).Range("A97:A142")
ElseIf t = "Friday" Then
Set r = Worksheets(w).Range("A145:A190")
ElseIf t = "Saturday" Then
Set r = Worksheets(w).Range("A193:A238")
End If
'On Error GoTo cls
Application.EnableEvents = False

For Each mycell In r
If mycell.Text = UserForm2.ListBox1.Text Then
mycell.Select
'UserForm2.Hide

If s = "Lauren" Then
If mycell.Offset(0, 1) <> "" And mycell.Offset(0, 3) <> "" Then
MsgBox "Please Choose New Time, Day or Week... " & mycell.Value & " Is Taken!", _
vbOKOnly, "Time Slot Taken"
ElseIf s = "Emma" Then
If mycell.Offset(0, 5) <> "" And mycell.Offset(0, 7) <> "" Then
MsgBox "Please Choose New Time, Day or Week... " & mycell.Value & " Is Taken!", _
vbOKOnly, "Time Slot Taken"
ElseIf s = "Cheryl" Then
If mycell.Offset(0, 9) <> "" And mycell.Offset(0, 11) <> "" Then
MsgBox "Please Choose New Time, Day or Week... " & mycell.Value & " Is Taken!", _
vbOKOnly, "Time Slot Taken"
'If mycell.Offset(0, 1) <> "" And mycell.Offset(0, 3) <> "" And mycell.Offset(0, 5) <> "" And mycell.Offset(0, 7) <> "" And mycell.Offset(0, 9) <> "" And mycell.Offset(0, 11) <> "" Then

'MsgBox "Please Choose New Time, Day or Week... " & mycell.Value & " Is Taken!", _
'vbOKOnly, "Time Slot Taken"
UserForm2.Show
Exit Sub
ElseIf mycell.Offset(0, 1) = "" Or mycell.Offset(0, 3) = "" Or mycell.Offset(0, 5) = "" Or mycell.Offset(0, 7) = "" Or mycell.Offset(0, 9) = "" Or mycell.Offset(0, 11) = "" Then
If MsgBox("Chosen Time Has An Empty Slot" & Chr(13) & "Click Yes to Make Booking or Click No To Exit", vbYesNo, "Make A Booking?") = vbYes Then
Unload UserForm2
UserForm1.Show

End If
End If
End If
End If
End If
End If

Next

Worksheets("Week Selection").Visible = True
Worksheets(w).Visible = False
cls:
Application.EnableEvents = True
Unload UserForm2
End Sub

[Leith Ross]

Hello Simon,

If I interpreted your code correctly, this revised version should work for you. I made some changes to make it easier to read.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Simon Lloyd]

Leith.........Thanks!, i made a few minor changes but it worked well and of course was a whole lot prettier than my efforts!. Now i can move on to Userform1 and start working the code out for where and how the values of the boxes will be placed.

Regards,
Simon