VBA Userform Combobox color validation?

[pangster]

Hi,

I'm trying to add color validation to comboboxes on a userform - but seem to be having issue with the syntax - I've done this before using defined drop down values and not had any issues.. I'm not sure how to amend this code to validate input based on using the scripting dictionary?.. can anyone help or offer advice or suggestions please?

code below

Private Sub cboBox1_Change()
    a = Sheets("SEARCH").Range("CODES").Value
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a, 1)
            If a(i, 1) = (Me.cboBox1) And Not .Exists(a(i, 2)) Then .Add a(i, 2), a(i, 2) & "_content"
        Next
        
        
    If cboBox1.Value = a Then
        cboBox1.BackColor = vbGreen
    ElseIf cboBox1.Value <> a Then
        cboBox1.BackColor = vbRed
    End If
    
       
        On Error Resume Next

 

[Fluff]

can anyone help or offer advice or suggestions please?

Yup.
Explain what you are trying to do, rather than just posting non-working code in the hope that one of us is a mind reader.

 

[pangster]

Yup.
Explain what you are trying to do, rather than just posting non-working code in the hope that one of us is a mind reader.

wow.. that's probably one of the least helpful replies I've ever received on here... and that's saying something given I've been a member for 14 years.. I thought it was pretty clear what the original ask was - and the code works to a point - the piece that doesn't is shown for reference.. not sure how I could have been any clearer.. but you certainly could have posted with a little less attitude.

 

[Fluff]

My apologies if you have taken my post the wrong, it was not meant like that, hence the winking emoji.

Please bear in mind that we cannot see your workbook, nor do we know exactly what you are trying to do.
Whilst I understand that you want to change the backcolour of the combo, I don't know what the criteria are. Added to which you have not said in what way your code is not working.
Your code makes no sense to me as you are not using the dictionary for anything.
If you would like to explain what you are trying to, I will be more than happy to help.

 

[pangster]

My apologies if you have taken my post the wrong, it was not meant like that, hence the winking emoji.

Please bear in mind that we cannot see your workbook, nor do we know exactly what you are trying to do.
Whilst I understand that you want to change the backcolour of the combo, I don't know what the criteria are. Added to which you have not said in what way your code is not working.
Your code makes no sense to me as you are not using the dictionary for anything.
If you would like to explain what you are trying to, I will be more than happy to help.

Fluff - sorry if I was short or rude before.. I've got a stinking cold at the minute and a pounding headache... but that's no excuse for bad manners or being curt with a anyone else... I just read your post in the wrong way


the code I have (which works) is below:

Private Sub cboCombo1_Change()
    a = Sheets("SEARCH").Range("CODES").Value
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a, 1)
            If a(i, 1) = (Me.cboCombo1) And Not .Exists(a(i, 2)) Then .Add a(i, 2), a(i, 2) & "_content"
        Next
        On Error Resume Next
        Me.cboCombo2.List = Application.Transpose(.Keys)
    End With
    With Me.cboCombo2
        .Enabled = True
        .Value = vbNullString
    End With
    With Me.cboCombo3
        .Enabled = False
        .Value = vbNullString
    End With
    With Me.cboCombo4
        .Enabled = False
        .Value = vbNullString
    End With
    With Me.cboCombo5
        .Enabled = False
        .Value = vbNullString
    End With
    
    Call cleardown
    
End Sub

I'm just wanting to know how I can colour the selection in Green if it's valid and Red if it's not (also ignore if blank).. the combobox still allows for users to type as it autofills if they use this approach instead of selecting a drop down..

 

[Fluff]

sorry if I was short or rude before

Absolutely no problem

I just read your post in the wrong way

We've all been there

Ok, how about

     Dim Flg As Boolean
     
    a = Sheets("SEARCH").Range("CODES").Value
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a, 1)
            If a(i, 1) = (Me.cboCombo1) Then
                If Not .Exists(a(i, 2)) Then .Add a(i, 2), a(i, 2) & "_content"
                Flg = True
            End If
        Next i
        If Flg Then
            Me.cboCombo2.List = Application.Transpose(.Keys)
            Me.cboCombo1.BackColor = 45678
        Else
            Me.cboCombo2.Clear
            Me.cboCombo1.BackColor = vbRed
        End If
    End With

 

[pangster]

Absolutely no problem
We've all been there

Ok, how about

     Dim Flg As Boolean
     
    a = Sheets("SEARCH").Range("CODES").Value
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a, 1)
            If a(i, 1) = (Me.cboCombo1) Then
                If Not .Exists(a(i, 2)) Then .Add a(i, 2), a(i, 2) & "_content"
                Flg = True
            End If
        Next i
        If Flg Then
            Me.cboCombo2.List = Application.Transpose(.Keys)
            Me.cboCombo1.BackColor = 45678
        Else
            Me.cboCombo2.Clear
            Me.cboCombo1.BackColor = vbRed
        End If
    End With

this works great!!! - I hadn't even thought about using a flag!!. great solution! and thanks for taking the time to help! - really appreciate it!

 

[Fluff]

My pleasure & thanks for the feedback

 

[Fluff]

A further thought, there is no need for the flag, it can be done like

 a = Sheets("SEARCH").Range("CODES").Value
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(a, 1)
            If a(i, 1) = (Me.cboCombo1) And Not .Exists(a(i, 2)) Then .Add a(i, 2), a(i, 2) & "_content"
        Next i
        If .Count > 0 Then
            Me.cboCombo2.List = Application.Transpose(.Keys)
            Me.cboCombo1.BackColor = 45678
        Else
            Me.cboCombo2.Clear
            Me.cboCombo1.BackColor = vbRed
        End If
    End With