listbox question

[Raysadude]

i have 2 userforms one with textboxes and the other one with listbox and textboxes.

Everytime user input their data(ie:first name, last name, address etc) in the first form the data's going to be saved in Worksheet("customerSheet") and later on to be displayed in the second form.
using the listbox you can select the customer's name and the customer info will be displayed in the textboxes.

here's my code in second form

Private Sub UserForm_Initialize()

Worksheets("CustomerSheet").Activate
Range("A1").Select
'ActiveCell.CurrentRegion.Name = "Database"
'Selection.End(xlDown).Select
'ActiveCell.address(False, False)

viewCustomerBox.RowSource = "A2:A15"

End Sub

Private Sub viewCustomerBox_Change()

Dim fname As String
Dim address As String
Dim phone As String
Dim email As String
Dim cYear As String
Dim cMake As String
Dim cModel As String


 Range("AA10").value = viewCustomerBox.value
 

fname = Application.WorksheetFunction.VLookup(Range("AA10").value, Range("A1:H100"), 2, False)
address = Application.WorksheetFunction.VLookup(Range("AA10").value, Range("A1:H100"), 3, False)
phone = Application.WorksheetFunction.VLookup(Range("AA10").value, Range("A1:H100"), 4, False)
email = Application.WorksheetFunction.VLookup(Range("AA10").value, Range("A1:H100"), 5, False)
cYear = Application.WorksheetFunction.VLookup(Range("AA10").value, Range("A1:H100"), 6, False)
cMake = Application.WorksheetFunction.VLookup(Range("AA10").value, Range("A1:H100"), 7, False)
cModel = Application.WorksheetFunction.VLookup(Range("AA10").value, Range("A1:H100"), 8, False)

firstName.value = fname
lastName.value = viewCustomerBox.value
addressTxt.value = address
phoneTxt.value = phone
emailTxt.value = email
carYearTxt.value = cYear
carMakeTxt.value = cMake
carModelTxt.value = cModel


End Sub

here's the code to save the data from the first form

                Worksheets("customerSheet").Activate
                Range("A1").Select
                Selection.End(xlDown).Select
    
                ActiveCell.Offset(1, 0).Range("A1").value = lastName
                ActiveCell.Offset(1, 0).Range("B1").value = firstName
                ActiveCell.Offset(1, 0).Range("C1").value = address
                ActiveCell.Offset(1, 0).Range("D1").value = phoneNumber
                ActiveCell.Offset(1, 0).Range("E1").value = email
                ActiveCell.Offset(1, 0).Range("F1").value = carYear
                ActiveCell.Offset(1, 0).Range("G1").value = carMake
                ActiveCell.Offset(1, 0).Range("H1").value = carModel
            
            
                ActiveWorkbook.Names("Database").Delete
                ActiveCell.CurrentRegion.Name = "Database"

my question is, how to populate the listbox other than using the rowsource?

the reason i don't want to use rowsource is because rowsource require you to declare the cells you want to display, while my data is going to be dynamic.

ex: there are 15 users ("a2:a15") and then a new user user input their information, thus change the data count into ("a2:a16")

i don't want to check the customerSheet and change the vba coding everytime a new customer input their data.

is there anyway that i can get listbox to populate itself using different method other than rowsource, so whenever a new user enter data, it automatically add one more value to the list?

thanks and regards

Ray

 

[Raysadude]

Maybe you need:

Range("AA10").Value = Val(viewCustomerBox.Value)

thanks a million andrew it's working great.

it's really annoying working with userform and worksheet though, since if the worksheets change then you can't get the value you want.

Ray

 

[Bill Rockenbach]

This will get you started on dynamic ranges

Dynamic Named Range (Array)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Assume your employee list is in column A starting in row 2

Assume number emplyees will not exceed 10,000
<o></o>
Select the sheet your list of employees is on
<o></o>
Open the window to define a name (xl200-2003 is Insert | Name | Define)
<o></o>
Name the array EmployeeList (No spaces in the name)
<o></o>
In the refers to: box enter
Offset($A$2,0,0,COUNTA($A$2:$A$10000,1)
<o></o>
Click OK
<o></o>
Now put the name EmployeeList in the rowsource of your list box (do not use quote marks)
<o></o>
The named range will expand and contract as you add and delete employee names
<o></o>
If you have employee numbers you can set them up the same way.

 

[Raysadude]

This will get you started on dynamic ranges

Dynamic Named Range (Array)
<o></o>
Assume your employee list is in column A starting in row 2

Assume number emplyees will not exceed 10,000
<o></o>
Select the sheet your list of employees is on
<o></o>
Open the window to define a name (xl200-2003 is Insert | Name | Define)
<o></o>
Name the array EmployeeList (No spaces in the name)
<o></o>
In the refers to: box enter
Offset($A$2,0,0,COUNTA($A$2:$A$10000,1)
<o></o>
Click OK
<o></o>
Now put the name EmployeeList in the rowsource of your list box (do not use quote marks)
<o></o>
The named range will expand and contract as you add and delete employee names
<o></o>
If you have employee numbers you can set them up the same way.

ahh that looks easy, no coding wow nice bill.

andrew's code using additem is working perfect but it's always good to get food for thought i really appreciate it and i'll definitely try it.