listbox question

Raysadude

New Member
Joined
Sep 18, 2009
Messages
32
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
Code:
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

Code:
                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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Raysadude

New Member
Joined
Sep 18, 2009
Messages
32
You can use the AddItem method (provided that the RowSource property isn't set). Or you can assign an array to the List property.

i didn't set the rowsource property.

i'm new with VBA so pardon my ignorance, i tried to use help in VBA and look for additem, and i come up with another question :(

is that mean i don't have to use

listbox.rowsource = "a2:a15"

to populate the listbox?

and the additem will be automatically loop through column A, find the end of the data and populate the listbox?

Ray

EDIT: i'll check that example thank you andrew
 

kab7030

New Member
Joined
Dec 2, 2009
Messages
14
On the list box change event, you can use code to check if the total number of rows have increased and then just add the latest addition to the listbox.

You will need to count number of rows with data and maybe store. Then check that to see any new additions and then add them to listbox.
 

Raysadude

New Member
Joined
Sep 18, 2009
Messages
32

ADVERTISEMENT

You can use the AddItem method (provided that the RowSource property isn't set). Or you can assign an array to the List property.

Edit:

I found an example:

http://www.dailydoseofexcel.com/archives/2004/05/07/populating-one-column-listboxcombobox/

thanks andrew, the additem code is working flawlessly.

but the problem is, my vlookup function is not working no more after i populate the listbox using additem

error is: runtime error 1004
unable to get the vlookup property of the worksheetfunction class
 

Raysadude

New Member
Joined
Sep 18, 2009
Messages
32
On the list box change event, you can use code to check if the total number of rows have increased and then just add the latest addition to the listbox.

You will need to count number of rows with data and maybe store. Then check that to see any new additions and then add them to listbox.

thank you kab, but im not really sure how to do that, i'm really new with vba and excel

edit: i know how to use the listbox_change event but not really sure how to count the number of rows in column A and add the value to the list
 

Bill Rockenbach

Well-known Member
Joined
May 4, 2009
Messages
1,412

ADVERTISEMENT

Use a named dynamic array for your emplyee names and put the the name of your array into the rowsource property of your list box.

The array will automatically expaned as you add emplyees
 

Raysadude

New Member
Joined
Sep 18, 2009
Messages
32
nevermind i found out what cause the error, i forgot to activate the customerSheet worksheet.

thanks guys
 

Raysadude

New Member
Joined
Sep 18, 2009
Messages
32
Use a named dynamic array for your emplyee names and put the the name of your array into the rowsource property of your list box.

The array will automaticall exbaned as you add emplyees

will try this also, thanks bill. but first i need to learn about dynamic array first, never used it before :p
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,734
Members
414,170
Latest member
Mdm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top