PDA

View Full Version : Solved: comboboxes on a worksheet



lehgzil
09-20-2010, 07:03 PM
in regards with my other post ([SOLVED]3 multilink comboboxes) and with the code from sir xld, im doing a little experiment if it will work without a userform meaning the 2 combobox are on the actual sheet.
the link between combobox1 and combobox2 is working,

but why is the value in the combobox1 duplicating everytime i activate it with clicked or change event? is there a way that it can work like its on a userform without duplicating?


vba:everything on the main sheet.

Sub combobox1_Change()

With Me.ComboBox2

.Clear

Select Case ComboBox1.Value

Case "1"
.AddItem ("a")
.AddItem ("b")
.AddItem ("c")

Case "2"
.AddItem ("1")
.AddItem ("2")
.AddItem ("3")
End Select
End With
End Sub

Sub combobox1_Click()
With Me.ComboBox1
.AddItem ("1")
.AddItem ("2")
End With
End Sub

xlbo
09-20-2010, 10:17 PM
Use a sheet event to populate the combobox rather than click event.
In any case, you need to clear the list if you want to add items to it:

Private Sub ComboBox1_Change()
With Me.ComboBox2

.Clear

Select Case ComboBox1.Value

Case "1"
.AddItem ("a")
.AddItem ("b")
.AddItem ("c")

Case "2"
.AddItem ("1")
.AddItem ("2")
.AddItem ("3")
End Select
End With
End Sub

Private Sub Worksheet_Activate()
With Me.ComboBox1
.Clear
.AddItem ("1")
.AddItem ("2")
End With
End Sub


Alternatively

Private Sub Worksheet_Activate()
With Me.ComboBox1
If .ListCount = 0 Then
.AddItem ("1")
.AddItem ("2")
End If
End With
End Sub

will avoid re-processing the addition of items when it is unnecessary

lehgzil
09-20-2010, 10:35 PM
thanks for the adjustments sir xlbo it worked properly
but i cant run it on private subs, i ran it on just subs. is that ok?

xlbo
09-20-2010, 10:54 PM
Should be fine - private just means that other objects cannot "see" it

lehgzil
09-20-2010, 11:14 PM
aha thanks...

and is it ok if i ask this on this same thread?, regarding to this, if why does the function .rowsource cant get to case 1 or case 2 as a replacement for .additem. i am trying to set the values of the comboboxes based on the data on the other sheet(the same workbook).

what i had tried>
.rowsource >invalid data match
.additem range("a1") works fine on the same sheet
.additem range("sheet2!a1") >didnt work
.additem sheets(sheet2).range("a1") didnt work even with .value
changed sheets to worksheets also.

xlbo
09-20-2010, 11:53 PM
Use the List property of the combobox to set by range:

Private Sub ComboBox1_Change()

Dim arr_Range As Variant

With Me.ComboBox2

.Clear

Select Case ComboBox1.Value

Case "1"
arr_Range = Sheets("Sheet2").Range("A1:A3")

Case "2"
arr_Range = Sheets("Sheet2").Range("c1:c3")
End Select

If Not IsEmpty(arr_Range) Then .List = arr_Range

End With
End Sub

Private Sub Worksheet_Activate()

With Me.ComboBox1
.Clear
.AddItem ("1")
.AddItem ("2")
End With

End Sub

lehgzil
09-21-2010, 12:10 AM
many thanks sir xlbo, this works pretty well... ill go experimenting again...