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
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?
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.
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.