Solved: Mandatory fields

[Mitchelson]

Hi All,

I would like to make certain fields mandatory so the user can not close the workbook without filling in the blank cells.

The active range is D3:J100

If D3 = "MAC" then E3 is mandatory or
If D3 = "BAU" then F3 is mandatory or
If D3 = "Project" then G3:I3 is mandatory.

Please see attached workbook with example of the table. The cell highlighted in yellow are the mandatory fields.

Attachment 8758

Many Thanks

[Mitchelson]

I found the follow code for Mandatory fields but don't know how change it to suit my workbook.


[vba]Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "IV" '<== change to suit

On Error Goto ws_exit
Application.EnableEvents = False

With ActiveCell

If .Row > 1 Then

If Me.Cells(.Row, WS_RANGE).Value = "Oracle paying to partner" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to supplier" Or _
Me.Cells(.Row, WS_RANGE).Value = "Partner paying to Oracle" Then

If Me.Cells(.Row, "S").Value = "" Or _
Me.Cells(.Row, "W").Value = "" Or _
Me.Cells(.Row, "X").Value = "" Then

MsgBox "Partner name,company and type are mandatory"
.Value = ""
End If
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub [/vba]

[Bob Phillips]

[vba]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow As Long
Dim i As Long
Dim tmp As String
Dim msg As String

With Worksheets("Sheet1")

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 3 To LastRow

tmp = ""
If .Cells(i, "D").Value = "MAC" Then

If .Cells(i, "E").Value = "" Then

tmp = "Cell E" & i & " must be completed" & vbNewLine
End If
ElseIf .Cells(i, "D").Value = "BAU" Then

If .Cells(i, "F").Value = "" Then

tmp = "Cell F" & i & " must be completed" & vbNewLine
End If
ElseIf .Cells(i, "D").Value = "Project" Then

If Application.Count(.Cells(i, "G").Resize(, 4)) < 4 Then

tmp = "Cells G" & i & ":J" & i & " must be completed" & vbNewLine
End If
End If

If tmp <> "" Then
msg = msg & "Cell D" & i & " has a value of " & .Cells(i, "D").Value & " , so:" & vbNewLine & tmp & vbNewLine
End If
Next i

If msg <> "" Then

MsgBox msg, vbExclamation + vbOKOnly, "Mandatory data"
Cancel = True
End If
End With
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[Aussiebear]

Works beautifully Bob

[Mitchelson]

Thank you xld, your code works well.

[vba]If Application.Count(.Cells(i, "G").Resize(, 4)) < 4 Then[/vba]

I understand the above code will look in Rows G, H, I & J but how would you look at cells that are not grouped together e.g. Row G, H, K & L.

[Bob Phillips]

[vba]

If Application.Counta(.Cells(i, "G").Resize(, 2),.Cells(i, "K").Resize(, 2)) < 4 Then
[/vba]

[Mitchelson]

Thank you again xld. The code works well.

[vba]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow As Long
Dim i As Long
Dim tmp As String
Dim msg As String

With Worksheets("Tickets")

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 5 To LastRow

tmp = ""
If .Cells(i, "D").Value = "AL / Sick" Then

If Application.CountA(.Cells(i, "E").Resize(, 1), .Cells(i, "I").Resize(, 1), .Cells(i, "K").Resize(, 1)) < 3 Then

tmp = "Completed all AL / Sick cells before closing workbook" & vbNewLine
End If

ElseIf .Cells(i, "D").Value = "Timesheet" Then

If Application.CountA(.Cells(i, "E").Resize(, 1), .Cells(i, "I").Resize(, 1), .Cells(i, "K").Resize(, 1)) < 3 Then

tmp = "Completed all Timesheet cells before closing workbook" & vbNewLine
End If

ElseIf .Cells(i, "D").Value = "EURC" Then

If Application.CountA(.Cells(i, "F").Resize(, 1), .Cells(i, "H").Resize(, 4), .Cells(i, "S").Resize(, 7), .Cells(i, "AD").Resize(, 5), .Cells(i, "AV").Resize(, 2)) < 19 Then

