VB in Excel Worksheet 1

[nuglorious]

im new in vb with application with excel. I want to create a unbound listbox. Meaning, when i type in any words in the sheets, it contents will be link to the listbox and update automatically. May I know how to go abt it? I'm appreciate for your help...thks

 

[RobBroekhuis]

Use the workbook_sheetchange or worksheet_change event handler to find out when the user has made a new entry, and then use the listbox.additem method to add the entry to your listbox. If you need help with any of that, do check back.
Rob

 

[nuglorious]

I have no idea how to or where to start. If possible, if you can provide some example so that I can study step by step. Or do you know where to find more examples like Ms Access which I can download and view their samples?thks

 

[RobBroekhuis]

First, use the "control toolbox" (view it using the view - toolbars menu item) to create a listbox on your worksheet. Double-click on the listbox, to take you to the VB editor. Name the listbox to something useful (e.g. lbSheetEntries) by changing its name in the properties editor, in the bottom left of the VBE screen. You will also have in front of you the code page for the worksheet onto which you created the listbox. From the two dropdown boxes at the top of the code page, select "worksheet" and "change", to create an event handler named worksheet_change. Inside this event handler, put the statement:

lbSheetEntries.additem target

this will add an entry to your listbox every time a sheet change is made. Try this first. If it works, you'll have to write the code to avoid duplicate entries - which we'd be happy to help you with as well. Let us know how you fare.
Rob

 

[JESTAR]

Dim Data as Object
Dim Row as Integer
Set Data = Sheets("Sheet1&quot.Cells

Row = 2
While Not IsEmpty(Data(Row, 1))
ListBox1.AddItem Data(Row, 1)
Row = Row + 1
Wend

_________________________________________________

A B C
1 Names
2 Bob
3 Jerry
4 Connie
5 Mike
6 Arnold

_________________________________________________

ListBox1 will look like this:

Bob
Jerry
Connie
Mike
Arnold

It will not have "Names" in it. If you wish to read from the very top cell, change the "Row = 2" to "Row = 1". Change the ("Sheet1&quot to whatever the name of the worksheet you wish to read from is called.

While Not IsEmpty(Data(Row, 1))
ListBox1.AddItem Data(Row, 1)

The "(Row, 1)" in these lines is referring to the column, not the row. So if you put "(Row, 4)", it will read from column D.

The "Row = Row + 1" will iterate down the column until a blank cell is found.