VBA and IF Statements

[bloodmilksky]

Hi Guys, I hope you are all well.

I was just wondering if anyone could help. I have a holiday form that has Remaing Holidays in B12, Holidays Taken In B13 and requested in E21. What I was wondering if anyone knows how I would have a MSGBOX pop up

• If B13 EXCEEDS 25 Days or
• If B12 & E21 EXCEED 25

Any Help would be greatly appreciated

Many thanks

Jamie

 

[bloodmilksky]

Do you want these message boxes to appear when: you open your excel file?

no

Do you want these message boxes to appear when: you enter a value into a certain cell?

Yes

Do you want these message boxes to appear when: you click a button?

no

 

[Joe4]

What is in cells B13, B12, and E21?
Hard-coded values or formulas?
How are these values populated/updated?

VBA has Event Procedures, which are basically events you can use to trigger VBA to run automatically. So in order to use it, you need to determine which event to put the code in.
Events include things like:
- when the file is opened
- when the worksheet is changed (can narrow it to just look at specific cells)
- anytime any calculation is made (I usually try to avoid this one, as it would run a lot, and often when not needed)
- before the file is saved or closed
And there are various others.

So we would need to determine exactly which event to tie the code to. Only you know the data and workflow of it, so we would be looking for you to tell us when it should run.

 

[bloodmilksky]

so dates are entered into B21(from) and C21(to) and then these dates would affect the amout of days the user has taken(B13) Has Remaining (b12) and the total of the holiday requested (e21)

b12 - =IFERROR(INDEX('Hidden Sheet'!E2:E30,MATCH(B7,Employees,0))&" Days","")
b13 - =IFERROR(INDEX('Hidden Sheet'!D2:D30,MATCH(B7,Employees,0))&" Days","")
e21 - =IF(OR(D21="AM",D21="PM"),NETWORKDAYS(B21,C21)/2,NETWORKDAYS(B21,C21))

so it would really be when the user enters the dates is ideally when it would run and if possible automatically?

Just wanted to say to thank you all for helping me with this, I am still new and really appreciate all of this help.

many thanks

Jamie

 

[Joe4]

I think this will do what you want.

Right-click on the sheet tab name at the bottom of the screen, select View Code, and paste this code in the resulting VB Editor Window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if cells B21 or C21 are updated manually
    If Intersect(Target, Range("B21:C21")) Is Nothing Then Exit Sub
    
'   Check values in B13, B12, and E21
    If Range("B13") > 25 Or (Range("B12") > 25 And Range("E21") > 25) Then
        MsgBox "Here is your message box!", vbOKOnly, "CONDITIONS MET!"
    End If

End Sub

Now, test it out by editing the value on your sheet.

 

[bloodmilksky]

Thats great thank you ever so much JOE 4. can I also ask just out of curiosity and for further learning can you change a Worksheet_change Into a Sub(macro) or do you have to rewrite it? as I know the triggers are different. sorry am just curious

 

[Joe4]

Just copy the body of the code and put it into a standard "Sub".
But you will need to replace all references to "Target" with some defined range (in a Worksheet_Change Event Procedure, Target is the cell being updated and is dynamic).

You can read up on Event Procedures here: Events In Excel VBA

 

[bloodmilksky]

so I had a go at adjusting the code and turned it into a macro to work with the requesting macro but it keeps on saying that the there isnt enough holiday when the sum doesnt add up to 25. Could I ask for a bit of help?

 

[Joe4]

Please post your code, and provide what is found in each cell that the macro references.

 

[bloodmilksky]

Thank you @Joe 4

I have changed some of the cell refernces as there was text values that were causing the macro to bug.

References
C12 Remaining Holidays
E21 Holiday To Be Requested
C13 Holiday already Taken
Holdates Is the where the dates for the holiday
Employee3 Is Users details
PreviousHoli is a range that shows the Users previous holiday request
NewBookingCheck Is the holiday request Macro

Sub TooMuchHoliday()

    If Range("C12").Value - Range("E21").Value < 25 Then
        Msg = " You Dont Have Enough Holiday " & Application.UserName & " Would You Like To Continue ? "
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbNo Then
                Range("HOLDATES").Select
                Selection.ClearContents
                Application.DisplayAlerts = False
                ThisWorkbook.Save
                Application.DisplayAlerts = True
                Application.Quit
                End If
    If Ans = vbYes Then
        Msg = " Please Delete Some Holiday To Free Some Time " & Application.UserName
                Range("Employee3").Select
                Selection.ClearContents
                Range("PreviousHoli").Select
                Selection.ClearContents
                Range("HOLDATES").Select
                Selection.ClearContents
                Range("Employee3") = Application.UserName
                End If
                
    ElseIf Range("C13").Value + Range("E21").Value > 25 Then
        
        Msg = " You Dont Have Enough Holiday " & Application.UserName & " Would You Like To Continue ? "
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbNo Then
                Range("HOLDATES").Select
                Selection.ClearContents
                Application.DisplayAlerts = False
                ThisWorkbook.Save
                Application.DisplayAlerts = True
                Application.Quit
                End If
    If Ans = vbYes Then
        Msg = " Please Delete Some Holiday To Free Some Time " & Application.UserName
                Range("Employee3").Select
                Selection.ClearContents
                Range("PreviousHoli").Select
                Selection.ClearContents
                Range("HOLDATES").Select
                Selection.ClearContents
                Range("Employee3") = Application.UserName
                End If
                
    ElseIf Range("C12").Value + Range("C13").Value < 25 Then
            
            Run "NewBookingCheck"
            
    End If


End Sub

 

[Joe4]

C12 Remaining Holidays
E21 Holiday To Be Requested
C13 Holiday already Taken

I am also looking for details of an actual example, i.e. what are the numbers in C12, E21, and C13?
Give me an actual example that is not working the way it should.

Also, are all your named ranges on the same sheet as all this data, or are things spread over numerous sheets?