Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 33

Thread: Un-highlighting Required Fields

  1. #11
    Board Regular
    Join Date
    Jan 2013
    Posts
    310
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    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.

  2. #12
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Un-highlighting Required Fields

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #13
    Board Regular
    Join Date
    Jan 2013
    Posts
    310
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    Oh sorry, I did do that and I saw the message that it's firing but it's still being highlighted in red.

  4. #14
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Un-highlighting Required Fields

    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()
    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
    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 by xenou; Jan 12th, 2018 at 05:04 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #15
    Board Regular
    Join Date
    Jan 2013
    Posts
    310
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

    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.

  6. #16
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Un-highlighting Required Fields

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #17
    Board Regular
    Join Date
    Jan 2013
    Posts
    310
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-highlighting Required Fields

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

  8. #18
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Un-highlighting Required Fields

    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 by xenou; Jan 16th, 2018 at 03:12 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #19
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Un-highlighting Required Fields

    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 by xenou; Jan 16th, 2018 at 03:46 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #20
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,895
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Un-highlighting Required Fields

    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 by xenou; Jan 17th, 2018 at 08:48 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •