Vba Compile Error :(

[bloodmilksky]

Hi Guys,

I was just wondering if anyone can help me with the below. I am trying to block out groups of dates and am failing horribly as it keeps on giving me a compile error.

I was just wondering if anyone could help me please.

many thanks

Jamie



Option Explicit

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


If Sheets("Request Form").Range("B14") < 26 And Sheets("Request Form").Range("E21") < 10 And AllowedDates Then NewBookingCheck.NewBookingCheck
ElseIf Range("FROM1") > 21 / 12 / 2017 And Range("FROM2") < 31 / 12 / 2017 Then msg = ("Nope")
End If
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

[mdmackillop]

I don't understand this

NewBookingCheck.NewBookingCheck
or this

Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
WorkbookClose


Why not

ThisWorkbook.Close True


Use With to avoid repeated sheet references.


Try this; untested

Option Explicit

Sub TooManyHolidays()
Dim msg As String
Dim Ans As VbMsgBoxResult
Dim AllowedDates As Long
With Sheets("Request Form")
If .Range("B14") < 26 And .Range("E21") < 10 And AllowedDates Then
NewBookingCheck.NewBookingCheck
ElseIf .Range("FROM1") > DateValue("21/12/2017") And .Range("FROM2") < DateValue("31/12/2017") Then
msg = "Nope"
ElseIf .Range("B14") >= 26 Then
msg = " You Don't Have Enough Holiday! Would You Like To Continue? "
Ans = MsgBox(msg, vbYesNo)
If Ans = vbNo Then
.Range("Employee3").ClearContents
.Range("DateRequest").ClearContents
End With
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
WorkbookClose
End If
End With
End Sub

[bloodmilksky]

Thank you

NewbookingCheck is a Macro that uses the users data to book in a holiday on their teams calendar.

can i ask mdmackillop is there a way of doing this with a select case instead as I have a lot more dates to reference?

[mana]

Range("FROM1") > datevalue("21/12/ 2017")

[mdmackillop]

Can you post your workbook?

[bloodmilksky]

There you go ill mail you login details

[bloodmilksky]

Did you manage to get into it?

[mdmackillop]

I did, thanks. Looking at in now.

[bloodmilksky]

I have tried the code adjustment and it is offering me another compile error Else Without IF

[Paul_Hossler]

As far as the original problem goes, these will allow it to compile

I don't see where Mac will add the Select Case




Sub TooManyHolidays()

Dim msg As String
Dim Ans As VbMsgBoxResult
Dim AllowedDates As Integer
If Sheets("Request Form").Range("B14") < 26 And Sheets("Request Form").Range("E21") < 10 And AllowedDates Then
NewBookingCheck.NewBookingCheck

' fix date formats
ElseIf Range("FROM1") > #12/21/2017# And Range("FROM2") < #12/31/2017# Then
msg = ("Nope")
'comment out
' End If
ElseIf Sheets("Request Form").Range("B14") >= 26 Then

[bloodmilksky]

thank you Paul for commenting. do you know any other way?

[greyangel]

2 things I would recommend:
1.) locking the workbook so nobody can look at the other tabs. I was able to find everyone's passwords and usernames by just right click and unhiding the sheet that had the usernames and passwords.

or
2.) Have you ever tried the very hidden feature within excel VBA this makes it so that certain sheets can not be accessed unless they are unhidden by the macro itself.

you could try something like this. You would put this in your Loginform textbox



username = usernametextbox.value
Role = sheets("user Data").find(username, lookin:=xlValues).offset(,2).value
if role = "Admin" then
sheets("user Data").visible = true
else
'This makes it so that the user info can only be unhidden and accessed via VBA script.
Sheets("user data").visible = xlveryhidden
end if
end sub


Also put this code in the "thisworkbook" object so that the code automatically veryhides the desired spreadsheets



Private Sub Workbook_Open()
sheets("user data").visible = xlveryhidden
end sub


I know this kind of goes off topic but I thought that some of the sheets you have should not be seen by everyone. Also please note that I did not test the code above.

[Paul_Hossler]

thank you Paul for commenting. do you know any other way?

To do what?

I just fixed the way VBA likes literal date values in code (#8/11/2017#) i.e. bracketed by #'s, just the way that literal text ("cat") is bracketed by quotes, and go rid of what seemed to be an out of place 'End If'

[bloodmilksky]

Is there a way of Blocking off the Ribbon and Save Features so any adjustments can only be carried out through VBA?

[greyangel]

Is there a way of Blocking off the Ribbon and Save Features so any adjustments can only be carried out through VBA?

Take a look at this. https://www.mrexcel.com/forum/excel-questions/634090-vba-hide-ribbon-others-how-limit-one-workbook.html. I did a search in google. Remember that if you do not know something VBA related always go to google! There is probably a good chance that somebody has tried the same thing you are doing.

[bloodmilksky]

Hi Paul,

could you please post the fixed code