Create dynamic named ranges to call from userform

[tynawg]

Hi,

If anyone can give me a kickstart with this it will be appreciated.

In the linked excel file there are 2 tabs. I want to work with the first tab, GM SOR's.

The first row has identifiers or column headers.

The first column, Sub Trade has a number of trades listed multiple times. Each trade has codes associated with that trade (column B) Each code is only listed once with unique data to the right for that code.

I have code that works to make a userform with comboboxs that pull the subtrade which then populates the code associated with the subtrade only. However once a subtrade is selected I want to be able to see the unique data for the selected code displayed showing the data in column C, D and E?

Problem is I want to create a dynamic named range so data can be added or removed and it will still work.

combobox 1 to select a sub trade.

Combobox 2 to select only codes associated with subtrade.

Once a code is selected I want its unique information displayed for selected code.

Help please?

Copy of Copy of dependentcomboboxestrialcode.xlsm

 

[Akuini]

First of all, your code says ComboBox1 & ComboBox2 but the name of the combobobx are ComboBox3 & ComboBox4, so you need to amend that.

For combobox1, you get the list from Trades in sheet1 col A, right?

Here's the code (replace all your code with this code):

[FONT=Lucida Console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] UserForm_Initialize()
    
    [COLOR=Royalblue]Me[/COLOR].ComboBox1.RowSource = [COLOR=Darkcyan]""[/COLOR]
    [COLOR=Royalblue]Me[/COLOR].ComboBox2.RowSource = [COLOR=Darkcyan]""[/COLOR]
    
    [COLOR=Royalblue]With[/COLOR] Sheets([COLOR=Darkcyan]"Sheet1"[/COLOR])
    [COLOR=Royalblue]Me[/COLOR].ComboBox1.List = .Range([COLOR=Darkcyan]"A2"[/COLOR], .Cells(.Rows.Count, [COLOR=Darkcyan]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp)).Value
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox2_Enter()
[COLOR=Royalblue]Dim[/COLOR] vList, i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]

