Forcing Required Fields

[scotball]

Hi,

Another query.

I have created a scorecard and I have certain fields which i need completed prior to the coach submitting the scorecard.

Is there a way which i can prompt the coach if they miss a field

The fields i want completed are:

X17, Y17, Z17, AA17, AB17, AC17, AD17 and AF17

Any ideas appreciated.

Lee

 

[Kaps_mr2]

Have you tried Conditional formatting ? thks

Kaps

 

[scotball]

never done it using Conditional Format, bit unsure how to start, could you enlighten me?

LP

 

[Kaps_mr2]

what version of cell do you have ?

the approach is to get the cell colour to be red until it is populated.


Kaps

 

[Weaver]

Try this formula

=IF(ISERROR(FIND("~~",X17&"~"&Y17&"~"&Z17&"~"&AA17&"~"&AB17&"~"&AC17&"~"&AD17&"~"&AF17&"~")),"ALL FIELDS COMPLETE","ALL FIELDS NOT COMPLETE")

This won't stop blanks being submitted but it'll create a warning if any are missing.

 

[Peter_SSs]

Try this formula

=IF(ISERROR(FIND("~~",X17&"~"&Y17&"~"&Z17&"~"&AA17&"~"&AB17&"~"&AC17&"~"&AD17&"~"&AF17&"~")),"ALL FIELDS COMPLETE","ALL FIELDS NOT COMPLETE")

This won't stop blanks being submitted but it'll create a warning if any are missing.

You would need another "~" before X17. As it is if all fields contain data excep X17, this formula returns "ALL FIELDS COMPLETE"

If using a formula approach, what about this?
=IF(COUNTA(X17:AD17,AF17)=8,"Complete","Incomplete")

 

[scotball]

Hey excelR8R

That worked a treat... so can you help with the next part then please?

I have a button which activates my macro and the first thing it does is:

Dim nResult As Long
nResult = MsgBox(Prompt:="Save & Clear Current Session Selected. Proceed?", Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub

How can i modify this to check Z18 and if Z18 = "ALL FIELDS NOT COMPLETE" then prompt with "Please complete all data fields before submitting" then exit sub, else run macro?

Thanks,
Lee

 

[Weaver]

scotball.

See peter's note. Can't imagine what I was thinking - I had something else in mind when I started. I think I thought there might be an easy way to tell which field(s) were missing.

Since you're using VB anyway, I think I'd do the check from VB also.

    If WorksheetFunction.CountA(Range("X17:AD17,AF17")) = 8 Then
        'code for all fields complete
    Else
        'code for missing field
    end if

 

[Weaver]

This might be more useful.

    msg = ""
    For Each cel In Range("X17:AD17,AF17")
        If cel.Value = "" Then
            msg = msg & "cell " & cel.Address & " not complete" & vbCrLf
        End If
    Next
    If msg = "" Then
        'all cells filled
    Else
        MsgBox msg
        'some cells incomplete
    End If

 

[scotball]

This works no problem. Thanks

I am now wondering if I can reference the cell's name eg: AD17 has a title in AD16 called CRN/Tag.

This would mean instead of the prompt saying the cell address it would say the cell title making it easier for the user to know which box is incomplete.