use the same vba code for all of my combo boxes

Question

So I had to create a couple of drop-down lists in Microsoft access and all of them are supposed to have different font colors. I did write the code for a single combo box :

Private Sub Kombinationsfeld51_AfterUpdate()

 

    If Me.Kombinationsfeld51 = 1 Then Me.Kombinationsfeld51.ForeColor = RGB(255, 255, 2)

    If Me.Kombinationsfeld51 = 2 Then Me.Kombinationsfeld51.ForeColor = RGB(255, 0, 255)

    If Me.Kombinationsfeld51 = 3 Then Me.Kombinationsfeld51.ForeColor = RGB(255, 0, 0)

    If Me.Kombinationsfeld51 = 4 Then Me.Kombinationsfeld51.ForeColor = RGB(0, 255, 0)

    If Me.Kombinationsfeld51 = 5 Then Me.Kombinationsfeld51.ForeColor = RGB(0, 0, 255)



End Sub

However, I want to apply the same code to all of my drop-down lists and not just one, without having to copy and paste the code and keep changing the name of my combo box. The colors are supposed to be the same for all combo boxes. I am very new to both microsoft access and especially VBA so any help would be highly appreciated.

Answer 1

If you prefer VBA, then create a small private subfunction you call from each of the AfterUpdate events:

Private Sub Kombinationsfeld51_AfterUpdate()

    SetKombiColor Me!Kombinationsfeld51

End Sub

Private Sub SetKombiColor(ByRef Control As ComboBox)

    Dim ForeColor   As Long

    Select Case Control.Value
        Case 1 
            ForeColor = RGB(255, 255, 2)
        Case 2
            ForeColor = RGB(255, 0, 255)
        Case 3 
            ForeColor = RGB(255, 0, 0)
        Case 4 
            ForeColor = RGB(0, 255, 0)
        Case 5 
            ForeColor = RGB(0, 0, 255)
    End Select

    Control.ForeColor = ForeColor

End Sub

But, first, do rename your controls to something meaningful.