tmp = "Completed all EURC cells before closing workbook" & vbNewLine
End If

ElseIf .Cells(i, "D").Value = "Fault" Then

If Application.CountA(.Cells(i, "F").Resize(, 6), .Cells(i, "S").Resize(, 11), .Cells(i, "AV").Resize(, 2)) < 19 Then

tmp = "Completed all Fault cells before closing workbook" & vbNewLine
End If

ElseIf .Cells(i, "P").Value = "Fail" Then

If Application.Count(.Cells(i, "Q").Resize(, 2)) < 2 Then

tmp = "Completed all SLA Falire Exceptions before closing workbook" & vbNewLine
End If


End If

If tmp <> "" Then
msg = msg & "Row " & i & " value = " & .Cells(i, "D").Value & vbNewLine & tmp & vbNewLine
End If
Next i

If msg <> "" Then

MsgBox msg, vbExclamation + vbOKOnly, "Mandatory data"
Cancel = True
End If
End With
End Sub

[/vba]
This is the modified code to fit into my workbook but I can't get the last part to work (See code below). When you change the code to look at column P to in see if the word "Fail" it does not pick up the mandatory data fields Q & R.

[vba]ElseIf .Cells(i, "P").Value = "Fail" Then

If Application.Count(.Cells(i, "Q").Resize(, 2)) < 2 Then

tmp = "Completed all SLA Falire Exceptions before closing workbook" & vbNewLine
End If
[/vba]
Can you please explain why this is.

Many thanks

[Mitchelson]

Sorry I forgot, please find attached workbook.

Attachment 8767

[Bob Phillips]

It's a different column so you need a separate If test

[vba]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow As Long
Dim i As Long
Dim tmp As String
Dim msg As String

With Worksheets("Tickets")

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 5 To LastRow

tmp = ""
If .Cells(i, "D").Value = "AL / Sick" Then

If Application.CountA(.Cells(i, "E").Resize(, 1), .Cells(i, "I").Resize(, 1), .Cells(i, "K").Resize(, 1)) < 3 Then

tmp = "Completed all AL / Sick cells before closing workbook" & vbNewLine
End If

ElseIf .Cells(i, "D").Value = "Timesheet" Then

If Application.CountA(.Cells(i, "E").Resize(, 1), .Cells(i, "I").Resize(, 1), .Cells(i, "K").Resize(, 1)) < 3 Then

tmp = "Completed all Timesheet cells before closing workbook" & vbNewLine
End If

ElseIf .Cells(i, "D").Value = "EURC" Then

If Application.CountA(.Cells(i, "F").Resize(, 1), .Cells(i, "H").Resize(, 4), .Cells(i, "S").Resize(, 7), .Cells(i, "AD").Resize(, 5), .Cells(i, "AV").Resize(, 2)) < 19 Then

tmp = "Completed all EURC cells before closing workbook" & vbNewLine
End If

ElseIf .Cells(i, "D").Value = "Fault" Then

If Application.CountA(.Cells(i, "F").Resize(, 6), .Cells(i, "S").Resize(, 11), .Cells(i, "AV").Resize(, 2)) < 19 Then

tmp = "Completed all Fault cells before closing workbook" & vbNewLine
End If
End If

If .Cells(i, "P").Value = "Fail" Then

If Application.CountA(.Cells(i, "Q").Resize(, 2)) < 2 Then

tmp = "Completed all SLA Falire Exceptions before closing workbook" & vbNewLine
End If
End If

If tmp <> "" Then
msg = msg & "Row " & i & " value = " & .Cells(i, "D").Value & vbNewLine & tmp & vbNewLine
End If
Next i

If msg <> "" Then

MsgBox msg, vbExclamation + vbOKOnly, "Mandatory data"
Cancel = True
End If
End With
End Sub
[/vba]

[Mitchelson]

Thank you once again.