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

 

[GTO]

Try:

In a Standard Module:

Option Explicit
  
Sub example()
  With Sheet1 '<--CodeName of sheet
    If (IsNumeric(.Range("B13").Value) Or .Range("B13").Value = vbNullString) _
       And (IsNumeric(.Range("E21").Value) Or .Range("E21").Value = vbNullString) _
       And (IsNumeric(.Range("B12").Value) Or .Range("B12").Value = vbNullString) Then
      
      If (.Range("B13").Value > 25) Or (.Range("B12").Value + .Range("E21").Value > 25) Then
        MsgBox "Nah, you don't get to ask for more time off..."
      End If
      
    End If
  End With
End Sub

Hope that helps,

Mark

 

[bloodmilksky]

its coming up with Variable not define

 

[GTO]

Change 'Sheet1' to the actual CodeName of the sheet possessing the Range we are looking at

 

[bloodmilksky]

still having none of it

Sub Day_Check()  With Request_Form '<--CodeName of sheet
    If (IsNumeric(.Range("B13").Value) Or .Range("B13").Value = vbNullString) _
       And (IsNumeric(.Range("E21").Value) Or .Range("E21").Value = vbNullString) _
       And (IsNumeric(.Range("B12").Value) Or .Range("B12").Value = vbNullString) Then
      
      If (.Range("B13").Value > 25) Or (.Range("B12").Value + .Range("E21").Value > 25) Then
        MsgBox "Nah, you don't get to ask for more time off..."
      End If
      
    End If
  End With
End Sub

 

[GTO]

You changed the CodeName of the worksheet (not the name on the tab) to "Request_Form"?

CROSS-POSTED: http://www.vbaexpress.com/forum/showthread.php?58008-VBA-an-MSGBOX

 

[Krayons]

Sub example()

	With ThisWorkBook.Sheets("Sheet1")

		If (.Range("B13").Value > 25) Or (.Range("B12").Value + .Range("E21").Value > 25) Then
			MsgBox "Nah, you don't get to ask for more time off..."
		Else
			MsgBox "Party hard..."
		End If

	End With

End Sub

 

[Joe4]

Is this something you want to happen automatically?
If so, when would you want it to run (i.e. when the workbook is opened, when certain cells are updated, etc)?

 

[bloodmilksky]

Hi Joe4,

ideally automatically when the cells meet the criteria mentioned.

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

 

[Krayons]

@bloodmilksky

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

Yes, or no?

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

Yes, or no?

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

Yes, or no?