• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

Change backcolor on control that has focus

I currently have form that I use to enter new records that use some vba code that will highlight the field that the user is entering data into. This is similar to using the conditional formatting where the backcolor changes when a field has focus. The only reason why I use vba code instead of the conditional formatting is because I want the backcolor to only change when the user is updating data and not whenever someone just clicks on a control on the form. I have the controls locked down so the only way a user can update the data is by clicking the cmdModify button which unlocks the controls and allows dataentry. It also uses the vba code below to also highlight the field that currently has focus. Once they save their changes the backcolor highlighting will stop. Here's the problem I am running into. I now have a new form with a subform and I cannot get this code to work on the subform. It will only work on the main form. I need to figure out how to modify the vba code to also allow the controls on the subform to be highlighted when a control has focus. Below is the vba code that I use. I fire the code in the cmdModify button to turn it on and then I fire the code again in the cmdSave button to shut it off.

This code is stored in a module called "basChangeFocusColor". I call it like this in the two command buttons:

cmdModify

InitialiseEvents Me

cmdSave

InitialiseEventsOff Me

Is there an easy way to modify the code to also allow it to work on subforms? If so, how and how would I call it on my subform to turn it on and off when the cmdModify & cmdSave buttons are clicked on my main form?

Public Sub InitialiseEvents(ByRef frmThisForm As Form)
  ' Comments: Creates Events for form controls
  ' Params  : None
  ' Owner   : Copyright (c) Boston Centerless, Inc.
  ' Source  : Boston Centerless, Inc. Source Code
  ' Usage   : Permission granted to Boston Centerless, Inc.
  ' Module  : modChangeFocusColor
    Dim ctl As Control
    
    On Error Resume Next

    For Each ctl In frmThisForm
        ctl.OnGotFocus = "=HandleFocus('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Got')"
        ctl.OnLostFocus = "=HandleFocus('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Lost')"
    Next ctl
    
    Err.Clear
End Sub

Public Sub InitialiseEventsOff(ByRef frmThisForm As Form)
  ' Comments: Turns off events for form controls
  ' Params  : None
  ' Owner   : Copyright (c) Boston Centerless, Inc.
  ' Source  : Boston Centerless, Inc. Source Code
  ' Usage   : Permission granted to Boston Centerless, Inc.
  ' Module  : modChangeFocusColor
    Dim ctl As Control
    
    On Error Resume Next

    For Each ctl In frmThisForm
        ctl.OnGotFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Got')"
        ctl.OnLostFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Lost')"
    Next ctl
    
    Err.Clear
End Sub

Public Function HandleFocus(ByVal strFormName As String, _
                            ByVal strControlName As String, _
                            ByVal strChange As String)
  ' Comments: Creates backcolor and font color for Got Focus Events
  ' Params  : None
  ' Owner   : Copyright (c) Boston Centerless, Inc.
  ' Source  : Boston Centerless, Inc. Source Code
  ' Usage   : Permission granted to Boston Centerless, Inc.
  ' Module  : modChangeFocusColor
    Static lngForeColour   As Long
    Static lngFontWeight   As Long
    Static lngBorderStyle  As Long
    Static lngBorderColour As Long
    Static lngBackStyle    As Long
    Static lngBackColour   As Long
    
    On Error Resume Next

    With Forms(strFormName)(strControlName)
        Select Case strChange
            Case "Got"
                ' Save current configuration.
                lngForeColour = vbBlack
                lngFontWeight = .FontWeight
                lngBorderStyle = .BorderStyle
                lngBorderColour = .BorderColor
                lngBackStyle = 1
                lngBackColour = vbWhite
                
                ' Set required configuration.
                .ForeColor = vbBlue
                .FontWeight = 700
                .BorderStyle = 1
                .BorderColor = vbRed
                .BackStyle = 1
                .BackColor = vbYellow
            
            Case "Lost"
                ' Restore saved configuration.
                .ForeColor = vbBlack
                .FontWeight = lngFontWeight
                .BorderStyle = lngBorderStyle
                .BorderColor = lngBorderColour
                .BackStyle = 1
                .BackColor = vbWhite
        End Select
    End With
    
    Err.Clear
End Function

