locked
Form background color change, search and replace RRS feed

  • Question

  • I'm looking to change the back color of both forms and some elements in the forms throughout my project.  Currently I have a few hundred forms with the header, detail & footer with the backcolor set to #969696 as well as lots of text lables that are set to the same backcolor #969696.

    A client asked me if I could make the background lighter so I'm looking for a way to replace all of them with #BFBFBF.  Even better would be to replace the number with a global variable.

    I think what I need is a nested loop that will cycle through each form and within each form, cycle through the elements.  In that I need to check the element for it's backcolor property and if it is #969696, swap it for #BFBFBF.

    Albert Kallal shared the following block of code to change a property on all reports in a project.
    For Each RName In CurrentProject.AllReports
            Debug.Print "working on " & RName.Name
            DoCmd.OpenReport RName.Name, acViewDesign
            Reports(RName.Name).RibbonName = "MyPrintPreview"
            DoCmd.Close acReport, RName.Name, acSaveYes
    Next

    Using his model, my code is as follows but I could use some help cleaning it up.  I know that I'm dealing with the color codes incorrectly and that the public variable may give me problems.

    Thank you all for your help,
    /Joe

    Public StdBackColor = #BFBFBF
    
    Public Sub FormColors()
        Dim FName, OName As Variant
        Dim Fcount, OCount As Integer
        Fcount = 0
        OCount = 0
    
        For Each FName In CurrentProject.AllForms
            Debug.Print "working on " & FName.Name
            DoCmd.OpenForm FName.Name, acViewDesign
            For Each OName In Screen.ActiveForm
                If OName.BackColor = 969696 Then
                    OName.BackColor = StdBackColor
                    OCount = OCount + 1
                End If
            Next
            DoCmd.Close acForm, FName.Name, acSaveYes
            Debug.Print OCount & " objects changed."
            Fcount = Fcount + 1
        Next
        Debug.Print Fcount & " forms changed."
    
    End Sub

    Wednesday, February 8, 2012 9:16 AM

Answers

  • You must declare StdBackColor as a constant, and to specify hexadecimal values in VBA, you must use &H, not #.

    Not all controls have a BackColor property, so you must use error handling to prevent the code from trying to set BackColor if it's not available.

    Public Const StdBackColor = &HBFBFBF
    
    Public Sub FormColors()
        Dim FName, OName As Variant
        Dim Fcount, OCount As Integer
        Dim lngColor As Long
        Fcount = 0
        OCount = 0
    
        For Each FName In CurrentProject.AllForms
            Debug.Print "working on " & FName.Name
            DoCmd.OpenForm FName.Name, acViewDesign
            On Error Resume Next
            For Each OName In Screen.ActiveForm
                Err.Clear
                lngColor = OName.BackColor
                If Err = 0 Then
                    If lngColor = &H969696 Then
                        OName.BackColor = StdBackColor
                        OCount = OCount + 1
                    End If
                End If
            Next OName
            On Error GoTo 0
            DoCmd.Close acForm, FName.Name, acSaveYes
            Debug.Print OCount & " objects changed."
            Fcount = Fcount + 1
        Next FName
        Debug.Print Fcount & " forms changed."
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Model_m Wednesday, February 8, 2012 10:21 AM
    Wednesday, February 8, 2012 9:32 AM
  • Detail should work, but the default name of the heeader is FormHeader and that of the footer is FormFooter:

        With Screen.ActiveForm
            .Detail.BackColor = StdBackColor
            .FormHeader.BackColor = StdBackColor
            .FormFooter.BackColor = StdBackColor
        End With

    You can also use the Section property:

        With Screen.ActiveForm
            .Section(acDetail).BackColor = StdBackColor
            .Section(acHeader).BackColor = StdBackColor
            .Section(acFooter).BackColor = StdBackColor
        End With


    Regards, Hans Vogelaar

    • Marked as answer by Model_m Wednesday, February 8, 2012 2:06 PM
    Wednesday, February 8, 2012 12:20 PM

