Mandatory field to be validated

[dalonglong]

I would like to make a vba to check all the cell below which contains the word 'mandatory' on row 6 are properly filled up.
Those that i have square in red are the potential error that this code should be giving an error message specifying which cell/cells is giving an issue (upon clicking on save button).
i am new to vba and i would like to explore how i can do that using macro. appreciate if anyone can provide some tips to go about doing this.

 

[Yongle]

Welcome to MrExcel


Run macro from the sheet containing the data

Sub Mandatory()
    Dim ws As Worksheet, rpt As Worksheet, cel As Range
    Dim lastR As Long, lastC As Long, r As Long, c As Long
    Set ws = ActiveSheet
'get last column
    lastC = ws.Cells(7, ws.Columns.Count).End(xlToLeft).Column
'get last row used in any column
    For c = 2 To lastC
        lastR = WorksheetFunction.Max(lastR, ws.Cells(ws.Rows.Count, c).End(xlUp).Row)
    Next c
'insert results sheet
    Application.ScreenUpdating = False
    Set rpt = Sheets.Add
    rpt.Cells(1, 1) = "Cells"
'loop values in cells and write to results sheet
    For c = 2 To lastC
        If ws.Cells(6, c) = "(Mandatory)" Then
            For r = 8 To lastR
                Set cel = ws.Cells(r, c)
                If cel = "" Then
                    rpt.Cells(rpt.Rows.Count, 1).End(xlUp).Offset(1) = cel.Address(0, 0)
                End If
            Next r
        End If
    Next c
End Sub

 

[dalonglong]

Hi @Yongle

Thanks for the quick respond. I want the code to be executed upon clicking on the save button (i tried replacing the first line to Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)) but it didn't work. Would you be able to advise?

 

[Yongle]

Rename the procedure Mandatory

Then go to ThisWorkbook code window and paste this in there

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call Mandatory
End Sub

 

[dalonglong]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call Mandato

Hi @Yongle
I think i am missing something here, I am still not able to execute it. Not sure if i have done this step correctly ( Rename the procedure Mandatory )

 

[Yongle]

Delete all previous code
Copy the code in post#2 and paste it into Module1
Copy the code in post#4 and paste it into ThisWorkbook code window
Select sheet containing the data and Save the workbook

 

[dalonglong]

@Yongle Thank You! I am able to execute it now!

If I want to enhance it such that upon clicking on the Cell that is being reflected upon validating, it will link me back to the main sheet on that specific cell. Is this scenario possible using vba?

 

[Yongle]

Q1 Is this what you want ?
Click on a cell in "results" sheet ... and if that cell value is K27 ... link to K27 in "main" sheet

Q2 What is the name of the "main" sheet ?

Q3 Does the workbook contain any other sheets ?

Q4 Would it be better if the old "results" sheet was cleared and overwritten with new values by procedure Mandatory (instead of creating a new sheet) ?
If so, what will be the name of "results" sheet ?

 

[dalonglong]

@Yongle

Q1) Yes

Q2) The name of the sheet will be changed anytime. therefore, is it possible to return to the tab (main sheet) based on the result tab that is generated on the left side of the tab. Hardcoding to the Sheet name is not an preferable option

Q3) Yes it contain multiple sheets

Q4) Yes, that is a good suggestion as everytime it is ran it created a new sheet, you can name the sheet as "Validated Result"

 

[Yongle]

Q2) The name of the sheet will be changed anytime. therefore, is it possible to return to the tab (main sheet) based on the result tab that is generated on the left side of the tab. Hardcoding to the Sheet name is not an preferable option

Will it always be the FIRST sheet in the workbook ?