Public Function HandleFocusOff(ByVal strFormName As String, _
                            ByVal strControlName As String, _
                            ByVal strChange As String)
  ' Comments: Creates backcolor and font color for Lost Focus Events
  ' Params  : None
  ' Owner   : Copyright (c) Boston Centerless, Inc.
  ' Source  : Boston Centerless, Inc. Source Code
  ' Usage   : Permission granted to Boston Centerless, Inc.
  ' Module  : modChangeFocusColor
    Static lngForeColour   As Long
    Static lngFontWeight   As Long
    Static lngBorderStyle  As Long
    Static lngBorderColour As Long
    Static lngBackStyle    As Long
    Static lngBackColour   As Long
    
    On Error Resume Next

    With Forms(strFormName)(strControlName)
        Select Case strChange
            Case "Got"
                ' Save current configuration.
                lngForeColour = vbBlack
                lngFontWeight = .FontWeight
                lngBorderStyle = .BorderStyle
                lngBorderColour = .BorderColor
                lngBackStyle = 1
                lngBackColour = vbWhite
            
            Case "Lost"
                ' Restore saved configuration.
                .ForeColor = vbBlack
                .FontWeight = lngFontWeight
                .BorderStyle = lngBorderStyle
                .BorderColor = lngBorderColour
                .BackStyle = 1
                .BackColor = vbWhite
        End Select
    End With
    
    Err.Clear
End Function

Open in new window

0
Lawrence Salvucci
Asked:
Lawrence Salvucci
5 Solutions
 
John TsioumprisSoftware & Systems EngineerCommented:
Have you tried :
cmdModify

InitialiseEvents Me
InitialiseEvents Form_TheNameOfYourSubform

cmdSave
InitialiseEventsOff Form_TheNameOfYourSubform
InitialiseEventsOff Me

Open in new window

In order for this to work the Subform should have code behind (or at least open it in design view --> open VBE -->Close VBE)
0
 
Dale FyeCommented:
Actually,

Since the subform is a control in the main form, you need to modify the code so that it steps into the subform.  Something like:

For Each ctl In frmThisForm
    if frmThisForm.ControlType = 112 then 
        InitialiseEvents(ctrl.form)
    Else
        ctl.OnGotFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Got')"
        ctl.OnLostFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Lost')"
    EndIF
Next ctl

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
For what it's worth, you might want to take a look at:

http://allenbrowne.com/highlight.html

 which works along the same lines and may provide some ideas.

Jim.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
@ John Tsioumpris

I tried your example and it didn't work. There is code behind the subform for other functions, etc. But I added your code and it didn't highlight the control that had focus.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
@ Dale Fye
Hi Dale....I tried your code and that didn't work. It actually stopped it from working on the main form when I added your code changes.
0
 
Dale FyeCommented:
Lawrence,

It shouldn't, it should simply call the code (recursively), so that when it reaches the subform, it then calls the function again, and loops through all of the controls in the subform, then, when it leaves that drops out of the function the 2nd time, it should continue with the other controls on the form.

Have you tried putting a breakpoint in the code, and stepping through it, to determine what is happening?
0
 
Dale FyeCommented:
Oh, where is the code?  if it is in a module in the code behind the main form, it might be causing a problem with the subform calling that code.  Move the code to a standard code module if it is currently in the main forms code module.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
The code was in a standard module. I am going to try the breakpoint and see what's happening. I will post back shortly.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
@ Dale
When I run the breakpoint I get this error when it gets to the "InitialiseEvents (ctl.Form)" line of the code

"you entered an expression that has an invalid reference to the property"

Then it bypasses the 'Else' portion of the IF statement and goes right to the "Next ctl" and just loops through that over and over. It doesn't even hit these lines of code:

ctl.OnGotFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Got')"
ctl.OnLostFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Lost')"

Open in new window


And all I did was open the form and clicked the "cmdModify" button.
0
 
Dale FyeCommented:
sorry, that should have read:

if ctl.ControlType = 112 then
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
It's now working on the main form again but still nothing on the subform.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
When I put a breakpoint in there again it's bypassing the "InitialiseEvents (ctl.Form)" and going straight to the "Else" portion of the IF statement. Could it be because the main form has the focus when I click the cmdModify on the main form so it doesn't see the subform?
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Here's something strange. When I step into the breakpoint on this line of code the 'frmThisForm.Name' shows the subform name. And when I step into the breakpoint on the 'ctl.Name' it shows the first label on the subform, 'lblItem'. The cursor is currently on the main form in one of my controls when I click the cmdModify. And it still highlights the backcolor on the main form. Why would I see the subform name and the first label in that line of code? Now I'm even more confused.

