https://docs.microsoft.com/ru-ru/office/vba/api/access.formatconditions.addI need to color to change based on the RGB values in the text box.
Public Sub SetFormatConditions() Dim iVal%, sVal$, iCondition% Dim objFrm As Form Dim objCtrl As Control Dim objFC As FormatCondition '---------------------------------------------------------------------------------------------- On Error GoTo SetFormatConditions_Err Set objFrm = Forms("График_ТО").Form For iVal = 2 To 12 sVal = "txtM" & Format(iVal, "00") Set objCtrl = objFrm.Controls(sVal) With objCtrl While .FormatConditions.Count > 0 .FormatConditions.Delete Wend For iCondition = 1 To 2 'Expression.Add (Type, Operator, Expression1, Expression2) Select Case iCondition Case 1 sVal = "[txtRol" & Format(iVal, "00") & "]=-1" Set objFC = .FormatConditions.Add(acExpression, acBetween, sVal) objFC.ForeColor = 255 objFC.BackColor = 13952764 '= light red (rose) objFC.Enabled = False Case 2 sVal = "[txtRol" & Format(iVal, "00") & "]=1" Set objFC = .FormatConditions.Add(acExpression, acBetween, sVal) objFC.ForeColor = 0 objFC.BackColor = 14151142 '= light green objFC.Enabled = False End Select Next iCondition End With Next iVal SetFormatConditions_End: On Error Resume Next Err.Clear Exit Sub SetFormatConditions_Err: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub" & _ "SetFormatConditions - modTools.", vbCritical, "Error!" 'Debug.Print "SetFormatConditions_Line: " & Erl & "." Err.Clear Resume SetFormatConditions_End End Sub
You can have up to 50 format conditions. However, as I said earlier, I think you can place the category colors in a lookup table and skip conditional formatting altogether.I'll try explain what I'm trying to achieve.
I have a student database. There are different categories that can be assigned to the student. Each category has a specific color that makes it easily recognisable to the staff. The student can also be linked to another student.
On the student overview form I need a side panel that displays linked students and colored dots/boxes to represent the category/ies
Since the colors on the category are changable by the user, I can't use the color picker to set up conditional formatting.
Also, there are about 40 different categories, I don't know how many rules you can do with conditional formatting.
Any ideas how I could do this?
Not true.You need to use conditional formatting. Use the type of student to assign the color. You CANNOT use RGB colors from a table. That isn't how conditional formatting works and VBA will not work in a continuous form.
Private Sub Form_Load() RemoveFormatConditions AddFormatConditions End Sub Public Sub AddFormatConditions() Dim con As FormatCondition Dim rs As DAO.Recordset Dim r As Integer Dim g As Integer Dim b As Integer Dim category As String Set rs = CurrentDb.OpenRecordset("tblCategories") Do While Not rs.EOF category = rs!CatID r = rs!Red g = rs!Green b = rs!Blue Set con = Me.CatID.FormatConditions.Add(acFieldValue, acEqual, "'" & category & "'") With con .BackColor = RGB(r, g, b) End With rs.MoveNext Loop Me.Refresh End Sub Public Sub RemoveFormatConditions() Dim con As FormatCondition For Each con In Me.CatID.FormatConditions con.Delete Next con End Sub