Un-highlighting Required Fields

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am trying a different approach to handle required fields. I'm testing another code and where it highlights required fields works great. However, I need it to un-highlight after the required field has been filled. Below is the code that handles the required fields. Any suggestions?

Code:
Public Function SetupRequiredFields(frm As Form)
On Error GoTo Err_Handler

    Dim rs As DAO.Recordset     'Recordset of the form.
    Dim ctl As Access.Control   'Each control on the form.
    Dim strField As String      'Name of the field a control is bound to.
    
    Set rs = frm.Recordset
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox
            'Ignore unbound, or bound to an expression.
            strField = ctl.ControlSource
            If (strField <> vbNullString) And Not (strField Like "=*") Then
                With rs(strField)
                    If (.Required) Or (.ValidationRule Like "*Is Not Null*") Then
                        ctl.BackColor = mlngcRequiredBackColor
                        Call MarkAttachedLabel(ctl)
                    End If
                End With
            End If
        End Select
    Next
    SetupRequiredFields = True
    
Exit_Handler:
    Set ctl = Nothing
    Set rs = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & err.Number & ": " & err.Description, vbExclamation, "SetupRequiredFields()"
    Resume Exit_Handler
End Function

Below is the full code that has been placed in a Module
Code:
Option Compare Database
Option Explicit

'The RGB value to use as a control's Back Color when it has focus.
Private Const mlngcFocusBackColor = &HB0FFFF
'The RGB value to use as Back Color if a control is bound to a required field.
Private Const mlngcRequiredBackColor = &HD0D0FF
'These constants are for assigning/reading the Tag property.
Private Const mstrcTagBackColor = "UsualBackColor"
Private Const mstrcTagSeparator = ";"
Private Const mstrcTagAssignmnent = "="

Public Function SetupForm(frm As Form, Optional iSetupWhat As Integer = &H7FFF)
    Const iSetupRequired = 1
    Const iSetupFocusColor = 2
    
    If (iSetupWhat And iSetupRequired) Then Call SetupRequiredFields(frm)
    If (iSetupWhat And iSetupFocusColor) Then Call SetupFocusColor(frm)
End Function

Public Function SetupFocusColor(frm As Form)
On Error GoTo Err_Handler

    Dim ctl As Access.Control   'Each control on the form.
    
    For Each ctl In frm.Controls
        With ctl
            Select Case .ControlType
            Case acTextBox, acComboBox, acListBox
                If (.OnGotFocus = vbNullString) And (.OnLostFocus = vbNullString) Then
                    .OnGotFocus = "=Hilight([" & .Name & "], True)"
                    .OnLostFocus = "=Hilight([" & .Name & "], False)"
                    .Tag = .Tag & IIf(.Tag <> vbNullString, mstrcTagSeparator, Null) & _
                        mstrcTagBackColor & mstrcTagAssignmnent & .BackColor
                End If
            End Select
        End With
    Next
    SetupFocusColor = True
    
Exit_Handler:
    Set ctl = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & err.Number & ": " & err.Description, vbExclamation, "SetupFocusColor()"
    Resume Exit_Handler
End Function

Public Function SetupRequiredFields(frm As Form)
On Error GoTo Err_Handler

    Dim rs As DAO.Recordset     'Recordset of the form.
    Dim ctl As Access.Control   'Each control on the form.
    Dim strField As String      'Name of the field a control is bound to.
    
    Set rs = frm.Recordset
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox
            'Ignore unbound, or bound to an expression.
            strField = ctl.ControlSource
            If (strField <> vbNullString) And Not (strField Like "=*") Then
                With rs(strField)
                    If (.Required) Or (.ValidationRule Like "*Is Not Null*") Then
                        ctl.BackColor = mlngcRequiredBackColor
                        Call MarkAttachedLabel(ctl)
                    End If
                End With
            End If
        End Select
    Next
    SetupRequiredFields = True
    
Exit_Handler:
    Set ctl = Nothing
    Set rs = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & err.Number & ": " & err.Description, vbExclamation, "SetupRequiredFields()"
    Resume Exit_Handler
End Function

Public Function Hilight(ctl As Access.Control, bOn As Boolean)

    '           bOn = flag: True if receiving focus, False if losing focus.
    Dim strBackColor As String
    
    If bOn Then
        'Assign the 'got focus' color.
        ctl.BackColor = mlngcFocusBackColor
    Else
        'Restore the color from the control's Tag property (white if not found.)
        strBackColor = ReadFromTag(ctl, mstrcTagBackColor)
        If IsNumeric(strBackColor) Then
            ctl.BackColor = Val(strBackColor)
        Else
            ctl.BackColor = vbWhite
        End If
    End If
End Function

Private Function MarkAttachedLabel(ctl As Access.Control)
On Error GoTo Err_Handler
    
    With ctl.Controls(0)
        If Not .Caption Like "*
[*]" Then
            .Caption = .Caption & "*"
            .FontBold = True
            .ForeColor = vbRed
        End If
    End With
Exit_Handler:
    Exit Function
Err_Handler:
    Resume Exit_Handler
End Function

Private Function ReadFromTag(ctl As Control, strName As String) As String

    Dim varArray As Variant
    Dim strValue As String
    Dim i As Long
    
    If ctl.Tag <> vbNullString Then
        varArray = Split(ctl.Tag, mstrcTagSeparator)
        If IsArray(varArray) Then
            For i = LBound(varArray) To UBound(varArray)
                If varArray(i) Like strName & mstrcTagAssignmnent & "*" Then
                    ReadFromTag = Mid(varArray(i), Len(strName) + Len(mstrcTagAssignmnent) + 1&)
                End If
            Next
        End If
    End If
