UserForm help

A

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
I have started a user form and am pretty illiterate on how to get it to work for what I want.
Here is a sample of example data from a static table that I want to use for values for the user to select.
Book1
ABCD
1DIVISIONPRODUCTSEGMENTPRODUCTFAMILYPARTNUMBER
2DIV1SEG1FAM1100001
3DIV1SEG1FAM1100002
4DIV1SEG1FAM2100003
5DIV1SEG1FAM2100004
6DIV1SEG2FAM3300005
7DIV1SEG2FAM3300006
8DIV1SEG2FAM4100007
9DIV1SEG2FAM4100008
10DIV2SEG3FAM5600009
11DIV2SEG3FAM5600010
12DIV2SEG3FAM6100011
13DIV2SEG3FAM6100012
14DIV2SEG4FAM7600020
15DIV2SEG4FAM7600021
16DIV2SEG4FAM8100031
17DIV2SEG4FAM8100032
Sheet1


So in my userform I want the following:
Drop Down List for Division where user can select only one of DIV1 or DIV2.
After that is selected, I would want the next Drop Down List to populate with only the Product Segments for the Division they selected. And then after they select their Product Segment the next Drop Down List would autopopulate just the Product Family's that are within this Product Segment, and then of course the Part Numbers would autopopulate with only the one's within the Product Family that the user selects.
However, I want the Part Number to be a multiple selection to where the user can select multiple part numbers.

Then after their selection is complete, I want the values that they selected to export to say Sheet2 in respective cells, Division.Value in A1 Product Segment.Value in A2, etc.

I know I am asking a lot; but if one of the macro gurus could assist me I would really appreciate it.

Thanks
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Okay, so I've got this working pretty good. Based off what user selects in ComboBox1, ListBox1 populates with its corresponding Product_Segment. However, I tried using the similar code to do the same with what the user selects in ListBox1 to populate ListBox2. However, when I test it and select a Product_Segment in ListBox1 it clears ListBox1 and populates it with what should go in ListBox2. Any ideas?
Here's my code so far:

Code: 
Private Sub ComboBox1_Change()
ListBox1.RowSource = ""

ListBox1.Clear
lastrow = Sheets("SEGMENTS").Range("B65536").End(xlUp).Row

If ComboBox1.Value = "ALL" Then


ListBox1.Clear
ListBox1.RowSource = "SEGMENTS!B2:B" & lastrow

Else
Dim I As Long
Dim ws As Worksheet

    Set ws = Worksheets("SEGMENTS")
    ListBox1.Clear
    For I = 1 To ws.Cells(65536, 1).End(xlUp).Row
        If ws.Cells(I, 1) = ComboBox1.Value Then
            ListBox1.AddItem ws.Cells(I, 2)
        End If
    Next I
    End If
End Sub
Private Sub ListBox1_Change()
ListBox2.RowSource = ""

ListBox2.Clear
lastrow2 = Sheets("Families").Range("B65536").End(xlUp).Row

If ListBox1.Value = "ALL" Then


ListBox2.Clear
ListBox2.RowSource = "Families2!B2:B" & lastrow2

Else
Dim J As Long
Dim wks As Worksheet

    Set wks = Worksheets("Families")
    ListBox2.Clear
    For J = 1 To wks.Cells(65536, 1).End(xlUp).Row
        If wks.Cells(J, 1) = ListBox1.Value Then
            ListBox2.AddItem wks.Cells(J, 2)
        End If
    Next J
    End If
End Sub
 
Upvote 0
Nevermind, I figured it out.
Next question I have though. Let's say I want to make ListBox1 a user multi-select ListBox.
How would I go about having the next portion of the macro look up all values the user selected and look them up on the same table.

