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

 

[tynawg]

Hi Akuini,
Is there a simple way to set this code to run as it does but from tab GM SOR's so I can simplify by only having one sheet of data? All the data is on GM SOR's. sheet1 was only set up to make that code work.

Also I know this may be asking a lot so no issue if the answer is no, but by any chance could comments be added perhaps where it may not be obvious what the code is doing? I want to know how it does it and when so I can use it elsewhere potentially.

Regards,
Wayne

 

[tynawg]

Hi,
I also changed A below to C so it displays the extra 3 cells only in the read only section, Awesome.
For Each c In .Range("A" & fm & ":E" & fm)

 

[Akuini]

Ok, I can see that you understand how the code works.

 

[Akuini]

Is there a simple way to set this code to run as it does but from tab GM SOR's so I can simplify by only having one sheet of data?

I think you we can use Dictionary object to do that. Let me try it in few minutes.

but by any chance could comments be added perhaps where it may not be obvious what the code is doing?

in which part do you need an explanation?

 

[Akuini]

Is there a simple way to set this code to run as it does but from tab GM SOR's so I can simplify by only having one sheet of data?

I think the simplest way is to move col A (Trade) in sheet1 to a colomn in GM SOR's, say colomn K, then just change this part:

    With Sheets("Sheet1")
    Me.ComboBox1.List = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value
    End With

to :

    With Sheets("GM SOR's")
    Me.ComboBox1.List = .Range("K2", .Cells(.Rows.Count, "K").End(xlUp)).Value
    End With

then you can delete sheet1.

 

[tynawg]

Hi Akuini,

Rather than start a new question thread I thought I might ask how I should word the question here as you have helped you will understand better what I am seeking? I will endeavor to list the steps in what I am trying to build including some explanations.
1. We export an excel service order from client with a certain amount of codes for each job matching the ones on GM SOR's. Link below with the import_SOR_Data1 macro. When macro is run it takes relevant data and copies to Imported Data sheet. This is where the following questions are being asked.
2. I need a method of exporting it to a specific workbook where the code can be run on.
3. Lets say a service order is exported and has 15 codes issued for a job. Often a code etc is wrong so I want to find a way to validate each line of code against the GM SOR's sheet using the userform. Not solved.
4. So there needs to be a way of starting to go through those 15 and if wrong or measure needs changing that maybe I select a macro button in the sheet to change code which runs the userform GM SOR's with a save button etc once a code is selected. Then the change is both saved to a yet to be developed form as well as either under the code that was wrong in the 15 or beside etc
5. Only codes in the 15 that need changing make a change occur otherwise no change and saved as is.

So I think There are 2 ways to proceed from here.
1. Build the save, change or delete buttons or others on the userform GM SOR's
2. Work on the code in the macro import_SOR_Data1 to be able to use the userform GM SOR's etc.

Hoping you might assist.

Regards,
Wayne

Copy of VOID VALIDATION KCxllinestyle.xlsm

 

[Akuini]

Actually it would be better if you start a new thread because it will attract more helpers to try to find a solution to your problem and also this is a new problem anyway.