All replies

  • You must declare StdBackColor as a constant, and to specify hexadecimal values in VBA, you must use &H, not #.

    Not all controls have a BackColor property, so you must use error handling to prevent the code from trying to set BackColor if it's not available.

    Public Const StdBackColor = &HBFBFBF
    
    Public Sub FormColors()
        Dim FName, OName As Variant
        Dim Fcount, OCount As Integer
        Dim lngColor As Long
        Fcount = 0
        OCount = 0
    
        For Each FName In CurrentProject.AllForms
            Debug.Print "working on " & FName.Name
            DoCmd.OpenForm FName.Name, acViewDesign
            On Error Resume Next
            For Each OName In Screen.ActiveForm
                Err.Clear
                lngColor = OName.BackColor
                If Err = 0 Then
                    If lngColor = &H969696 Then
                        OName.BackColor = StdBackColor
                        OCount = OCount + 1
                    End If
                End If
            Next OName
            On Error GoTo 0
            DoCmd.Close acForm, FName.Name, acSaveYes
            Debug.Print OCount & " objects changed."
            Fcount = Fcount + 1
        Next FName
        Debug.Print Fcount & " forms changed."
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Model_m Wednesday, February 8, 2012 10:21 AM
    Wednesday, February 8, 2012 9:32 AM
  • Hans,

    Thank you for the color explanations.  After trying your changes the error handling is not working when it hits an object that has no backcolor property.  Checking ?err in the immediate window returns 0 so that should be ok.

    What's wrong here?

    /Joe

    Wednesday, February 8, 2012 10:20 AM
  • I tested the code. It works for me - if a control doesn't have a BackColor property, Err = 438, and if it does, Err = 0...

    Regards, Hans Vogelaar

    Wednesday, February 8, 2012 10:51 AM
  • Yes, it is showing error 438 and the debug / end window pops up and takes me to the line "lngColor = OName.BackColor"

    What am I doing wrong?

    /Joe

    Wednesday, February 8, 2012 11:06 AM
  • In the Visual Basic Editor, select Tools | Options...

    Activate the General tab.

    Make sure that "Error Trapping" is NOT set to "Break on All Errors". That will cause error handling to be ignored. It should be set to "Break in Class Module" (preferably) or to "Break on Unhandled Errors".

    Click OK.

    Does that solve the problem?


    Regards, Hans Vogelaar

    Wednesday, February 8, 2012 11:12 AM
  • Thank you.  That worked.

    After running it I see that it did not change the form background properties.  I didn't realize that they did not fall under the scope of form objects.

    I thought that inserting the following (with or without an "IF" check) before looping through the objects would do the trick.  

    ActiveForm.detail.backcolor= StdBackColor

    ActiveForm.footer.backcolor= StdBackColor

    ActiveForm.header.backcolor= StdBackColor

    It's not compiling.  What is the correct syntax?

    /Joe

    Wednesday, February 8, 2012 11:33 AM
  • Detail should work, but the default name of the heeader is FormHeader and that of the footer is FormFooter:

        With Screen.ActiveForm
            .Detail.BackColor = StdBackColor
            .FormHeader.BackColor = StdBackColor
            .FormFooter.BackColor = StdBackColor
        End With

    You can also use the Section property:

        With Screen.ActiveForm
            .Section(acDetail).BackColor = StdBackColor
            .Section(acHeader).BackColor = StdBackColor
            .Section(acFooter).BackColor = StdBackColor
        End With


    Regards, Hans Vogelaar

    • Marked as answer by Model_m Wednesday, February 8, 2012 2:06 PM
    Wednesday, February 8, 2012 12:20 PM
  • Thanks for your help.  I'm getting errors again with forms that don't have one of the properties so I need to put in handling.  The question is where do I put the error ignore line so that it will still do the loop through the form objects?  Create two error variables?

    /Joe

    Wednesday, February 8, 2012 3:23 PM
  • Place a line

    On Error Resume Next

    above, and

    On Error GoTo 0

    below these three lines.


    Regards, Hans Vogelaar

    Wednesday, February 8, 2012 4:11 PM
  • OK.  That worked.  The correct choice was to use the section property.  (Your first option for some reason didn't work. Not sure why not.) The only quirk is that forms that had previously appeared with out lines between the sections (Header, Detail, Footer) now have dividing lines.  

    I don't want to add a line of "Screen.ActiveForm.DividingLines = False" because there are some forms that should have them.

    Any ideas?

    /Joe

    Thursday, February 9, 2012 8:48 AM
  • Not sure where the lines come from. Make sure that the Special Effect property of the sections is set to Flat. If it's set to Raised or Sunken, that might cause lines between the sections.

    Regards, Hans Vogelaar

    Thursday, February 9, 2012 12:27 PM