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.


Code:
--------------------

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

With Worksheets(w)
Select Case t
Case Is = "Tuesday"
Set r = .Range("A4:A46")
Case Is = "Wednesday"
Set r = .Range("A49:A94")
Case Is = "Thursday"
Set r = .Range("A97:A142")
Case Is = "Friday"
Set r = .Range("A145:A190")
Case Is = "Saturday"
Set r = .Range("A193:A238")
End Select
End With

'On Error GoTo cls
Application.EnableEvents = False

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

Select Case s
Case Is = "Lauren"
C = 1: GoSub TestSlot
Case Is = "Emma"
C = 5: GoSub TestSlot
Case Is = "Cheryl"
C = 9: GoSub TestSlot
End Select

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

If 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
Answer = MsgBox("Chosen Time Has An Empty Slot" & Chr(13) & "Click Yes to Make Booking or Click No To Exit", vbYesNo, "Make A Booking?")
If Answer = vbYes Then
Unload UserForm2
UserForm1.Show
End If
End If

End If
Next mycell

Worksheets("Week Selection").Visible = True
Worksheets(w).Visible = False

cls:
Application.EnableEvents = True
Unload UserForm2

Exit Sub

TestSlot:
If mycell.Offset(0, C) <> "" And mycell.Offset(0, C + 2) <> "" Then
Msg = "Please Choose New Time, Day or Week... " & mycell.Value & " Is Taken!"
MsgBox Msg, vbOKOnly, "Time Slot Taken"
End If
Return

End Sub

 

[Simon Lloyd]

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

Regards,
Simo