oh God....i tried so many ways and nothing is going into my combobox!!! its  on an excel worksheet and what ive tried is as follows

1. in the listfillrange i put sheet2!d1:d20 - not working
2. in the vb code: Combobox1.ListFillRange=sheet2.Range(d1:d20)
3. Combobox1.Value=Range.Value()

cant use add item because the list is too big....

pleaseeee help...immediately....thanx   

[Edited by mayanair on 04-06-2003 at 12:17 PM GMT]

[Edited by mayanair on 04-06-2003 at 12:18 PM GMT]

try this...

Worksheets(1).OLEObjects(1).ListFillRange = "D120"

AddItem would also work...so would using the "Shape" object/collection...

____________________________
Eggheads unite! You have nothing to lose but your yolks.

it still doesnt work, im not sure why is it not reading the list from the other sheet. ive tried to modify ur code to sheet5.OleObjects.Listfillrange=D120 which is not working as well....hmm...what is the possible problem for this?

What is the error you get?

And if your data is on a sheet other than the sheet with your control, your code will need to be modified a bit...

'Original Worksheets(1).OLEObjects(1).ListFillRange = "Sheet2!A1:A20" 'Could also use this Sheets("Sheet1").OLEObjects("Combobox1").ListFillRange = "Sheet2!A1:A20" 'Or this... Sheet1.ComboBox1.ListFillRange = "Sheet2!A1:A20"

This code works for me...

[Edited by ~Bean~ on 05-06-2003 at 07:48 AM GMT]

____________________________
Eggheads unite! You have nothing to lose but your yolks.

its ok...ive solved that problem....i used the listfillrange property of the combobox and filled it up as 'nameofsheet'!D920

now i would like to ask how can i fill in the combobox from a list of data from two different worksheets? Thanx for ur help

Use AddItem

____________________________
Eggheads unite! You have nothing to lose but your yolks.

hmm...anyway i did it the easier but hassle way which is to separate the delete function for each worksheet...

another question is to link two Combobox, like one for country and another one for states....hope u cpuld explain to me how to do that? thanx a million

One combo box content is dependant on the other combo box?

This could get complicated...do you have a Database (i suppose a set of excel sheets could be considered a database) of Countries and Provinces/States that you'll be using?

Once you have this, or at least a general design, you need to place code in the Change event of the Country combo box that (re)sets the ListFillRange (or Items) of the States/Provinces combo box...that way every time the user selects a country, the corresponding states are populated into the "State" combo box...post back if you need more assistance

____________________________
Eggheads unite! You have nothing to lose but your yolks.

Ok let me explain how my database looks like, Its in excel.

Country        State       ZipCode
Malaysia     Kelantan     523525
Malaysia     Johor         23555
US             California    4254525
US             Texas         534555

Ok, so now i would prefer the user to type in the name of the country in a textbox and then in the combobox, the list of states for that country will appear. and then when the user clicks on the display button, the zipcode will be displayed....

I hope its not too hard....i was nearly thinking of using Access instead but im more well verse in Excel.....thanx for ur help and suggestions

You may want to reconsider using Access, especially if you're going to allow the user the ability to Add/Edit/Delete Countries/States/Zip Codes. Don't get me wrong, this can be done in Excel, just this is what Access was designed to do. Since you're familiar with Excel I will give a few pointers to help get you started...

To Add Values to Combo Box without Duplicating Entries I use This...

'Beans Collection Trick Dim AllCells As Range Dim Cell As Range Dim NoDupes As New Collection Dim Item As String              '   The items are in A2:A5     Set AllCells = Range("A2:A5")      ' Adds all Values from the Range ' into a Collection - we ignore the error caused by ' adding a dupe value to the collection and thus get ' only unique values On Error Resume Next     For Each Cell In AllCells         NoDupes.Add Cell.Value, CStr(Cell.Value)         ' Note: the 2nd argument (key) for the Add method must         ' be a string (see Add method for Collections for more info)     Next Cell '   Resume normal error handling     On Error GoTo 0 '   Sort the collection (optional)          '   Add the items to a List/Combo     For Each Item In NoDupes         Worksheets(1).Combobox(1).AddItem Item     Next Item

btw there are MANY zip codes for each state, are you going to account for that? i.e., Texas

Country        State       ZipCode
Malaysia     Kelantan     523525
Malaysia     Johor         23555
US             California    4254525
US             Texas         53455 (Houston)
US             Texas         53460 (Dallas)
US             Texas         53470 (Austin)
US             Texas         53480 (San Antonio)
etc.......

Regardless, the above should help you get started with filling your combos...although you could create a universal routine that would fill BOTH combos (Country and State) it may be easier to write 2 Subs, one for filling each combo (since your state combo fill routine will need to verify the country before adding a particular State)...





____________________________
Eggheads unite! You have nothing to lose but your yolks.

Thanx for the idea Bean...

Im actually giving a simpler example which is to use countries and states but my database is actually about something else so the zipcode is just to represent the information related to each data....

Ok, i havent tried ur code yet but to remind u that the user has to type in the name of the country in a  textbox and the list of states will be displayed in a combobox...so it involves linking the textbox with the combobox....