Display info from Spreadsheet in listbox

[AlvinChaand]

Hi,
I'm a newbie at VB so any help rendered will be really appreciated

How do I display info from a spreadsheet in a listbox on a userform, according to user search?

I have a userform which asks user to select Country from combobox1 and City from combobox2. When search is clicked, he is shown a list of towns in that city in the listbox.

How can i go about doing this?

[Bob Phillips]

[vba]

Public Sub LoadListbox(cty As String)
Dim cell As Range

With ActiveSheet

Me.ListBox1.Clear
For Each cell In .Range("A1:A20")

If cell.Value = cty Then

Me.ListBox1.AddItem cell.Offset(0, 1)
End If
Next cell
End With
End Sub
[/vba]

[AlvinChaand]

Hi, sorry but could u explain more in depth what is meant to be done?
this is my first project and i am totally new at VB... i really appreciate the time and patience u've taken to help out
Hope to hear from u soon
Thanku

[Bob Phillips]

That is a simple procedure to do waht you asked.

Pass the country as a parameter, and it will get the towns in coumn B associated with the rows where column A equals your nominated country.

[AlvinChaand]

Hi
ok i shall try to explain further.... the reason why the user is to choose the country and city is because i have a list of countries ranging from A to Z

Example.
Country = USA
eg for USA i have CITIES= new york, chicago, new jersey etc
for Country= india i have CITIES= pune, mumbai, new delhi etc
so i want the user to be able to narrow his search. if he wants to view india, he selects india as the country
he selects mumbai as the city
and the listbox will display towns in mumbai

this is supposed to work for any other country as well eg Malaysia or Singapore or any i have on my country list

[AlvinChaand]

When the search criteria of the user is entered, Eg if he chooses Country=India & City= New Delhi , the listbox is to display info of the towns in New Delhi....

I'm doing this part by part...so once i can get this to work, i have to work on other things like enabling the user to click on the town and do view/updates info like the population/birth rate for that particular town etc

[Bob Phillips]

I still don't get it. If e selects India, then Mumbai, that's it. There are no towns in Mumbai, Mumbai is the town/city.

[AlvinChaand]

Hi xld,
I'm sorry about that... i was just using country, cities and towns as a reference thinking it would be easier to put it across but i guess i have confused u...... my project is actually using country and surnames

Eg if user selects country "China", surname "Tan"
Then all the "Tan" listed in my DB who are from origin "China" will be displayed in the listbox...
Likewise if the User selects "Singapore" and surname "Tan" all the matches will be displayed in the listbox...

what needs to be done is the user has to select from 2 comboboxes and if the criteria matches, the listbox displays the results
I hope i didnt confuse u further i m really sorry abt the misunderstanding.

[RonMcK]

xld,

Would data validation and dependent lists work for OP?

[Bob Phillips]

The problem would be getting a uniue list of names for the second Ron, and I still have no idea what would go in the listbox once you have selected the country and the name.

[AlvinChaand]

Hello, thank u so uch for ur patience n time... i managed to populate the listbox with the desired data by simply using this

Private Sub CommandButton1_Click()
If SN_COMBO.Value = "TAN" And COORIGIN_COMBO.Value = "SINGAPORE" Then
ListBox1.RowSource = "=SINGAPORE_TAN!A1:A15"
End If

(but the problem is i have to go on and on with all the different Surnames n countries and do a If statement with each and evryone of them...i have hundreds of them..is there any simpler way?)

my next problem is...
when the data is listed in the listbox, with Eg
Tan,Susie
Tan, Ah Teck
Tan,Alan
Tan,Debbie

I want the user to be able to click on a one of the above and be able to update that person's data...
So when he clicks on Tan,Susie he will be presented with an update page
Where he has to fill in
AGE-
Date Of birth-
Country of Birth-
Address-
Telephone number-
Martial status-
etc
once he fills the following and clicks on submit, the TanSusie.xls sheet will be updated with the latest data. how do i do this?

[AlvinChaand]

This is what i have done til now
i have created a DB with countries, surnames etc.
On this search page IF user selects country "China", surname "Tan"
Then all the "Tan" listed in my DB who are from origin "China" will be displayed in the listbox...
Likewise if the User selects "Singapore" and surname "Tan" all the matches will be displayed in the listbox.i managed to populate the listbox with the desired data by simply using this

Private Sub CommandButton1_Click()
If SN_COMBO.Value = "TAN" And COORIGIN_COMBO.Value = "SINGAPORE" Then
ListBox1.RowSource = "=SINGAPORE_TAN!A1:A15"
End If

(but the problem is i have to go on and on with all the different Surnames n countries and do a If statement with each and evryone of them...i have hundreds of them..is there any simpler way?)
------------------------------------------------------------------------------------------------------------------------
my next problem is when the data is listed in the listbox, with Eg
Tan,Susie
Tan, Ah Teck
Tan,Alan
Tan,Debbie
I want the user to be able to click on a one of the above and be able to update that person's data...
So when he clicks on Tan,Susie he will be presented with an update page
Where he has to fill in
AGE-
Date Of birth-
Address-
etc

