Highlight functionality for indication of empty/missing fields 1

[TBL3]

Hi,

I am working on an application form.
I have many fields that needs to be inputted.
Also, there are mandatory fields that needs to be inputted in order to obtain the final value.

However, my concern is that I wish to code a macro that recognizes all the mandatory fields, and once the mandatory fields are not inputted, the description of the missing field becomes highlighted or change color (indication showing that the field must be filled).

Well, I am lost on how to approach this issue.
Any input or suggestions will be great.Thanks,

 

[Andrzejek]

What program are you using? Excel, Word, Access, other?

What do you have so far?

The simple indication to the user is to mark required fields:
[tt]
First Name [ ] [red]*[/red]
Middle Init [ ]
Last Name [ ] [red]*[/red]
[/tt]

Have fun.

---- Andy

 

[TBL3]

The program I am using is Excel.

I have completed the template of the application form.
There are few macros (button functionality), with other basic IF functionalities within the template.

I do have an indication of a RED STAR besides the mandatory fields.
However, I was wondering if there is a way to program such that after completing the form, if there are any mandatory fields that were empty, that those fields can be highlighted or something to show indication or some sort.

Hope this is more clear.


Regards,
tbl3

 

[SkipVought]

Is this 'form' a sheet or a UserForm?

Waht validation code are you using?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Very simple approach:
In Excel on a User Form, 3 text boxes:
1 - txtFirstName *
2 - txtMI
3 - txtLastName *
and a command button

And let's say First and Last name is required:

Option Explicit

Private Sub CommandButton1_Click()

Select Case ""
    Case Trim(txtFirstName.Text)
        txtFirstName.SetFocus
        MsgBox "I need your First Name"
    Case Trim(txtLastName.Text)
        txtLastName.SetFocus
        MsgBox "I need your Last Name"
End Select

End Sub

Have fun.

---- Andy

 

[TBL3]

It is not a UserForm.
Sorry for not clarifying this previously.
Application Form is on a Worksheet.


tbl3

 

[SkipVought]

You can use CONDITIONAL FORMATTING to emphasize cells that are done or not.

Excel '97-3002: Cells > Conditional Format...

Excel 2007+: Home > Styles > Conditional Format...


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TBL3]

I understand the use of Conditional Formatting.

My situation is that a Final Value is calculated once the last field has been inputted.

However, if I use conditional formatting, how can I make it so that the empty mandatory fields becomes significant once the last field is done? Is there a way to capture or to notice when the user has finished completing the form?

Because from my understanding, if I use conditional formatting, the mandatory fields are automatically highlighted to start with, since it starts empty.

I hope I am clear on what I wish to achieve.

 

[SkipVought]

How are you defining the 'last' field?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TBL3]

The last field is pretty much undefined.

I have created on the sheet a very basic calculating system.
So, once every field that calculator needs has been inputted, it calculates and populates a number.
Then, the obtained number is triggered to the 'Final Value' field on the template.


tbl3

 

[SkipVought]

Then you have to have a procedure that runs each time the user changes the sheet that evaluates each form field and when they all have been validated, then run your summation.

use the Worksheet_Change event. Do you know where to find and how to use?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Skip is right (as always) about needing to use Worksheet_Change to detect when to run the calculate macro. However you could still use conditional formatting if you want to. All you'd need to do is use one cell on the sheet (preferably somewhere hidden, or at least out of sight), to note when all the required cells have been filled. To do so you could simply AND the result of checking whether each input cell has received the appropriate data.

For example, if you require that cell B4 has numeric data and C7 has anything other than blank, then:

First use data validation on B4 to ensure that it only takes the correct data type,
then put the formula = And(B4<>"",C7<>"")
in some other cell, say Z1.

Your condtional formatting in B4 and C7 could then both refer to Z1 and only remove the highlighting on B4 and C7 if Z1 = true.

It also means that your worksheet change even need only check the value of Z1 to decide whether to run the macro.

In fact, if you put the And result on a different sheet, the macro would only run when the input details had been completed.

Tony

 

[TBL3]

Hi Skip,

I do not know where to find or how to use it.
Can you let me know where it is a good place to start?

 

[SkipVought]

right-click the sheet tab and select View Code

This is NOT a MODULE. It is the Code Window for this worksheet, very much like a module.

ABOVE the Code Window are two Drop Downs.

In the L-H Drop Down select Worksheet

In the R-H Drop Down are the EVENTS for the sheet. You will want to use the Worksheet CHANGE event and maybe the Worksheet SelectionChange event.

Before coding anything in the events, I would suggest coding all your procedures for validation that you will call from the CHANGE event. So typically you might have a function called CountEmpty that might be

Function CountEmpty() as integer
   dim r as range
    
   for each r in [YourListOfCellsToFillIn]
      if range(r.value).value = 0 then CountEmpty = CountEmpty +1
   next
End Function

If this were all you were checking then

Private Sub Worksheet_Change(ByVal Target As Range)
  if CountEmpty() = 0 then
    'call your summing procedure
  end if 
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TBL3]

Thanks for the clear explanation.
However, I am still not fully understanding it.

The Final Value cell, which I named 'FINAL_score' is formulated so that its =SUM(T16:54).

The mandatory fields are M25,N25,M26:M28,M43:M46

The cell that displays the final score (=FINAL_score cell) is O38.

Here is the way how I interpreted it, but doesn't make sense how the functionality works.

Function FINAL_score() as integer   
  dim r as range
     
  for each r in [M25,N25,M26:M28,M43:M46]
    if range(r.value).value = 0 then FINAL_score = FINAL_score +1   
  next
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
  if FINAL_score() = 0 then
    'call your summing procedure
    =SUM(T16:T54)      '??'
  end if
End Sub

I believe this is not even anything close to how to approach it, but yea I just am confused on how this works exactly.
Could you elaborate little more regards to the functionality of the code.


Thanks,
TBL3

 

[SkipVought]

The Final Value cell, which I named 'FINAL_score' is formulated so that its =SUM(T16:54).

That 'Final Value Cell' should be OFF LIMITS to data entry, so how can it be the FINAL?

If all the entry cells are filled, THEN the sum can be calculated!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TBL3]

The 'Final Value Cell' should be off limits to data entry.
The name of the range of the cell is 'FINAL_score' that has formula of '=SUM(T16:T54)

BTW is that how I should be coding it? Does it follow comparing to what you have provided?

Just a thought, do you think it will be better if I creat a button that says 'calculate score' so that the final value is only populated once the user clicks the button and not when the last field in inputted? And, if so, when the button is clicked and there are missing mandatory fields, it creats a pop-up msg saying 'fill in all the mandatory fields' as well as highlighting the missing fields.

What is your input in this approach?


tbl3

 

[SkipVought]

Why do you require the user to press a button?

Why not calculate the score when all the requirements are met?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TBL3]

That is what I have it as of right now.

I just thought it would be harder to indicate the missing fields if it is based on just the requirements. This may lead to the mandatory fields being highlighted from the start until they get something inputted?

How do you think I should just do this? I think I am making this way to complicated than it should be.

Bottom line, I have a calculator type of a application sheet which calculates a value once the last field has been inputted. As of now, if mandatory fields are missing, the final value is not calculated and shows nothing (blank). I wish to have a functionality that indicates which of the mandatory fields are missing for the user to identify which of them. Hoping that this indication can be done through either 1. Highlighting the fields that are missed or 2. a msg box pops-up saying which of the fields are missing.

I hope this is clear enough. Sorry about the confusion.


tbl3

 

[SkipVought]

What an incentive for the user to get rid of all the RED cells by entering correct values!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}