PDA

View Full Version : Display info from Spreadsheet in listbox



AlvinChaand
09-19-2008, 03:37 AM
Hi,
I'm a newbie at VB so any help rendered will be really appreciated :)
:banghead:
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?:dunno

Bob Phillips
09-19-2008, 03:57 AM
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

AlvinChaand
09-19-2008, 04:00 AM
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
09-19-2008, 04:41 AM
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
09-19-2008, 04:52 AM
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
09-19-2008, 05:00 AM
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
09-19-2008, 06:04 AM
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
09-19-2008, 06:18 AM
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:help

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...:banghead:

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
09-19-2008, 07:31 AM
xld,

Would data validation and dependent lists work for OP?

Bob Phillips
09-19-2008, 08:06 AM
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
09-19-2008, 08:55 AM
Hello, thank u so uch for ur patience n time... i managed to populate the listbox with the desired data by simply using this:cloud9:

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?):banghead:

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...:dunno
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?:help

AlvinChaand
09-19-2008, 10:04 AM
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
09-19-2008, 10:37 AM
Private Sub CommandButton1_Click()

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

AlvinChaand
09-19-2008, 11:03 AM
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
09-19-2008, 01:25 PM
I don't understand the question.

AlvinChaand
09-20-2008, 05:33 AM
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::dunno
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?:help

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?? :bug:


[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
09-20-2008, 05:48 AM
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
09-20-2008, 08:07 AM
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! :bow:

Bob Phillips
09-20-2008, 08:41 AM
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
09-20-2008, 08:42 AM
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.

AlvinChaand
09-20-2008, 02:40 PM
Hello!

The ADMIN can
1-search,
2-update and
3-view data

while
The user can only
1-search and
2-view data

oOPS i realise i didnt change the radio button in user menu from update data to Search data...sorry abt that

Redundancy...yeah i realised that but was not sure if the search userform cld be the same for both the user and the admin?
The admin menu and the user menu is a copy but i had to duplicate it because the admin has an update option that is not available to the user...

Oh thank u for pointin out the SG_Tan thing....i stared at it for so long but didnt realise... thank u once again!
Fixed it already! :)

AlvinChaand
09-20-2008, 02:50 PM
deleted

AlvinChaand
09-20-2008, 02:56 PM
deleted

Bob Phillips
09-20-2008, 03:27 PM
You've deleted the workbook now, it's hard to help without it.

AlvinChaand
09-20-2008, 11:55 PM
hi i have put up my workbook again...

AlvinChaand
09-21-2008, 03:02 AM
Hi do u think this is a better application since there isnt redundancy?
but there are alot of "cant display modally errors :(

do take a look... i think this design is better.... no repetitions...

Bob Phillips
09-22-2008, 02:05 AM
Here's some ideas

AlvinChaand
09-22-2008, 03:49 AM
Hello!
I looked thru the wkbk u uploaded...thank u :) u've been a geat mentor and i really appreciate all ur help!:friends:

now the updates occur on row B thru E in the selected wksheet etc...
is there a way i can get the update to occur in a new workbook named ChinaChan.xls and in the Chan Ai Mei worksheet?:bug:

Also this info which is updated must not overwrite the previous info that had been stored.so each update occurs on the next empty row...
is there a possibility of geting this done?

:(

AlvinChaand
09-24-2008, 03:10 AM
Hi,

do take a look at my wkbk thank you!
I managed to get the desired worksheet to be viewed from the search_data userform...
How do i ensure :help that the updates are done in the particular worksheet too instead in rows B thru E???? :help

Bob Phillips
09-24-2008, 04:16 AM
A bit of detail explaation is in order. What are the other two workbooks, and what is their purpose?

AlvinChaand
09-24-2008, 04:33 AM
Hi,
The other workbooks are to hold more details of the people...
so i will have one workbook for chinaChan one for Singaporechan and so on for the rest...

this workbook will contail more details on the people the user is searching for...

it will have their
1.picture
2.some other info which wil be populated later when i get part 2 of the assignment.....
3.also the update done on the userform shld not overwrite the existing info but add on to it...in the next rows:dunno

i hope i didnt confuse u further?do let me noe if i did...i will try to explain again to the best of my ability