Forcing Cells to be Mandatory

[Smithy2k7]

Hello,
I need some help with VBA that will do the following please:-
At work I have a spreadsheet with upto 32 sheets and in each of the sheet, there are certain cells that must have data entered.

I would like VBA to identify which of these mandatory cells are blank and inform the end user which are blank and that they need to go back and fill them in, but... I would like it to run when they come to the end of data entry for each sheet.

Thank you in advance

 

[My Aswer Is This]

To help we would need specific details. Like what cells are mandatory.
And when exactly is this:
You said:
I would like it to run when they come to the end of data entry for each sheet.

 

[Smithy2k7]

Thank you for replying my question, apologies I should of provided this information with my original post.

Operators fill out the batch card and some pieces of information in the cells that they enter are mandatory and must be entered.
The cells that they must fill out are G110, I171, I218, E25, K143 and J13. There are 5 sheets in this spreadsheet with only two sheets that have the mandatory fields as mentioned.

What I would like the code to do is either:-
When they enter the last piece of information on the sheets, the macro runs and checks to make sure all mandatory fields are completed and if not a dialogue box appears telling them
When they save the spreadsheet the macro runs and tells them if anything is missing

Thanks,
Alex

 

[My Aswer Is This]

Here is a script that will check all ranges mentioned on the active sheet.
You mentioned more then one sheet but did not mention the sheet names.
Put this script in a button on the active sheet we want to check.

Sub Fill_Range()
'Modified  11/8/2021  10:32:32 AM  EST
Application.ScreenUpdating = False
Dim Del As Variant
Dim ans As Long
Dim r As String
Del = Array("G110", "I171", "I218", "E25", "K143", "J13")
ans = UBound(Del)

For i = 0 To ans
If Range(Del(i)).Value = "" Then r = Range(Del(i)).Address & vbNewLine & r
Next
If r <> "" Then
MsgBox "The Below Ranges are empty" & vbNewLine & r
Else
MsgBox "No Ranges are empty"
End If


Application.ScreenUpdating = True
End Sub

 

[dmt32]

The cells that they must fill out are G110, I171, I218, E25, K143 and J13. There are 5 sheets in this spreadsheet with only two sheets that have the mandatory fields as mentioned.

When they save the spreadsheet the macro runs and tells them if anything is missing

If you want to prevent workbook being saved until specified cells in each sheet have been completed then can do this with a function that returns boolean (true / false) value to Cancel the save activity

Try following & see if does what you want

Place this code in a STANDARD module

Function NotAllComplete() As Boolean
    Dim Cell        As Range, RequiredCells As Range
    Dim msg         As String
    Dim ws          As Worksheet
   
    For Each ws In Worksheets(Array("Sheet1", "Sheet2"))
        Set RequiredCells = ws.Range("G110,I171,I218,E25,K143,J13")
       
        For Each Cell In RequiredCells.Cells
            With Cell
                If Len(.Value) = 0 Then
                    msg = msg & ws.Name & " - " & .Address & Chr(10)
                    'mark cell
                    .BorderAround ColorIndex:=3, Weight:=xlThick
                Else
                    .Borders.LineStyle = xlNone
                End If
            End With
        Next Cell
        Set RequiredCells = Nothing
    Next ws
   
    NotAllComplete = CBool(Len(msg) > 0)
   
    If NotAllComplete Then MsgBox "Please Complete The Following Marked Cells" & Chr(10) & msg, 48, "Entry Required"
   
End Function

You will need to change the sheet names shown in BOLD as required

Place following code in the ThisWorkbook code page

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = NotAllComplete
End Sub

When users save workbook the function is called.
Any blank cell(s) in specified range a Red border is placed around them & reported in msgbox - function returns True cancelling save action.

I trust you appreciate that solution only works if your users enable macros.

Hope helpful

Dave

 

[Smithy2k7]

