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

 

[Smithy2k7]

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

OK thats ok, if I changed the cells to H393 on sheet 1 and H280 on sheet 2 would that work? To be honest, not really sure they are merged, the cell could just be made bigger to accommodate - I can get the sheet changed if you're able to help with the code please?

 

[My Aswer Is This]

OK thats ok, if I changed the cells to H393 on sheet 1 and H280 on sheet 2 would that work? To be honest, not really sure they are merged, the cell could just be made bigger to accommodate - I can get the sheet changed if you're able to help with the code please?

I think this is beyond my abilities. One is we are dealing with more then one sheet and not sure if the mandatory ranges are the same on all sheets. And you say the mandatory ranges will be changing from time to time. The only way I could see doing this would be if you provide all the sheet names and then set the interior color of all mandatory cells to yellow.
Then you could set more cells to yellow and the script would search for all cells which are yellow.

 

[My Aswer Is This]

I think this is beyond my abilities. One is we are dealing with more then one sheet and not sure if the mandatory ranges are the same on all sheets. And you say the mandatory ranges will be changing from time to time. The only way I could see doing this would be if you provide all the sheet names and then set the interior color of all mandatory cells to yellow.
Then you could set more cells to yellow and the script would search for all cells which are yellow.

So would something like that work? And why must the script run when some cell on the sheet has a value entered. Why not have a script run when you try to close the workbook on run a script.

 

[Smithy2k7]

So would something like that work? And why must the script run when some cell on the sheet has a value entered. Why not have a script run when you try to close the workbook on run a script.

Yeh I think something like that would work. The reason I said for it to run when a certain cell is populated is because it's an operator batch sheet that I'm trying to make it work for and they tend to save their sheets at end of shift and I can't guarantee any issues would be resolved when they're about to go home. However, I am open to other suggestions - your suggestion of using code to look at certain coloured cells would work.
What do you need off me to allow you to guide me in doing it?

 

[My Aswer Is This]

To use my ideal of checking the interior color of each cell that is mandatory to see if it is yellow then.
1. I need the names of all sheets we want to check. You earlier said several sheets.
2. After the script runs what do you want to happen.
3. Do you want a message box to popup showing the sheet name and the cell address of each cell which is colored yellow that does not have some value?
If not that then what do you want to happen if some cell colored yellow has no value in that cell.
We wait to see your answers to these questions. And my script would run when you press a button but it can be activated by some other means. But having it run when a value is not entered into some cell on several sheets would be difficult. Having a script activated if a value is not entered in a cell would not be possible. For any script to run requires some action to be taken not when some action is not taken. Thanks and so get back with me.

 

[My Aswer Is This]

So here is my script:
Script looks for cells in each sheet where the cell interior color is Yellow and has no value in that cell.

Look at the script and change and or add sheet names where you see this:
Case "Alpha", "Charlie", "Echo"

The script puts the cell address and sheet name where the empty cell was found. Into a Popup Message Box.

Sub Check_Range()
'Modified  11/10/2021  12:54:13 AM  EST
Application.ScreenUpdating = False
Dim r As Range
Dim s As String
s = ""
For i = 1 To Sheets.Count
    Select Case Sheets(i).Name
        Case "Alpha", "Charlie", "Echo"
            For Each r In Sheets(i).UsedRange
                If r.Interior.Color = vbYellow And r.Value = "" Then s = r.Address & "  " & Sheets(i).Name & vbNewLine & s
            Next
    End Select
Next
If s <> "" Then
MsgBox "The Below cells were found empty." & vbNewLine & "With sheet names." & vbNewLine & s
Else
MsgBox "No cells found empty"
End If
Application.ScreenUpdating = True
End Sub

 

[Smithy2k7]

So here is my script:
Script looks for cells in each sheet where the cell interior color is Yellow and has no value in that cell.

Look at the script and change and or add sheet names where you see this:
Case "Alpha", "Charlie", "Echo"

The script puts the cell address and sheet name where the empty cell was found. Into a Popup Message Box.

Sub Check_Range()
'Modified  11/10/2021  12:54:13 AM  EST
Application.ScreenUpdating = False
Dim r As Range
Dim s As String
s = ""
For i = 1 To Sheets.Count
    Select Case Sheets(i).Name
        Case "Alpha", "Charlie", "Echo"
            For Each r In Sheets(i).UsedRange
                If r.Interior.Color = vbYellow And r.Value = "" Then s = r.Address & "  " & Sheets(i).Name & vbNewLine & s
            Next
    End Select
Next
If s <> "" Then
MsgBox "The Below cells were found empty." & vbNewLine & "With sheet names." & vbNewLine & s
Else
MsgBox "No cells found empty"
End If
Application.ScreenUpdating = True
End Sub


[/QUOTE]
Thank you - this is how I have altered the code, not sure if I have done it right. Have removed Alpha, Charlie, Echo and replaced with the sheet name and change the colour from Yellow to Green

Sub Check_Range()
'Modified  11/10/2021  12:54:13 AM  EST
Application.ScreenUpdating = False
Dim r As Range
Dim s As String
s = ""
For i = 1 To Sheets.Count
    Select Case Sheets(i).Name
        Case "Poly INT - Stage 1"
            For Each r In Sheets(i).UsedRange
                If r.Interior.Color = vbGreen And r.Value = "" Then s = r.Address & "  " & Sheets(i).Name & vbNewLine & s
            Next
    End Select
Next
If s <> "" Then
MsgBox "The Below cells were found empty." & vbNewLine & "With sheet names." & vbNewLine & s
Else
MsgBox "No cells found empty"
End If
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

So are you saying you only want to look at a sheet name:
"Poly INT - Stage 1"

If not what are the sheet names you want to search?

 

[My Aswer Is This]

So are you saying you only want to look at a sheet name:
"Poly INT - Stage 1"

If not what are the sheet names you want to search?

My script looks at 3 different sheets
Named:
"Alpha"
"Charlie"
"Echo"

 

[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 - I have tested and it works so thank you for tat

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

Hi Dave,
This is very helpful, thank you. Have tested the code and it does work which is a great step forward. Few comments:-
1) is there a way of rather than running the code when it is saved, can the code works when data is entered into a particular cell? So for example, on sheet 1, the code will work and look through and check all the mandatory fields on sheet 1 when the operator enters information into cell H227 which is an end time for a batch?
2) then the same function again when the operators have entered an end time in cell H280?
3) the two sheets are two separate stages in a manufacturing process so need the codes to run separately on each sheet when required
4) dont want to stop operators from saving spreadsheets if data isnt entered because at the end of a shift for example they maybe part way through the process and wont have all information so will need to save it for the next on coming shift

Thank you again for your help