End Function

The code is being called in the Form_Load event.
FYI - this is someone else's code I found online and wanted to try out.

Thank you
 

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
When the form opens, the user will see all the required fields to know what needs to be filled out. The fields are all blank when the form is opened. What I want to do after the user fills out each control is remove the required function as it has already been filled.
So it's going from a blank or Null to a filled field. The user should be able to only see the fields that are required that have not been filled out yet. There are 14 required fields that will be highlighted in red that are blank and will need to be filled out (these are combinations of text and combo boxes). If the user only fills out 12 of these required fields, then we should see the remaining two fields still in red and the rest converted back to white fields and black label boxes.

If it helps, this is the site where I picked up the code I posted in my original post.
http://allenbrowne.com/highlight.html

Everything works great when I implemented that code, but I'm afraid that my requestor is going to ask if it is possible to remove highlighted fields once the user has entered a value.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
okay,
when you add this code to the after update event for the cyclemonth control, do you see the message that confirms the event is firing:
Code:
Private Sub CycleMonth_AfterUpdate()
msgbox "CycleMonth_AfterUpdate Event Fired!"
If Not IsNull(Me.CycleMonth) Then Me.CycleMonth.BackColor = vbWhite
End Sub

You should see something when you enter a value into the (previously empty) field.
 

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Oh sorry, I did do that and I saw the message that it's firing but it's still being highlighted in red.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
okay, so next you want to check the obvious: what is the backcolor of the control?!

If it is already vbWhite then changing it to vbWhite will do nothing:

Code:
Private Sub CycleMonth_AfterUpdate()
[B][COLOR="#FF0000"]msgbox "CycleMonth_AfterUpdate Event Fired!"
msgbox "BackColor of Control is " & me.CycleMonth.BackColor
msgbox  iif(isnull(me.CycleMonth),"CycleMonth is null!", "Value of CycleMonth is " & me.CycleMonth)
msgbox "Note: vbWhite integer constant = " & vbWhite
[/COLOR][/B]If Not IsNull(Me.CycleMonth) Then Me.CycleMonth.BackColor = vbWhite
End Sub

and also we want to know what the value of the control is so that's there too now - three messages, one to confirm the event, one to report the backcolor, and one to report the new value of CycleMonth.

Edit: now four messages, one more so we know what vbWhite is too.
 
Last edited:

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Upon loading of the form, the background color of this control is red.

I added the message boxes and the first message box stated that the event was fired.
The second message states that the backcolor of the Control is 11599871.
The third message box states that the Value if CycleMonth is April,
The fourth message box states that vbwhite integer constant = 16777215.

After I update the control from a blank to a value and these messages pop up, the control backcolor is still red and not being changed to white after a value has been added.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
Okay. So that doesn't work. If you just remove or comment out the code with the SetupFocusColor() function that is another try. The behavior will change a little but you might still get the required field stuff that you want.
 

MHamid

Active Member
Joined
Jan 31, 2013
Messages
385
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

No, that doesn't do what I want either ...
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
You said you put your code in a module. How are you using it in the form?

Also according to the link you gave it should be unhighlighting already. So make sure you are following the instructions and don't have other code or changes in the form that are interfering with this code functioning properly (such as other code you have added to the form or to controls).
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
Also according to the link you gave it should be unhighlighting already.
No, I think I'm wrong. I was reading "will not highlight if it has something in it" but the rest of the sentence is "...if it has something in its got focus or lost focus events".

At any rate, the lost focus event is setting the form back to the required field color. The code from the link doesn't make any changes based on fields being filled in or not - it is entirely based on the field being required or not, and whether a control has the focus or not (regardless of whether there is something in the control or if it is empty).

If you want to change this to make that distinction, you'll probably have to intercept the on current event also, to check fields for whether they are filled in or not (so records that start out with data in them aren't marked required even when they are already filled in). Then you also need to tweak the got focus and lost focus events to use a filled in color (the default backcolor), as well as a required color, and the focus color that Allen is using, so that the control can properly get one of those three colors (1. required - but still empty - color, 2. control with focus color, and 3. required - but filled in - color)
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
I used a little of the code from Allen Browne's site but mainly just rewrote this.
Here would be an example of code in the form. It would highlight required fields that are empty, with the requirement that the fields that are required have a * in their tag property - that is, the single character * as the value of the tag property. By required fields here I only mean that you want the field to be always filled in, not that it is set as a required field as a design feature at the table definition level. As with Allen's code, it only works on textboxes, comboboxes, and listboxes. There isn't any additional highlight of the field with the focus.

Code:
Private Const REQUIRED_BACKCOLOR = &HD0D0FF
Private Const DEFAULT_BACKCOLOR = &HFFFFFF

Private Sub Form_Current()
Dim ctl As Access.Control

    For Each ctl In Me.Controls
        With ctl
            Select Case .ControlType
                Case acTextBox, acComboBox, acListBox
                    If (.Tag = "*") And (Len(ctl & "") = 0) Then
                        .BackColor = REQUIRED_BACKCOLOR
                        .OnLostFocus = "=Highlight([" & .Name & "])"
                    Else
                        .BackColor = DEFAULT_BACKCOLOR
                    End If
            End Select
        End With
    Next

End Sub

Private Function Highlight(ctl As Access.Control)
    Dim strBackColor As String

    If Len(ctl & "") > 0 Then
        ctl.BackColor = DEFAULT_BACKCOLOR
    Else
        ctl.BackColor = REQUIRED_BACKCOLOR
    End If

End Function
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,295
Messages
5,641,401
Members
417,208
Latest member
wendy823

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top