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]

One of the reasons I suggested my script is you said the mandatory cells might be changing from time to time. With my script all you have to do is make the mandatory cell yellow. That way the script does not need to be changed when the mandatory cells change.
We could tell the script to not allow the workbook to be saved if the results show some mandatory cells are not filled in.

 

[Smithy2k7]

T

One of the reasons I suggested my script is you said the mandatory cells might be changing from time to time. With my script all you have to do is make the mandatory cell yellow. That way the script does not need to be changed when the mandatory cells change.
We could tell the script to not allow the workbook to be saved if the results show some mandatory cells are not filled in.

That would be easier, maintenance free pretty much apart from changing the colours of cells.
Two questions:-
I would still like the message to appear when operators save the spreadsheet so they know what is missing but I cant have the script not saving the spreadsheet. The reason for this is the operators may finish their shift part way through the process and not able to complete the sheet so they would save and hand over to the next operator.
My sheet names are:-
Poly INT - Stage 1 - the mandatory fields are G110 / I171 / I218
Stadis 450 RA - Stage 2 - the mandatory fields are K143 / G182
When I amended the code you wrote earlier I got a run time error 13.
How do I change the colour - the cells are currently like a bright green, is there a name for that other than green?
Thank you

 

[My Aswer Is This]

When you give sheet names you need to say
Sheet Named "Alpha"
Sheet Named "Bravo"
I still can not figure out you sheet names

Are you saying
Sheet named "Poly INT - Stage 1"
Sheet named "Stadis 450 RA - Stage 2"

And I'm not able to use your mandatory range address with my most recent script.
If you want too do it that way I suggest using the other posters script.
And you said: when saving the spreadsheet.
I know how to give a warning to stop a workbook from closing but not how to stop a Workbook from saving. Workbook closing and saving are not the same. You can save without closing.

And you said:
When I amended the code you wrote earlier I got a run time error 13.

Show me the script:

 

[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

I like this solution. Due to way the spreadsheet I want to implement this on is used, not allowing it to be saved if not all fields are populated isn't what I need; it's an operator batch card so is feasible that it won't all be filled out at the end of their shift.

I would like it to still run the code when the sheet is saved and Allow the save, but also show the message showing which cells haven't been filled out on which sheet.

Thank you

 

[dmt32]

I like this solution. Due to way the spreadsheet I want to implement this on is used, not allowing it to be saved if not all fields are populated isn't what I need; it's an operator batch card so is feasible that it won't all be filled out at the end of their shift.

I would like it to still run the code when the sheet is saved and Allow the save, but also show the message showing which cells haven't been filled out on which sheet.

Thank you

You can just modify the Function a little to give users the option in Msgbox prompt to Cancel save & enter missing data.
If they choose not to, the cells with missing data are highlighted with Red border for next operator.

Function NotAllComplete() As Boolean
    Dim Cell        As Range, RequiredCells As Range
    Dim msg         As String, strPrompt As String
    Dim Response    As VbMsgBoxResult
    Dim ws          As Worksheet
    
    strPrompt = "Batch Card Entry Not Fully Completed" & Chr(10) & _
                "Do You Want To Enter Data To Marked Cells Shown Before Saving?" & Chr(10) & Chr(10)
    
    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
        Response = MsgBox(strPrompt & msg, 36, "Entry Not Complete")
        NotAllComplete = Response = vbYes
    End If
    
End Function

Dave

 

[MrFox77]

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, I know this is an old thread and maybe you will not be in a position to answer, but your solution was the only one among many threads that I consulted that worked for me.
The only problem I now face is that if I want to close a blank form I cannot do that, since the excel keeps forcing me to fill out all the mandatory fields (which would be fine if I were the one required to fill out the form, but I am not).
Is there any workaround that you could suggest?
Thank you!!

 

[dmt32]

The only problem I now face is that if I want to close a blank form I cannot do that, since the excel keeps forcing me to fill out all the mandatory fields (which would be fine if I were the one required to fill out the form, but I am not).
Is there any workaround that you could suggest?
Thank you!!

Hi,
on possibility would be to make yourself and Admin user

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Admin As Boolean
    Admin = Environ("USERNAME") = "MrFox77"
    Cancel = Not Admin And NotAllComplete
End Sub

Change name shown in BOLD with your network username

Dave

 

[MrFox77]

Hi,
on possibility would be to make yourself and Admin user

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Admin As Boolean
    Admin = Environ("USERNAME") = "MrFox77"
    Cancel = Not Admin And NotAllComplete
End Sub

Change name shown in BOLD with your network username

Dave

Thank you for your prompt response Dave. Should the above be placed in the ThisWorkbook code page?

 

[dmt32]

Thank you for your prompt response Dave. Should the above be placed in the ThisWorkbook code page?

Yes that event should already exist in your workbook - I just included an addition to it.

Dave