Setting Row Source Property Of Combobox 1

[xlbo]

Hi all - anyone know how to set the rowsource property of a combobox from either an array or a string rather than from a range in excel
TIA
Geoff

 

[acron]

You can use the AddItem method.

e.g.

For Each Item In Array("A", "B", "C", "D")
    Me.ComboBox1.AddItem (Item)
Next

A.C.

 

[xlbo]

Cheers for the reply. Was trying to use the rowsource property as I need a completely new set of data each time something is changed in another combobox. Is there a similar For Each item in combobox
item.delete ???

to enable me to clear the current contents before adding the new values

TIA
Geoff

 

[acron]

Geoff,

You could use ComboBox1.Clear, which should remove all items from the list. You could then have some code triggered by a change in the other combobox rebuild the list. I think to use the RowSource property, you need a data source with identifiable records.

There is a RemoveItem method, but is more suited to removing single items. ComboBox1.RemoveItem(0) will remove the first item.

The For Each Item I used in the earlier example was to loop through the elements of the array and add them to the combobox list - sorry if it confused.

A.C.

 

[xlbo]

Cheers Acron - the combobox.clear and for each item additem work perfectly
Have a star
Just as a little aside, I have posted the whole code I am using and as you can see, have declared sArray with 50 items. Is there a way to set the number of items as a variable so that it can be passed with the actual number of items that are going to be added. I have tried using the reDim statement in an attempt to resize the array to the number of objects in it but can't seem to get the syntax right (if indeed that is the right way to go aboput it)......thoughts ??

If anyone is interested, here is the full code. On combobox
change, looks up values to add to a related combobox

Private Sub cbMulti_Change()
Dim sSheet As Worksheet, sArray(50) As Variant, rSource As String
Set sSheet = Sheets("Sites&quot
x = 0
For i = 2 To sSheet.Range("A65536&quot.End(xlUp).Row
If sSheet.Range("A" & i).Text = ufSites.cbMulti.Text Then
sArray(x) = sSheet.Range("B" & i).Text
x = x + 1
Else
End If
Next i
cbSite.Clear
For Each Item In sArray
If sArray(Item) <> &quot;&quot; Then
Me.cbSite.AddItem (Item)
Else
Exit For
End If
Next
End sub

Thanks
Geoff

 

[xlbo]

Solved it - final code for anyone that is interested in relating comboboxes in excel:

Private Sub cbMulti_Change()
Dim sSheet As Worksheet, sArray() As Variant, rSource As String, nSites As Integer
Set sSheet = Sheets(&quot;Sites&quot
nSites = WorksheetFunction.CountIf(sSheet.Range(&quot;A2:A&quot; & sSheet.Range(&quot;A65536&quot.End(xlUp).Row), cbMulti.Text)
ReDim sArray(nSites)
x = 0
For i = 2 To sSheet.Range(&quot;A65536&quot.End(xlUp).Row
If sSheet.Range(&quot;A&quot; & i).Text = ufSites.cbMulti.Text Then
sArray(x) = sSheet.Range(&quot;B&quot; & i).Text
x = x + 1
Else
End If
Next i

cbSite.Clear
For Each Item In sArray
If Item <> &quot;&quot; Then
Me.cbSite.AddItem (Item)
Else
Exit For
End If
Next


End Sub

 

[acron]

Geoff,

Have not tried this approach, but might the following be a useful shortcut ? :

Private Sub cbMulti_Change()
Dim sSheet As Worksheet, rSource As String
Set sSheet = Sheets("Sites")
cbSite.Clear
For i = 2 To sSheet.Range("A65536").End(xlUp).Row
    If sSheet.Range("A" & i).Text = ufSites.cbMulti.Text Then
    Me.cbSite.AddItem (sSheet.Range("B" & i).Text)
    End If
Next i
End Sub

Cut out the middle man (sArray).

A.C.

 

[xlbo]

Very true Acron (and very nice) - will amend as you suggest in a while but I wanna keep the sArray in there for a mo so's I don't forget it. Gonna use it to build a string of sites for a SQL query. Have another star for the shortened effort tho
Rgds
Geoff