PDA

View Full Version : Fill second listbox based on selection in first listbox+ add en delete



eric055
11-14-2006, 05:51 PM
In an Exel file i have:
One Userform with two listboxes
Listbox a contains the main categories
Listbox b contains the sub categories

Lisbox a is filled using column B on the worksheet
Lisbox b is filled using column D on the worksheet

When the user selects an value from listbox a then listbox b shows the subcategory's belonging to the maincategorie selected in listbox a.

I came up with an solution using an indexnumber to each main categorie (in the worksheet on column A) and adding the same indexnumber to the subcategories (in the worksheet on column C).

Using an loop to fill up listbox B works great. So far no questions. Adding new categories or subcategories is no problems. Removing categories or subcategories gives a problem. I keep getting error messages...

I'm not able to delete cells in the source of the listboxes (columns on the worksheet) without getting an error... Can someone help me out with this? Of can someone give me another sollutions for filling the listboxes and adding/deleting categories/subcategories. I included an testversion of what i came up with so far... Excuse me for the messy code! Thanks in advance!

jolivanes
11-14-2006, 07:38 PM
Goedendag Eric.
I downloaded this from one of the sites that one of the generous excel specialists uploaded for someone. Unfortunately, I don't remember who it was but the credit goes to that person. It'll give you a good start.
Hope it works.
Groetjes uit Canada.
John

eric055
11-15-2006, 12:03 AM
Thanks John...
This partly does the job! Now i can fill an listbox based on another but i can not add or delete data... Adding data in your example gives problems since the raw data is sorted! Thanks anyway!

Kind regards
Eric

jolivanes
11-15-2006, 09:03 AM
Hi Eric.
Lucas had an addressbook on this site. I think it was here:
http://www.vbaexpress.com/forum/showthread.php?t=9001
Have a look at that.
Hope it helps.
John

eric055
11-15-2006, 02:08 PM
Hello John...

Thanks again. It just helps me partly but does nog concern the specific problems i have with the listboxes...

Eric

Charlize
11-15-2006, 02:14 PM
When adding a subcategorie you just have to refresh the listbox with the subcategories in it. When you switch the head categorie and go back to the one where you have added a subcategorie, the new subcategorie will show up in the listbox for the subcategories.

In other words, clear the list of the subcategories and define your source again and choose the items with the right index no.

Charlize

eric055
11-15-2006, 02:23 PM
Hi Charlize,

This works! Great i already came up with something like this to fill the second listbox based on the first.

Me.lbSubcatergorie.Clear

Dim Lrange As Range
Dim x As Variant
'Set the range to loop through
Set IndexNum = Sheet2.Columns("C").Find(lbCategorie, lookat:=xlWhole).Offset(0, -1)

lRowNum = Sheet2.Range("E3").End(xlDown).Row
Set Lrange = Range("Sheet2!E3:E" & lRowNum)

'Loops through the ranges
For Each x In Lrange
If x.Offset(0, -1).Value = IndexNum Then
Me.lbSubcatergorie.AddItem x.Value
End If
Next x

Adding date works great but when i delete an row in the source (raw) data then i'll get an error message...

Can someone help me out?!

Eric

Charlize
11-15-2006, 02:45 PM
Maybe use seperate sheets. One for the headcategories and one for the sub... We name them head and sub. Now, when you delete a head, do you check for subs or not ? Should all subs be deleted as well ? The problem you were facing (i think) was that head and sub can be on the same row. When you delete a head or sub the one next to him will also be deleted.

Charlize

eric055
11-15-2006, 03:19 PM
I'm gonne try this tommorow... Maybe you can test my file (first post). It has an delete option that gives an error.. Do you understand what happens? Thanks

Eric

Charlize
11-15-2006, 04:33 PM
I think that when you delete a subcategorie that has only one item in it, there's nothing to reload in the list, so you'll get an out of range error.

Charlize

Charlize
11-21-2006, 05:47 AM
A sample by using an external workbook where the data is kept. Headcategories are sheets and subcategories are cellvalues.

When a headcategorie is deleted that had subitems in it, the list will just clear. Possible way is to prevent the deleting until subitems are deleted first.

Had a lot to do the last days, so it took awhile before I could finish this.

Let me know what you think of this.

Charlize

eric055
11-21-2006, 03:44 PM
Hello Charlize,

Thanks alot for your response and time! Very nice... i might have an stupid question. I cannot find an button to show the first userform. When i use the command Input_frm.show i get an error message... Can you help me out with this one...

Charlize
11-21-2006, 04:01 PM
use alt+F8 to run a macro named get_input. The workbook 'categories program' is the one with the macro. Both workbooks must be open. Categories-ini is the one holding the data. The data workbook must be saved manually.

You get probably an error message because there is a check if the data workbook is open or not. If not, the form will not work.

Charlize