[RESOLVED] Change BackColor

[New2vba]

Thanks to much appreciated help from DKenny I now have a Word (2003) userfrom that loads values into comboboxes depending on a previous selection.

In some instances, this will result in some comboboxes having only one choice. In these instances I want to lock the combobox and change the backcolor. I have managed to lock, but can't change the backcolor - I receive an invalid qualifier message when testing.

Also, I'm sure I can shorten/improve the code because some of the items choices in cboChassisMake lead to the same results. For example, "DAF" "ERF" and "Foden" - Any ideas would be appreciated (I did try using OR but kept receiving errors). Here's the code.

With Me.cboGearboxMake
        'First remove existing values from cboGearboxMake
        .Clear
        If Me.cboChassisMake.Value = "DAF" Then
            'Add the appropriate items for DAF
            .AddItem "Eaton"
            .AddItem "ZF"
            'Select standard gearbox
            .ListIndex = 1
        ElseIf Me.cboChassisMake.Value = "ERF" Then
            'Add the appropriate items for DAF, ERF and Foden
            .AddItem "Eaton"
            .AddItem "ZF"
            'Select standard gearbox
            .ListIndex = 1
        ElseIf Me.cboChassisMake.Value = "Foden" Then
            'Add the appropriate items for Foden
            .AddItem "Eaton"
            .AddItem "ZF"
            'Select standard gearbox
            .ListIndex = 1
        ElseIf Me.cboChassisMake.Value = "Iveco" Then
            'Add the appropriate items for Iveco
            .AddItem "ZF"
            'Select standard gearbox
            .ListIndex = 0
        ElseIf Me.cboChassisMake.Value = "MAN" Then
            'Add the appropriate items for MAN
            .AddItem "ZF"
            'Select standard gearbox
            .ListIndex = 0
        ElseIf Me.cboChassisMake.Value = "Mercedes" Then
            'Add the appropriate items for Mercedes
            .AddItem "Mercedes"
[VBCODE[COLOR=Red]].BackColor.RGB = RGB(123, 123, 123)[/[/COLOR]VBCODE]
            .Locked = True
            'Select the standard gearbox
            .ListIndex = 0
        ElseIf Me.cboChassisMake.Value = "Renault" Then
            'Add the appropriate items for Renault
            .AddItem "ZF"
            'Select the standard gearbox
            .ListIndex = 0
        ElseIf Me.cboChassisMake.Value = "Scania" Then
            'Add the appropriate items for Scania
            .AddItem "Scania"
            'Select the standard gearbox
            .ListIndex = 0
        ElseIf Me.cboChassisMake.Value = "Volvo" Then
            'Add the appropriate items for Volvo
            .AddItem "Volvo"
            'Select the standard gearbox
            .ListIndex = 0
        Else
            'Add all items
            .AddItem "Eaton"
            .AddItem "Mercedes"
            .AddItem "Scania"
            .AddItem "Volvo"
            .AddItem "ZF"
        End If
        
    End With
    
End Sub

[DKenny]

You would be better served by using a SELECT CASE sytanx rather than lots of ELSEIF's as below.

Also, when you need to use an objects property multiple time, like Me.cboChassisMake.Value in this code, it is much better to pass that value into a variable and then use the variable. This improves performance as the compiled code only has to "lookup" that property once, rather than multiple times.

You were close on the colour, but did not need the .rgb. I've also moved that out of the SELECT CASE section, as you can make this call once - after you have setup the correct values .

Option Explicit
 
Private Sub cboChassisMake_Change()
Dim SelectedMake As String
 
    SelectedMake = Me.cboChassisMake.Value
 
    With Me.cboGearboxMake
        'First remove existing values from cboGearboxMake
        .Clear
        Select Case SelectedMake
            Case "DAF", "ERF", "Foden"
                'Add the appropriate items for DAF
                .AddItem "Eaton"
                .AddItem "ZF"
                'Select standard gearbox
                .ListIndex = 1
            Case "Iveco", "MAN", "Renault"
                'Add the appropriate items for Iveco
                .AddItem "ZF"
                'Select standard gearbox
                .ListIndex = 0
            Case "Mercedes"
                'Add the appropriate items for Mercedes
                .AddItem "Mercedes"
                'Select the standard gearbox
                .ListIndex = 0
            Case "Scania"
                'Add the appropriate items for Scania
                .AddItem "Scania"
                'Select the standard gearbox
                .ListIndex = 0
            Case "Volvo"
                'Add the appropriate items for Volvo
                .AddItem "Volvo"
                'Select the standard gearbox
                .ListIndex = 0
            Case Else
                'Add all items
                .AddItem "Eaton"
                .AddItem "Mercedes"
                .AddItem "Scania"
                .AddItem "Volvo"
                .AddItem "ZF"
            End Select
            
            If .ListCount = 1 Then
                .Locked = True
                .BackColor = RGB(123, 123, 123)
            Else
                .Locked = False
                .BackColor = RGB(255, 255, 255)
            End If
    End With
End Sub

[New2vba]

Thanks again DKenny seems to work a treat

However, when I included "option explicit" in the code it jumped up to the end of the previous code (after end sub), then I get a compile error.

I deleted this expression it seems to work fine. If I understand it's application correctly (and forgive me if I don't becuase it's straight out of book, and not a great one at that), provided I don't spell anything incorrectly, this won't cause me any problems?