So, let's say user selected Division = DIV1...which makes ListBox1 show only Product Segments of SEG1 & SEG2. So let's say they want to select those two (note: in the real format there are several, so i can't really use an "ALL" feature here). How would I go about tweaking the macro to look up all the Product Families found in SEG1 & SEG2 and populate ListBox2 with those Families?
so for this example it should populate with FAM1, FAM2, FAM3, & FAM4.

Please help....I'm almost there, I can feel it :)
 
Upvote 0
There's some code on this thread that someone wrote to help me with multiselect listboxes:

http://www.mrexcel.com/forum/showthread.php?t=313852

Also, I've only looked at your code quickly, but if you've got more options than you've included at the moment, you might want to think about using named ranges, so you don't have to hard code each option.
 
Last edited:
Upvote 0
I'm not really good at named ranges on data that might increase. The data that I am using is a refreshable query linked to a database that could (and does) change regularly.

I know that would probably require a new thread posting. But I would need to figure out how to do a end(xlup) type formula for the "dynamic ranges".

Ultimately I just want to figure out this multiple selection and how to use those values to populate (or do a vlookup so to speak) the next listbox.
 
Upvote 0
My VBA isn't good enough to be able to help you efficiently - but there are loads of threads on dynamic ranges and dependent ranges, that I'm sure would be useful.

The userform that I built relied on dynamic named ranges. They were specified in Excel, rather than in the code, in the form

=OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1,1)
 
Upvote 0
I have almost got what I need on a single selection ListBox's however, for some reason my 3rd List box (ListBox6) which should populate from the value selected in the previous List Box (ListBox2) -- I know a little confusing because of the order of the List Boxes...but the other one's work. I'm wondering if someone could tell me if the number of rows that it has to search through in Excel is limited - or could it go through all 65536 rows if it had to?
I have 13,900 + rows that the value of ListBox2 has to go through and search a worksheet called ALL. Anyway, maybe it's just a typo that I'm missing. Could someone take a look and let me know.
Code: 
Private Sub CancelButton_Click()
Unload UserForm1
End Sub


Private Sub Clarifier_Click()

End Sub

Private Sub ComboBox1_Change()
ListBox5.RowSource = ""

lastrow = Sheets("SEGMENTS").Range("B65536").End(xlUp).Row

If ComboBox1.Value = "ALL" Then


ListBox5.Clear
ListBox5.RowSource = "SEGMENTS!B2:b" & lastrow
Else
Dim i As Long
Dim ws As Worksheet

    Set ws = Worksheets("SEGMENTS")
    ListBox5.Clear
    For i = 1 To ws.Cells(65536, 1).End(xlUp).Row
        If ws.Cells(i, 1) = ComboBox1.Value Then
            ListBox5.AddItem ws.Cells(i, 2)
        End If
    Next i
    End If
End Sub





Private Sub ListBox5_Change()
ListBox2.RowSource = ""

lastrow2 = Sheets("Families").Range("B65536").End(xlUp).Row

If ListBox5.Value = "ALL" Then


ListBox2.Clear
ListBox2.RowSource = "Families!B2:B" & lastrow2

Else


Dim J As Long
Dim wks As Worksheet
Set wks = Worksheets("Families")
       ListBox2.Clear
    For J = 1 To wks.Cells(65536, 1).End(xlUp).Row
        If wks.Cells(J, 1) = Me.ListBox5.Value Then
            ListBox2.AddItem wks.Cells(J, 2)
        End If
    Next J
    End If

End Sub





Private Sub ListBox2_Change()
ListBox6.RowSource = ""

lastrow3 = Sheets("ALL").Range("B65536").End(xlUp).Row

If ListBox2.Value = "ALL" Then


ListBox6.Clear
ListBox6.RowSource = "ALL!B2:B" & lastrow3

Else


Dim k As Long
Dim wsh As Worksheet
Set wsh = Worksheets("ALL")
       ListBox6.Clear
    For k = 1 To wsh.Cells(65536, 1).End(xlUp).Row
        If wsh.Cells(k, 1) = ListBox2.Value Then
            ListBox6.AddItem wsh.Cells(k, 2)
        End If
    Next k
    End If

End Sub
 
Upvote 0
I'm still at a loss for how to use a multiple value to lookup up if I were to change these list boxes to multiselect listboxes.
Anyone out there have some ideas of how I could pull that off?
 
Upvote 0
Did you look at the thread I linked to? There's code in there for looping through all the selected values in a multiselect listbox.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Jump to a TAB from a Drop-Down Menu
2
Replies
12
Views
506
LongToast
L
T
  • Question Question
Select from a term list, but input the corresponding key
Replies
1
Views
162
sijpie
sijpie
I
  • Solved
Add value into userform Combobox & also on worksheet Table
Replies
4
Views
147
ipbr21054
I
V
  • Question Question
Userform, combobox: Repicking the same selection?
Replies
2
Views
112
vrod2000
V
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
224
krunal123
K

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,947
Latest member
Gerry_F

Share this page

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
Back
Top