Here is a script that will check all ranges mentioned on the active sheet.
You mentioned more then one sheet but did not mention the sheet names.
Put this script in a button on the active sheet we want to check.

Sub Fill_Range()
'Modified  11/8/2021  10:32:32 AM  EST
Application.ScreenUpdating = False
Dim Del As Variant
Dim ans As Long
Dim r As String
Del = Array("G110", "I171", "I218", "E25", "K143", "J13")
ans = UBound(Del)

For i = 0 To ans
If Range(Del(i)).Value = "" Then r = Range(Del(i)).Address & vbNewLine & r
Next
If r <> "" Then
MsgBox "The Below Ranges are empty" & vbNewLine & r
Else
MsgBox "No Ranges are empty"
End If


Application.ScreenUpdating = True
End Sub

This is great thank you so much - is there anyway to automate i.e run when the end user enters a value in a specific cell? Or would the code only work on a button?
Thank you

 

[Smithy2k7]

If you want to prevent workbook being saved until specified cells in each sheet have been completed then can do this with a function that returns boolean (true / false) value to Cancel the save activity

Try following & see if does what you want

Place this code in a STANDARD module

Function NotAllComplete() As Boolean
    Dim Cell        As Range, RequiredCells As Range
    Dim msg         As String
    Dim ws          As Worksheet
  
    For Each ws In Worksheets(Array("Sheet1", "Sheet2"))
        Set RequiredCells = ws.Range("G110,I171,I218,E25,K143,J13")
      
        For Each Cell In RequiredCells.Cells
            With Cell
                If Len(.Value) = 0 Then
                    msg = msg & ws.Name & " - " & .Address & Chr(10)
                    'mark cell
                    .BorderAround ColorIndex:=3, Weight:=xlThick
                Else
                    .Borders.LineStyle = xlNone
                End If
            End With
        Next Cell
        Set RequiredCells = Nothing
    Next ws
  
    NotAllComplete = CBool(Len(msg) > 0)
  
    If NotAllComplete Then MsgBox "Please Complete The Following Marked Cells" & Chr(10) & msg, 48, "Entry Required"
  
End Function

You will need to change the sheet names shown in BOLD as required

Place following code in the ThisWorkbook code page

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = NotAllComplete
End Sub

When users save workbook the function is called.
Any blank cell(s) in specified range a Red border is placed around them & reported in msgbox - function returns True cancelling save action.

I trust you appreciate that solution only works if your users enable macros.

Hope helpful

Dave

This is very helpful will try this method.
Yeh I understand that it will only run with macros enabled.
Thank you

 

[My Aswer Is This]

This is great thank you so much - is there anyway to automate i.e run when the end user enters a value in a specific cell? Or would the code only work on a button?
Thank you

Yes this script can be run when a end user enters a specific value in a specific cell.
So tell me the specifics.

 

[Smithy2k7]

Yes this script can be run when a end user enters a specific value in a specific cell.
So tell me the specifics.

Thank you - so the specifics are
On sheet one, the mandatory fields are G110, I171 and I218. Then I would like the code to run and check those fields have been completed when the operator has entered information into cell H393:H394 (merged cells)
Then on sheet two, the mandatory fields are E25, H13, K143 and J13. Then I would like the code to run and check those fields have been completed when the operator has entered information in cell H280:H281 (merged cells)
In time more cells will be need to be made mandatory so is it a case of adding the new cells to be checked into the code?

 

[My Aswer Is This]

Thank you - so the specifics are
On sheet one, the mandatory fields are G110, I171 and I218. Then I would like the code to run and check those fields have been completed when the operator has entered information into cell H393:H394 (merged cells)
Then on sheet two, the mandatory fields are E25, H13, K143 and J13. Then I would like the code to run and check those fields have been completed when the operator has entered information in cell H280:H281 (merged cells)
In time more cells will be need to be made mandatory so is it a case of adding the new cells to be checked into the code?

I'm not able to help with Merged cells:
That is beyond my knowledge base