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
here's the code to save the data from the first form
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
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
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"
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