Msg box for

[RedOctoberKnight]

Good Morning,

I'm looking to add a msg box that pops up when the daily number of allotted days off is exceeded. My current code shows the msg box but only after the day off has been logged. I'm trying to figure out a way so that if the data that is entered into any of the COMBO boxes says either "DAT" or "FTO", it will trigger the popup box if it will exceed the daily limit and do so before it logs the information.

Private Sub CMDSAVE_Click()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim Sh As Worksheet
Dim IROW As Long

Set Sh = ThisWorkbook.ActiveSheet
IROW = ActiveCell.row

With Sh
    .Cells(IROW, Range("SUP").Column) = UserForm1.TXTSUPAM.Value
    .Cells(IROW, Range("AM_1").Column) = UserForm1.ComboBox1.Value
    .Cells(IROW, Range("AM_1T").Column) = UserForm1.TextBox1.Value
    .Cells(IROW, Range("AM_2").Column) = UserForm1.ComboBox2.Value
    .Cells(IROW, Range("AM_2T").Column) = UserForm1.TextBox2.Value
    .Cells(IROW, Range("AM_3").Column) = UserForm1.ComboBox3.Value
    .Cells(IROW, Range("AM_3T").Column) = UserForm1.TextBox3.Value
    .Cells(IROW, Range("PM_1").Column) = UserForm1.ComboBox4.Value
    .Cells(IROW, Range("PM_1T").Column) = UserForm1.TextBox4.Value
    .Cells(IROW, Range("PM_2").Column) = UserForm1.ComboBox5.Value
    .Cells(IROW, Range("PM_2T").Column) = UserForm1.TextBox5.Value
    .Cells(IROW, Range("PM_3").Column) = UserForm1.ComboBox6.Value
    .Cells(IROW, Range("PM_3T").Column) = UserForm1.TextBox6.Value
    .Cells(IROW, Range("OVN_1").Column) = UserForm1.ComboBox7.Value
    .Cells(IROW, Range("OVN_1T").Column) = UserForm1.TextBox7.Value
    .Cells(IROW, Range("OVN_2").Column) = UserForm1.ComboBox8.Value
    .Cells(IROW, Range("OVN_2T").Column) = UserForm1.TextBox8.Value
    .Cells(IROW, Range("OVN_3").Column) = UserForm1.ComboBox9.Value
    .Cells(IROW, Range("OVN_3T").Column) = UserForm1.TextBox9.Value
    .Cells(IROW, Range("SCKNOTES").Column) = UserForm1.TBDN.Value
    .Cells(IROW, Range("AMAGENT1").Column) = UserForm1.ComboBox10.Value
    .Cells(IROW, Range("AMAGENT2").Column) = UserForm1.ComboBox11.Value
    .Cells(IROW, Range("AMAGENT3").Column) = UserForm1.ComboBox12.Value
    .Cells(IROW, Range("PMAGENT1").Column) = UserForm1.ComboBox13.Value
    .Cells(IROW, Range("PMAGENT2").Column) = UserForm1.ComboBox14.Value
    .Cells(IROW, Range("PMAGENT3").Column) = UserForm1.ComboBox15.Value
    .Cells(IROW, Range("OVNAGENT1").Column) = UserForm1.ComboBox16.Value
    .Cells(IROW, Range("OVNAGENT2").Column) = UserForm1.ComboBox17.Value
    .Cells(IROW, Range("OVNAGENT3").Column) = UserForm1.ComboBox18.Value
    End With
   

  
    Unload Me
    Range("M" & CurrentRow).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

ActiveSheet.Protect , AllowFormattingCells:=True

If ActiveSheet.Range("O1").Value < 0 Then
    MsgBox "ONLY 6 DATs/FTOs ARE ALLOWED PER DAY"
    Exit Sub
   End If


End Sub

Any help would be much appreciated.

Thanks

 

[Micron]

Not sure what the math would be and on what cell or range, but combo has BeforeUpdate event, so maybe do the math first and cancel the update if it's not allowed. You probably would just want to exit the posted sub as well? Not sure, but assuming this is all on a user form module, maybe like:

Dim bolCancel As Boolean '<< userform module level variable

Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ActiveSheet.Range("O1") - Me.Combobox1 < = 0 Then '<< don't know what the math is supposed to be
    Cancel = True
    bolCancel = True
End Sub

and your existing sub ends like

ActiveSheet.Protect , AllowFormattingCells:=True

If bolCancel = True Then
    MsgBox "ONLY 6 DATs/FTOs ARE ALLOWED PER DAY"
    Exit Sub
End If

End Sub

if the data that is entered into any of the COMBO boxes says either "DAT" or "FTO"

except I can't tell if DAT or FTO is a control name or a value that it might hold. You would also have to test both combos; I only did one since I had to guess at its name.