[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=Darkcyan]"GM SOR's"[/COLOR])
vList = .Range([COLOR=Darkcyan]"A1:B"[/COLOR] & .Cells(.Rows.Count, [COLOR=Darkcyan]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

[COLOR=Royalblue]Me[/COLOR].ComboBox2.Clear
    
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=Royalblue]LBound[/COLOR](vList) [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](vList)
    [COLOR=Royalblue]If[/COLOR] UCase(vList(i, [COLOR=Brown]1[/COLOR])) = UCase([COLOR=Royalblue]Me[/COLOR].ComboBox1.Value) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Me[/COLOR].ComboBox2.AddItem vList(i, [COLOR=Brown]2[/COLOR])
    [COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[/FONT]

 

[tynawg]

Hi Akuini,

Thank you for taking the time to reply.
Your code works, but no different than the one I posted.
Yes the code i have pulls the trade from sheet 1. However the trades are the same in GM SOR's, just listed beside each code it is applied to.
I am wanting to use one sheet as the data sheet and thought that would be GM SOR's as I want to have the associated cell in columns C, D and E displayed once selection is made in combobox 2. This is where I am stuck.
Example; selected in combobox1. Combobox2 now populates with codes associated like below, Carpenter_Joiner box 1, then select CAAF01 box 2 - now I want to have the cells after CAAF01 displayed.
Combobox1 Combobox2 Now display data below here as example, but in the file the data follows.
[TABLE="width: 1571"]
<tbody>[TR]
[TD="class: xl67, width: 201"]Carpenter_Joiner[/TD]
[TD="class: xl68, width: 120"]CAAF01[/TD]
[TD="class: xl68, width: 178"]Each[/TD]
[TD="class: xl68, width: 281"]Air Conditioner Frames and Panel[/TD]
[TD="class: xl68, width: 791"]Build air conditioner frame (timber framed dwelling). Includes panel[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

Example; selected in combobox1. Combobox2 now populates with codes associated like below, Carpenter_Joiner box 1, then select CAAF01 box 2 - now I want to have the cells after CAAF01 displayed.

Display where?

 

[tynawg]

Oh, hahaha.
On the userform after the code is selected in combobox 2. It could be that all 4 cells starting with the code cell such as CAAF01 are populated into combobox 2. I think I saw a setting that can be made to stipulate the number of cells in the box2 setting, not sure.
Alternately just display the 3 cells data below or adjacent to the selection in box 2?
This is essentially a data validation form so next I want to do further once selection has been made.
Cheers

 

[Akuini]

I don't think it's a good idea to display it in a combo box, how about in a multiline textbox below the combobox?

 

[tynawg]

Yes I was just thinking it is not for selection just read only. I will likely want to copy that data when I get to the save button later if that makes a difference?

 

[Akuini]

Ok, I use a textbox and set it up so you can't select it & the data is displayed separated by " - ", you can change it in this part (in "Private Sub ComboBox2_Change()"):
tx = tx & " - " & c

so when you want to copy that data, you should consider the " - ".

Here's the code:

[FONT=Lucida Console]
[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] UserForm_Initialize()
    
    [COLOR=Royalblue]Me[/COLOR].ComboBox1.RowSource = [COLOR=Darkcyan]""[/COLOR]
    [COLOR=Royalblue]Me[/COLOR].ComboBox2.RowSource = [COLOR=Darkcyan]""[/COLOR]
    [COLOR=Royalblue]Me[/COLOR].TextBox1.MultiLine = True
    [COLOR=Royalblue]Me[/COLOR].TextBox1.BackColor = [COLOR=Darkcyan]"&H80000004"[/COLOR]

    [COLOR=Royalblue]With[/COLOR] Sheets([COLOR=Darkcyan]"Sheet1"[/COLOR])
    [COLOR=Royalblue]Me[/COLOR].ComboBox1.List = .Range([COLOR=Darkcyan]"A2"[/COLOR], .Cells(.Rows.Count, [COLOR=Darkcyan]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp)).Value
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]


[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox2_Enter()
[COLOR=Royalblue]Dim[/COLOR] vList, i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]

[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=Darkcyan]"GM SOR's"[/COLOR])
vList = .Range([COLOR=Darkcyan]"A1:B"[/COLOR] & .Cells(.Rows.Count, [COLOR=Darkcyan]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

[COLOR=Royalblue]Me[/COLOR].ComboBox2.Clear
    
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=Royalblue]LBound[/COLOR](vList) [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](vList)
    [COLOR=Royalblue]If[/COLOR] UCase(vList(i, [COLOR=Brown]1[/COLOR])) = UCase([COLOR=Royalblue]Me[/COLOR].ComboBox1.Value) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Me[/COLOR].ComboBox2.AddItem vList(i, [COLOR=Brown]2[/COLOR])
    [COLOR=Royalblue]Next[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]


[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox1_Change()
[COLOR=Royalblue]Me[/COLOR].ComboBox2.Clear
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]


[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] ComboBox2_Change()
[COLOR=Royalblue]Dim[/COLOR] c [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] fm

TextBox1 = [COLOR=Darkcyan]""[/COLOR]
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=Darkcyan]"GM SOR's"[/COLOR])
fm = Application.Match([COLOR=Royalblue]Me[/COLOR].ComboBox2, .Range([COLOR=Darkcyan]"B1"[/COLOR], .Cells(.Rows.Count, [COLOR=Darkcyan]"B"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp)), [COLOR=Brown]0[/COLOR])
    [COLOR=Royalblue]If[/COLOR] IsNumeric(fm) [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] c In .Range([COLOR=Darkcyan]"A"[/COLOR] & fm & [COLOR=Darkcyan]":E"[/COLOR] & fm)
            tx = tx & [COLOR=Darkcyan]" - "[/COLOR] & c
        [COLOR=Royalblue]Next[/COLOR]
        TextBox1 = Right(tx, Len(tx) - [COLOR=Brown]3[/COLOR])
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
 
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] TextBox1_Enter()
[COLOR=Royalblue]Me[/COLOR].ComboBox2.SetFocus
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

[/FONT]

The file:
https://www.dropbox.com/s/1dvy0a5mpsxdcfo/Copy of Copy of dependentcomboboxestrialcode - 1.xlsm?dl=0

 

[tynawg]

Awesome, Your file worked a treat. I changed mine but still was something wrong and would not work.

Thank you sir, I have been banging away at this one for weeks. Solved.

Now to the next part, WoooooHooooo!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.