VBA To Check That Value Appears In Single Cell Range Before Starting Macro

[bloodmilksky]

Hi Guys I was just wondering if anyone can help with the below. I have put in AndEvaluate Worksheet Function to check that range D21 has one of the following values "FULL","AM","PM" in it before progressing with the rest of the code. However when I have tried this after numerous adjustments and changes it keeps on coming up with a compile Syntax error :(

I was wondering if anyone can help please




Option Explicit

Sub TooManyHolidays()
Dim msg As String
Dim Ans As VbMsgBoxResult


If Sheets("Request Form").Range("B14") < 26 And Sheets("Request Form").Range("E21") < 10 Then AndEvaluate(WorksheetFunction.If(Sheets("Request Form").Range("FORM3"), "FULL" Or "AM Or "PM") <> 1 Then
NewBookingCheck.NewBookingCheck
ElseIf Sheets("Request Form").Range("B14") >= 26 Then
msg = (" You Dont Have Enough Holiday! Would You Like To Continue? ")
Ans = MsgBox(msg, vbYesNo)
If Ans = vbNo Then
Sheets("Request Form").Select
Range("Employee3").ClearContents
Range("DateRequest").ClearContents
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
WorkbookClose
End If

If Ans = vbYes Then
Sheets("Request Form").Select
Range("Employee3").ClearContents
Range("DateRequest").ClearContents
Range("Employee3") = Application.UserName
ElseIf Sheets("Request Form").Range("E21") >= 11 Then
msg = (" You Cant Book More Than 10 Days In One Request! Would You Like To Continue? ")
Ans = MsgBox(msg, vbYesNo)
If Ans = vbNo Then
Sheets("Request Form").Select
Range("Employee3").ClearContents
Range("DateRequest").ClearContents
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
WorkbookClose
End If
If Ans = vbYes Then
Sheets("Request Form").Select
Range("Employee3").ClearContents
Range("DateRequest").ClearContents
Range("Employee3") = Application.UserName
End If
ElseIf Evaluate(WorksheetFunction.IfError(Sheets("Request Form").Range("FORM3"), "Full" Or "Am" Or "Pm")) <> 0 Then
msg = ("Please Enter A Day Type!")
Ans = vbOK
End If
End If
End Sub

[Paul_Hossler]

OR's don't work that way -- note the parens

(Test1) AND (Test2) AND ((Test3a) OR (Test3b) OR (Test3c))


Not tested


If Sheets("Request Form").Range("B14") < 26 And _
Sheets("Request Form").Range("E21") < 10 Then And _
InStr (Sheets("Request Form").Range("FORM3").Value & "#", "FULL#AM#PM#") <> 0 Then




You can simply the code by using a With / End With




With Sheets("Request Form")

......

If .Range("B14") < 26 And _
.Range("E21") < 10 Then And _
InStr (.Range("FORM3").Value & "#", "FULL#AM#PM#") <> 0 Then


....

End With

[Fennek]

Hi,

try to switch to



InStr ("FULL#AM#PM#", Sheets("Request Form").Range("FORM3").Value & "#") <> 0


regards

[bloodmilksky]

will I need to adjust the bottom of the code as well


ElseIf Evaluate(WorksheetFunction.IfError(Sheets("Request Form").Range("FORM3"), "Full" Or "Am" Or "Pm")) <> 0 Then msg = ("Please Enter A Day Type!")
Ans = vbOK
End If
End If
End Sub

[bloodmilksky]

Hi,

its now offering me a Run-time error 1004 Application Defined or Object Defined error since using the above resolution. sorry for the trouble guys

[bloodmilksky]

would I use that same string of code at the bottom to bring up the error message ?

Dont worry really appreciate your helo :)

[Paul_Hossler]

With Fennick's correction to my overly fast typing (or underly slow brain), try something like this

1. your "Full#Am#Pm#" use different Cases ("Full#Am#Pm#" and "FULL#AM#PM#")

2. Not sure you really need the .Select's

3. I added the With / End With




Option Explicit

Sub TooManyHolidays()
Dim msg As String
Dim Ans As VbMsgBoxResult

With Sheets("Request Form")

If .Range("B14") < 26 And _
.Range("E21") < 10 And _
InStr("FULL#AM#PM#", .Range("FORM3").Value & "#") <> 0 Then

NewBookingCheck.NewBookingCheck

ElseIf .Range("B14") >= 26 Then
msg = (" You Dont Have Enough Holiday! Would You Like To Continue? ")
Ans = MsgBox(msg, vbYesNo)
If Ans = vbNo Then
.Select
Range("Employee3").ClearContents
Range("DateRequest").ClearContents
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
WorkbookClose
End If

If Ans = vbYes Then
.Select
Range("Employee3").ClearContents
Range("DateRequest").ClearContents
Range("Employee3") = Application.UserName
ElseIf .Range("E21") >= 11 Then
msg = (" You Cant Book More Than 10 Days In One Request! Would You Like To Continue? ")
Ans = MsgBox(msg, vbYesNo)
If Ans = vbNo Then
.Select
Range("Employee3").ClearContents
Range("DateRequest").ClearContents
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
WorkbookClose
End If
If Ans = vbYes Then
.Select
Range("Employee3").ClearContents
Range("DateRequest").ClearContents
Range("Employee3") = Application.UserName
End If

ElseIf InStr("Full#Am#Pm#", .Range("FORM3").Value & "#") <> 0 Then
msg = ("Please Enter A Day Type!")
Ans = vbOK
End If
End If

End With

End Sub

[bloodmilksky]

Its now hitting a run time error 1004 on that first line :S

[Paul_Hossler]

Its now hitting a run time error 1004 on that first line :S

What is the line?

I could interpret 'first line' different ways

[bloodmilksky]

This part sorry Paul



With Sheets("Request Form")

If .Range("B14") < 26 And _
.Range("E21") < 10 And _
InStr("FULL#AM#PM#", .Range("FORM3").Value & "#") <> 0 Then