Prompt to enter missing values before worbook save

[Thomas Kuriakose]

Dear Sirs,

We have a workbook, where three cell values are mandatory.

If the users do not fill these cell values and try to save the workbook, can we get a prompt of the empty cell references with a message, please enter values in C3, C4 and C5.

Kindly find attached the sample file.

Thank you so much,

with regards,
thomas
__________________________________________________________________

Mod edit : post moved to appropriate forum …​

 

[Marc L]

Hi Thomas !

Can't be done by an Excel formula …

But with a code event to paste to ThisWorkbook module
of a binary (.xlsb) or a macro (.xlsm) workbook :​

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    S$ = Join$(Filter(Sheet1.Evaluate("TRANSPOSE(IF(C3:C5="""",ADDRESS(ROW(C3:C5),3,4)))"), False, False), " & ")
    If S > "" Then
        Cancel = True
        Sheet1.Activate
        MsgBox "Please fill " & S, vbExclamation, "  Control !"
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Thomas Kuriakose]

Respected Sir,

Thank you so much for the code provided.

Apologies for having posted this question in the incorrect forum.

Thank you once again,

with regards,
thomas

 

[Marc L]

You're welcome ! Thanks !

Regards.

​

 

[Marc L]

A little mod to select the first empty cell to fill :​

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    S$ = Join$(Filter(Sheet1.Evaluate("TRANSPOSE(IF(C3:C5="""",ADDRESS(ROW(C3:C5),3,4)))"), False, False), " & ")
    If S > "" Then
        Cancel = True
        Sheet1.Activate:  Range(Split(S, " & ")(0)).Select
        MsgBox "Please fill " & S, vbExclamation, "  Control !"
    End If
End Sub

Regards.
​

 

[Thomas Kuriakose]

Respected Sir,

Thank you once again for the revised code.

Two queries -

(1) Can we have the message for the labels instead of the cell reference. For example, "Project field not filled", Start field not filled" and "End field not filled".

(2) Can we have this code only for the sheet and not for the workbook.

Thank you so much,

with regards,
thomas

 

[Marc L]

Thomas, look at the previous codes, query #2 is yet there with Sheet1 !

Labels prompt :​

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    V = Filter(Sheet1.Evaluate("TRANSPOSE(IF(C3:C5="""",ADDRESS(ROW(C3:C5),3,4)))"), False, False)
    If UBound(V) > -1 Then
        Cancel = True
        Sheet1.Activate:  Range(V(0)).Select
        MsgBox Join(Filter(Evaluate("TRANSPOSE(IF(C3:C5="""",B3:B5))"), False, False), " & ") & " not filled", vbExclamation, "  Control !"
    End If
End Sub

You should Like it !
​

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the revised code.

with regards,
thomas