Combobox Sub Worksheet_Change(ByVal Target As Range)

[ayazgreat]

hi All

I need help on the following

I have a combobox on sheet and I want it to fill when a value change on range b1 let me explain you

Sub Worksheet_Change(ByVal Target As Range)<strike></strike>

If range b1 a text A then combobox would fill with AA,BB,CC

If range b1 a text B then combobox would fill with DD,EE,FF

If range b1 a text C then combobox would fill with GG,HH,LL


Thanks

 

[ayazgreat]

Could anybody please resolve it

 

[JoeMo]

Is your combo box a form control or an active-x control? Do you have a list fill range on the sheet with the control? If yes, what is the range? If no, what range would you like to use to hold the list fill? What is the name of the combo box?

 

[My Aswer Is This]

So if Range("B1").value= "A" then you want ComboBox to look like this:

"AA"
"BBB"
"CC"

Is this what you want?

And what ComboBox?

Is it "Combobox1" or "ComboBox2"

You gave no details.

 

[ayazgreat]

it is combobox1 and yes it is activex control
and yes if it is A then it looks like
AA
BB
CC

 

[My Aswer Is This]

What does this mean:

"would fill with AA,BB,CC"

You want all the values in column A B and C. Or what?

 

[RedBeard]

Not quite understanding what the question is, but the following maybe?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Sheet1.Range("B1") Then


    If Sheet1.Range("B1").Value2 = "A" Then
    
        With Sheet1.ComboBox1
            .AddItem "This"
            .AddItem "Is"
            .AddItem "A"
            .AddItem "Test"
        End With
        
    End If
    
End If


End Sub

 

[ayazgreat]

I mean to say that combobox fills with mentioned value
like


Sub Worksheet_Change(ByVal Target As Range)<strike></strike>

If range b1 a text A then combobox1 would fill with AA,BB,CC

If range b1 a text B then combobox1 would fill with DD,EE,FF

If range b1 a text C then combobox1 would fill with GG,HH,LL

 

[ayazgreat]

Not quite understanding what the question is, but the following maybe?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Sheet1.Range("B1") Then


    If Sheet1.Range("B1").Value2 = "A" Then
    
        With Sheet1.ComboBox1
            .AddItem "This"
            .AddItem "Is"
            .AddItem "A"
            .AddItem "Test"
        End With
        
    End If
    
End If


End Sub

[/QUOTE

Red Thanks for your reply but you didn't apply mentioned all three condition and further combobox1 is not clear when required condition does not match

 

[Jaafar Tribak]

Something like this :

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sValue As String
    
    If Target.Address = Range("B1").Address Then
        Select Case Target.Value2
            Case "A"
                sValue = "AA,BB,CC"
            Case "B"
                sValue = "DD,EE,FF"
            Case "C"
                sValue = "GG,HH,LL"
            Case Else
                Exit Sub
        End Select
        With ComboBox1
            On Error Resume Next
                .Clear
            On Error GoTo 0
            .AddItem Split(sValue, ",")(0)
            .AddItem Split(sValue, ",")(1)
            .AddItem Split(sValue, ",")(2)
        End With
    End If
    
End Sub