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]

@dmt32 - I am not jumping ship

 

[dalonglong]

1. rename current result sheet "Validated Result"
2. amend Validation_Click as below
3. Run the code to prove that works
4. I will post code to link to correct cell in next post

OLD

'insert results sheet
    Application.ScreenUpdating = False
   Set rpt = Sheets.Add
    rpt.Cells(2, 1) = "Cells"
    rpt.Cells(2, 2) = "Remarks"

NEW

'CLEAR results sheet
    Application.ScreenUpdating = False
    Set rpt = Sheets("Validated Result")
    rpt.Cells.Clear
    rpt.Cells(1, 1) = ws.Name
    rpt.Cells(2, 1) = "Cells"
    rpt.Cells(2, 2) = "Remarks"

@Yongle
I would like to go with your input. Hmm. what i mean is after the first sheet is inserted (from the OLD code), then the sub sequence Run will just refresh that Inserted Sheet. That sheet will be call "validated result".

 

[Yongle]

after the first sheet is inserted (from the OLD code), then the sub sequence Run will just refresh that Inserted Sheet.

Do you want the sheet updated or cleared each time?

 

[dalonglong]

@Yongle

I want it to be updated in the same sheet when i run the code. (the old validated data should be cleared)

 

[Yongle]

Post16# amendments achieve that
What is the problem?

 

[dalonglong]

@Yongle

Yes it acheived that by following step 1:
1. rename current result sheet "Validated Result"

However can step 1 be automated

 

[Yongle]

Once the sheet has been renamed manually it will NEVER require renaming again
Why is it required in the VBA ?
What am I missing?

 

[dalonglong]

@Yongle

Because it is a repetitive task to rename manually as there are not just 1 same file that require validating. it is a file that is used one time only and not be saved. Therefore it will always require a rename by using this method whenever a new file is needed to check

 

[Yongle]

In that case, try this

1. Insert this where variables are declared
Const Res = "Validated Result"

2. Below this line ...
    Set ws = ActiveSheet
... insert these lines
    If ws.Name = Res Then
        MsgBox "cannot run from results sheet"
        Exit Sub
    End If

3. use this instead of code in post#16
'CLEAR results sheet
    Application.ScreenUpdating = False
    On Error Resume Next
    Set rpt = Sheets(Res)
    On Error GoTo 0
    If rpt Is Nothing Then Set rpt = Sheets.Add
    rpt.Name = Res
    rpt.Cells.Clear
    rpt.Cells(1, 1) = ws.Name
    rpt.Cells(2, 1) = "Cells"
    rpt.Cells(2, 2) = "Remarks"

 

[dalonglong]

Thanks, that is exactly what i need,
by having this code, back to the original topic (to link to the cell).. is the below function Application.Goto mentioned in #20 (must be placed in that SHEET's code window) will also need to change accordingly? To placed it in the Sub Validation_Click() instead, i tried shifting it but it could'nt work. appreciate your help!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error Resume Next
    If Target.CountLarge > 1 Then Exit Sub
    Dim cel As Range
    Set cel = Sheets(Range("A1").Value).Range(Target.Value)
    Application.Goto Reference:=cel, scroll:=True
    On Error GoTo 0

End Sub