once he fills the following and clicks on update the TanSusie.xls sheet will be updated with the latest data. how do i do this?
Right now i only know how to make sheet1 be updated as its hard coded.I want the sheets to change accordingly...to TANSUSIE.xls or ChanTowKay.xls etc ..how do i do that?

[code]
Private Sub CMD_UPDATE_Click()
Unload Me

Dim iRow As Long
Dim WS As Worksheet
Set WS = Worksheets("SHEET1") 'CHANGE TO SHEET NAME THAT U WISH UR UPDATE TO APPEAR IN

'find first empty row in database
iRow = WS.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
' Check user input
If Me.AGE_COMBO.Value = "" Then
MsgBox "Please select the AGE", vbExclamation
Me.AGE_COMBO.SetFocus
Exit Sub
End If
'Check if user left it blank
If Me.DOB.Value = "" Then
MsgBox "Please enter a date of birth", vbExclamation
Me.DOB.SetFocus
Exit Sub
End If

'Check if user left it blank
If Me.Address.Value = "" Then
MsgBox "Please enter a Address",vbExclamation
Me.Address.SetFocus
Exit Sub
End If
'copy the entered data to the database
WS.Cells(iRow, 1).Value = Me.AGE_COMBO.Value
WS.Cells(iRow, 2).Value = Me.DOB.Value
WS.Cells(iRow, 3).Value = Me.ADDRESS.Value
'clear the data after first set of data is entered in db so that user can enter next set
Me.AGE_COMBO.Value = ""
Me.DOB.Value = ""
Me.ADDRESS.Value = ""
'Me.AGE_COMBO.SetFocus

End Sub

[Bob Phillips]

[vba]

Private Sub CommandButton1_Click()

ListBox1.RowSource = "=INDIRECT(""'" & COORIGIN_COMBO.Value & "_" & SN_COMBO & "'!A1:A15"")"
End Sub
[/vba]

[AlvinChaand]

Hi XLD
Thank u so much! that one line made my life so much easier and my codes so much neater! thank u!

Do u noe how i can change the sheets to be updated accordingly?

[Bob Phillips]

I don't understand the question.

[AlvinChaand]

Hi XLD,

ok let me try to explain it again...i'm sorry for the earlier confusion

Now that the data i want is displayed in the listbox, i want the user to be able to select one of the displayed data from the listbox and update the info.

So if the user selected Country= China & Surname = Chan
the listbox is populated with the all the Chan surnamed people who are from China.eg

Chan, AiLi
Chan, Tee Ko
Chan, Kim Li
Chan, Meimei

I want the user to be able to click on one of the names listed here and update info of that person.

Now i need to work on this:
When the user selects one of the above name eg. Chan,Kim Li and clicks the NEXT button, he is presented with a userform asking him to enter the following data:

AGE-
Date Of Birth-
Address-
Martial Status-
No.Of Children-
Income- etc

When he enters the data listed above and clicks the UPDATE button, i want the ChanKimLi.xls to be updated, or for that matter any other name that is selected to have that name.xls be updated. Can this be done?

Right now all the updates are done in Sheet1 because its been hardcoded in my coding... how do i ensure that the sheet changes according to the names the user picks and updates that worksheet??


[code]
Private Sub CMD_UPDATE_Click()
Unload Me

Dim iRow As Long
Dim WS As Worksheet
Set WS = Worksheets("SHEET1")

'find first empty row in database
iRow = WS.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

' Check user input
If Me.AGE_COMBO.Value = "" Then
MsgBox "Please select the AGE", vbExclamation
Me.AGE_COMBO.SetFocus
Exit Sub
End If

'Check if user left it blank
If Me.DOB.Value = "" Then
MsgBox "Please enter a date of birth", vbExclamation
Me.DOB.SetFocus
Exit Sub
End If

'Check if user left it blank
If Me.Address.Value = "" Then
MsgBox "Please enter a Address",vbExclamation
Me.Address.SetFocus
Exit Sub
End If

'copy the entered data to the database
WS.Cells(iRow, 1).Value = Me.AGE_COMBO.Value
WS.Cells(iRow, 2).Value = Me.DOB.Value
WS.Cells(iRow, 3).Value = Me.ADDRESS.Value

'clear the data after first set of data is entered in db so that user can enter next set
Me.AGE_COMBO.Value = ""
Me.DOB.Value = ""
Me.ADDRESS.Value = ""
'Me.AGE_COMBO.SetFocus

End Sub

Pls let me know if it made any sense to u
Thank you once again for all your help
I really appreciate it
Have a nice day!

[Bob Phillips]

Why don't you post your workbook, so that we can see what we are really dealing with, otherwise we are being drip-fed.

[AlvinChaand]

hi i have uploaded my workbook for ur kind perusal... pls take a look and tell me how i can further improve the program...i hope to hear from u soon
thank u once again for your time and patience!

[Bob Phillips]

You seem to have a lot of redundancy in this code, the same screens duplicated etc.

What can ADMIN do that a USER can't?

[Bob Phillips]

Oh BTW, the reason Singapore doesn't work is because the worksheets are name Sg_Tan and so on, they must be the same as the country in the combo, i.e. Singapore_Tan, or else you need a lookup table.