ctl.OnGotFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Got')"

Open in new window

0
 
Dale FyeCommented:
As it loops through the controls collection, it should eventually find a control, where the controlType = 112.  Try this:

For Each ctl In frmThisForm.controls
    debug.print ctl.name, ctl.controlType
    if ctl.ControlType = 112 then 
        InitialiseEvents(ctrl.form)
    Else
        ctl.OnGotFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Got')"
        ctl.OnLostFocus = "=HandleFocusOff('" & frmThisForm.Name & "', '" & ctl.Name & "', 'Lost')"
    EndIF
Next ctl

Open in new window

Notice that I've added the ",Controls" to the end of the "For each " line.

This should print out the name and control type of each control in the main form, as you loop through the main forms controls.  Check to see if any of the controls have the name of the subform.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
None of the controls in the list are on the subform. It does find the subform itself since it's a control on the main form. But none of those listed are on the subform itself.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I was able to find a solution with some help from Google. Here's what I did....These functions are only used on subforms. I am still using the main code I posted earlier to run these functions on main forms or single forms with no subforms. For the subforms I use the following code.
When a user clicks the 'Modify' command button on the main form it then fires the 'ToggleColors' function on the subform to hightlight the controls that have focus. Then when they click the 'Save' command button on the main form it then fires the 'ToggleColorsOff' function on the subform to turn off the highlighting function. It's not the best solution but it works and only needs to be used when there is a subform attached.


Private Sub ToggleColors()
Dim ctl As Control
Dim strGot As String
Dim strLost As String

    For Each ctl In Me.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox
            With ctl
            strGot = "=SetFocusCtla([" & ctl.Name & "].[Name], True)"
            strLost = "=SetFocusCtla([" & ctl.Name & "].[Name], False)"
                .OnGotFocus = strGot
                .OnLostFocus = strLost
            End With
        End Select
    Next
End Sub

Private Sub ToggleColorsOff()
Dim ctl As Control
Dim strGot As String
Dim strLost As String

    For Each ctl In Me.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox
            With ctl
            strGot = "=SetFocusCtlOff([" & ctl.Name & "].[Name], True)"
            strLost = "=SetFocusCtlOff([" & ctl.Name & "].[Name], False)"
                .OnGotFocus = strGot
                .OnLostFocus = strLost
            End With
        End Select
    Next
End Sub

Function SetFocusCtl(strCtlNameWithFocus As String, blnFocus As Boolean)
Dim strActiveColor As String: strActiveColor = vbRed
Dim strNonActiveColor As String: strNonActiveColor = vbBlack
Dim strActiveBackColor As String: strActiveBackColor = vbYellow
Dim strNonActiveBackColor As String: strNonActiveBackColor = vbWhite
Dim strActiveForeColor As String: strActiveForeColor = vbBlue
Dim strNonActiveForeColor As String: strNonActiveForeColor = vbBlack
Dim ctl As Control
 
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox
            If ctl.Name = strCtlNameWithFocus Then
                ctl.BorderColor = strActiveColor
                ctl.BackColor = strActiveBackColor
                ctl.ForeColor = strActiveForeColor
                ctl.FontWeight = 700
            Else
                ctl.BorderColor = strNonActiveColor
                ctl.BackColor = strNonActiveBackColor
                ctl.ForeColor = strNonActiveForeColor
                ctl.FontWeight = 400
            End If
        End Select
    Next ctl
End Function

Function SetFocusCtlOff(strCtlNameWithFocus As String, blnFocus As Boolean)
Dim strNonActiveColor As String: strNonActiveColor = vbBlack
Dim strNonActiveBackColor As String: strNonActiveBackColor = vbWhite
Dim strNonActiveForeColor As String: strNonActiveForeColor = vbBlack
Dim ctl As Control

    For Each ctl In Me.Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox
            If ctl.Name = strCtlNameWithFocus Then
                ctl.BorderColor = strNonActiveColor
                ctl.BackColor = strNonActiveBackColor
                ctl.ForeColor = strNonActiveForeColor
                ctl.FontWeight = 400
            Else
                ctl.BorderColor = strNonActiveColor
                ctl.BackColor = strNonActiveBackColor
                ctl.ForeColor = strNonActiveForeColor
                ctl.FontWeight = 400
            End If
        End Select
    Next ctl
End Function

Open in new window

0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Was able to find my own solution with guidance from the contributors and google.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now