conditional formatting

[abenitez77]

I have this code to copy conditional formatting from 1 textbox to all the textboxes (more than 75 textboxes) in a form. It works when I have the form open and i run it, but when i close it and then reopen the form it is not there again. I open it in design mode to check the other textboxes, it is not saved. Am I missing something?

FYI, I tried copying the info using "format painter" , and it does copy the conditional formatting, but it does not update the textbox name, it is still referencing the previous textbox that I copied it from, so I can't use that.

Format Painter results:
Copying from:
Right([p24_dmg],1)='G'

Copying To:
"p23_dmg"

and it keeps "p24_dmg"

Function AddFormats(ctlSource As Control, frm As Form) As Integer

    Dim ctl As Control
    Dim fcdSource As FormatCondition
    Dim fcdDestination As FormatCondition
    Dim varOperator As Variant
    Dim varType As Variant
    Dim varExpression1 As Variant
    Dim varExpression2 As Variant
    Dim intConditionCount As Integer
    Dim intCount As Integer
    
    intConditionCount = ctlSource.FormatConditions.Count
    
    For Each ctl In frm.Controls
    
        If ctl.Name = ctlSource.Name Then
            ' This is the source.  Don't apply formatting.
        ElseIf ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            Select Case ctl.Name
                Case "legal_entity_id", "curr", "asin", "pubcdap", "pubcdpo", "marketplaceid", "gl_grp", "descr", "upc", "upc6", "cat", "sub_cat", "pubcode", "studio", "brand"
                    ' Skip these, don't need formatting them.
                Case Else
            
                    intCount = 0
                    
                    ' Bulk remove all current FormatConditions
                    ctl.FormatConditions.Delete
                    
                    Do Until intCount = intConditionCount
                        Set fcdSource = ctlSource.FormatConditions.Item(intCount)
                        
                        varOperator = fcdSource.Operator
                        
                        varType = fcdSource.Type
                        varExpression1 = fcdSource.Expression1
                        varExpression2 = fcdSource.Expression2
                        
                        ' Add the FormatCondition
                        ctl.FormatConditions.Add varType, varOperator, varExpression1, varExpression2
                        'ctl.FormatConditions.Add acExpression, , varExpression1
                        
                        ' Reference the FormatCondition to apply formatting.
                        ' Note: The FormatCondition cannot be referenced
                        ' in this manner until it exists.
                        Set fcdDestination = ctl.FormatConditions.Item(intCount)
                        
                        With fcdDestination
                            .BackColor = fcdSource.BackColor
                            .FontBold = fcdSource.FontBold
                            .FontItalic = fcdSource.FontItalic
                            .FontUnderline = fcdSource.FontUnderline
                            .ForeColor = fcdSource.ForeColor
                        End With
                        
                        ' Move to the next FormatCondition
                        intCount = intCount + 1
                        
                    
                    Loop
                    'ctl.FormatConditions.Item(intCount).Modify
                    
            End Select
        End If
    Next ctl
    
    ' Cleanup
    AddFormats = intConditionCount
    MsgBox "There were " & AddFormats & " Conditional Format(s) applied to all text and combo boxes except the source."
    Set ctl = Nothing
    Set fcdSource = Nothing
    Set fcdDestination = Nothing
    Set varOperator = Nothing
    Set varType = Nothing
    Set varExpression1 = Nothing
    Set varExpression2 = Nothing
    intConditionCount = 0
    intCount = 0
    
End Function

 

[arnelgp]

Call the function in the Load Event of the form:

Private Sub Form_Load()
AddFormats Me.theFormattedTextbixName, Me

 

[abenitez77]

It will run every time and still not save it. I want it to run once and save so that it doesn't run each time I open the form.

 

[Bilbo_Baggins_Esq]

It will run every time and still not save it. I want it to run once and save so that it doesn't run each time I open the form.

Are you opening your form in acDesign view?
I don't think formatting changes initiated during runtime can be saved unless the form is opened in acDesign and then saved.

If I get a chance later today I'll do some testing on this.

 

[arnelgp]

' create a test sub in a module:
' and run it

Private Sub test()
Dim frm As Form
DoCmd.OpenForm FormName:="yourFormName", view:=acDesign
Set frm = Forms!yourFormName
Call AddFormats(frm!yourFormattedTextboxName, frm)
Set frm = Nothing
DoCmd.Close acForm, "yourFormName", acSaveYes
End Sub

 

[abenitez77]

' create a test sub in a module:
' and run it

Private Sub test()
Dim frm As Form
DoCmd.OpenForm FormName:="yourFormName", view:=acDesign
Set frm = Forms!yourFormName
Call AddFormats(frm!yourFormattedTextboxName, frm)
Set frm = Nothing
DoCmd.Close acForm, "yourFormName", acSaveYes
End Sub

I ran this code and it did run successfully, but did not save the conditional formatting.

 

[Bilbo_Baggins_Esq]

I ran this code and it did run successfully, but did not save the conditional formatting.

I wonder if you're losing it when the frm object is closed?
Maybe try this?

Private Sub test()

DoCmd.OpenForm FormName:="yourFormName", view:=acDesign

Call AddFormats("yourFormName"!yourFormattedTextboxName, "yourFormName")


DoCmd.Close acForm, "yourFormName", acSaveYes
End Sub

 

[Bilbo_Baggins_Esq]

' create a test sub in a module:
' and run it

Private Sub test()
Dim frm As Form
DoCmd.OpenForm FormName:="yourFormName", view:=acDesign
Set frm = Forms!yourFormName
Call AddFormats(frm!yourFormattedTextboxName, frm)
Set frm = Nothing
DoCmd.Close acForm, "yourFormName", acSaveYes
End Sub

In your code, a great quick test BTW, I'm thinking the formatting changes are applied to an object in memory and then the object is closed before it is saved thus losing the changes?

 

[abenitez77]

I wonder if you're losing it when the frm object is closed?
Maybe try this?

Private Sub test()

DoCmd.OpenForm FormName:="yourFormName", view:=acDesign

Call AddFormats("yourFormName"!yourFormattedTextboxName, "yourFormName")


DoCmd.Close acForm, "yourFormName", acSaveYes
End Sub

No Cigar... Tried this below and didn't work:

 DoCmd.OpenForm FormName:="Form2", view:=acDesign
    Set frm = Forms!Form2
    
    Call AddFormats(Forms("Form2")!p24_dmg, frm)
    
    
    DoCmd.Close acForm, "Form2", acSaveYes

 

[arnelgp]

Its not losing the changes ive tried it. Maybe there are other codes in the form that we dont know.

 

[abenitez77]

form has no other code. Not sure why it does not save.:banghead: