Combobox source , linked to other combobox value ??(DYNAMIC)

Anyone? BUMP

Add Ciombobox1 and Combobox2 to your worksheet "Sheet(1)"
Right click the sheet tab, VB window appears.
Paste the entire code below into window.
Select sheet (2), ReSelect sheet (1) to activate the Code.
Comboxes should now be filled.
Change Value in combobox1 for new Results in combox2.

Please Login or Register  to view this content.

Regards Mick

See the attached File :-
Ref:- code in "Userform1_Initialize" Event.

Try this.

ThisWorkbook module

Please Login or Register  to view this content.

Sheet1 module

Please Login or Register  to view this content.

Thanks for the help! jindon

Here ya go ( the file is in attachments), in the if you need some info on what is the purpose or whatever you like to know please Private msg me !

Some info: If you want to start it up , it will probably say " is already open" This is because i want it to be launched without workbook open and also close it when data is entered.

So what you need to do to make it work like i have , put the code in personal.xls project
Check properties and there should be a project named personal.xls that's always there.
If you dont have it , or dont know how to make it. Google is your best friend.

To make personal.xls record new macro and save it in Personal macro workbook.
I hope you can figure out my code!



Problem right now , if i select another number 640 . Comboboxx1.value = "" which cannot be found.


And thanks again for the help!
Yours , Steven

The file wont upload.. ill try at home.

just for an example

here you go , the file !

as i said before , you need to put the userform in personal.xls project.

The file you sent looks good , i need to know the row number + column number so i can put textbox values at that place.
I used to do this with matchfunction , but i see you did it with listbox.

If you have questions , please ask.

Steven

Can you tell me exactly what should happen with those comboboxes? and textboxes?

The first 2 are values in MAIN row A and D. Here the person selects a number and then the ord.number. this will give a specific row. Then the person has to enter a "dienst" which can be found in MAIN again. To get the coresponding Column number. With then we have a textbox "aantalstuks" where the person enters a Value. The value has to be placed on the Row he selected and the Column he selected.

Next we have an identical sheet. Ronnie controle where i want the values of textbox + value of combobox"Naam" ( which is name) so we can see who entered the data again on the coresponding row and column he entered. Next up i have a Date combobox. where the person enters a date.
This is used to fill sheet "percentages" the person selects a date. And the value will be added to the right date under the right "naam" column.

I hope u can do something with this info.

First,

Then the person has to enter a "dienst" which can be found in MAIN again. To get the coresponding Column number.

What will be the list for this combobox to identify the column reference?

In row 3 , KLM , OPQ, STU , WXY , AA AB AC

So the text: " Maandag Ochtend , Maandag Middag , Maandag Nacht , Dinsdag Ochtend , Dinsdag Middag , Dinsdag Nacht , Woensdag Ochtend , Woensdag Middag , Woensdag Nacht , Donderdag Ochtend , Donderdag Middag , Donderdag Nacht, Vrijdag Ochtend , Vrijdag Middag , Vrijdag Ochtend"

So the heading from Col.J to Col.AC in row 3?

Ah, I see...

KLM , OPQ, STU , WXY , AA AB AC

Well yes , but not all of them. Only 3k 3L 3M etc. without N - R -V-Z

2)

With then we have a textbox "aantalstuks" where the person enters a Value. The value has to be placed on the Row he selected and the Column he selected.

What is the name of the command button to update?

That's the OK button.
When you press OK all the data gets added.

Also i have a refresh button which closes and reopens the userform ( update/nieuw button) called cancelbutton in the code.

And the AFSLUIT button = Escape button / close button which unloads the userform.

This the code that you have requested.

Note:
You need to delete "RowSource" property from all the comboboxes except "Naam", otherwise you will receive run-time error.

Please Login or Register  to view this content.

This looks awesome , could you also explain what you have done. With ' text inbetween the code?

I know this might be some work. But in this way i also understand what my code does , and in this way i could know how i could maybe edit it or tweak it when necessary.

Thanks allot !

OK,

It will be sometime tomorrow, it is too late here.

1) The Apostrophe in VBA code module disable the line from execution. In other words, the code will skip those lines.
If you delete them, it will re-activate those lines to execute.

2) You need to learn about vba Array and Dictionary object, I don't want to explain the basics.

What the code does;

1) Store the data from A3:ACx to an array variable "a", where x is the last cell used in that sheet.
The reasons :
i) need to manipulate the elements without affecting the data in worksheet.
ii) speed up the process.

2) Loop through "a"
i) if col.A is empty, copy from the row above, so that col.A will be filled. -
If a(i,1) = "" Then a(i,1)=a(i-1,1)

ii) if Col.D is not empty -
If a(i,4) <> ""

iii) If dictionary doesn't have unique value of col.A -
If Not .Exists(a(i,1)) Then

iv) Set another dictionay in its Item property -
Set .Item(a(i,1)) = Createobject("Scripting.Dictionary")
So to say, parent & child dictionary relation.

v) Store the row reference in item property in dictionary object in combination of a(i,1) & a(i,4)
.Item(a(i,1))(a(i,4)) = i + 2
Since the array "a" is storing the data from A3, row reference should be added by 2 on loop index "i"for later reference.

3) Populate Combo for Col.Reference
i) loop through array "a" for the heading from Col.K by interval of 4 columns. -
For i = 11 To Ubound(a,2) - 3 Step 4

ii) Store each heading in 1st dimention and col.Refernce in 2nd dimention in an array variable "b" -
For ii = i To i + 2
n = n + 1
b(n, 1) = a(1, ii): b(n, 2) = ii
Next

iii) Set the list property -
Me.ComboBoxx2.List = b
If you change the ColumnCount property to 2, you can actually see what the list have.

This is the main part of what the code does.

Hope you understand.

Thanks , yes this is really helpfull thanks !

Question : I forgot something , the 2 labels you see on the right are filled with values from column 31 and the second label 32. The labels should display the text in that cell

So again the row number is needed, so for example the person select 710 , then 200047978. The coresponding row number = 282 so i want the 2 labels to be filled with ( label6 and label7) 282AE cell value. and label7 with 282AF cell value

How i used to do this

Please Login or Register  to view this content.

And the same for label7.caption only then with 32

If i understand it correctly now , i have to use comboboxx1.listindex?

Yours , Steven

1) add 2 lines

Please Login or Register  to view this content.

2) Add following code

Please Login or Register  to view this content.

Problem #2

subscript out of range.

If i debug it highlights this part:

Please Login or Register  to view this content.

Any suggestions what might cause this?

@ #28 That works perfectly , thanks !

Check sheet name and/or book name.

Subscript means the string within a bracket might be wrong.

Yup , name was incorrect! thanks

But it i think i haven't explained correctly what i wanted to be in sheet "Percentages".

now the code is this

Please Login or Register  to view this content.

But in sheet Percentages i also want to do the same as in MAIN . The combobox value with Naam: is the Column number.. And the Combobox value with Datum to be the Row_number.

This is new sorry i didn't tell you before , but i thought i could figure it out myself. ( but its hard for me without knowing the problem solution above ^)
In this Row / Col coreseponding cell , i would like to have the value of the calculation ( textbox value / label6.caption value)

Steven,

See "CommandButton1_Click()" in my post #22,

myRow, myCol are the row/col reference.

myRow = Row connected to "MAIN" machine and order number right? (comboboxx1 & comboboxx5)

And not Row connected to Date right????

I have tried

Please Login or Register  to view this content.

But nothing happens so i have no idea what this code does.

So then i tried this.

Please Login or Register  to view this content.

And like i suspected , this takes the Column and Row number from (combobox5 & comboboxx1) and comboboxx2 and not to date and naam. Which is comboboxx3 and comboboxx4.

I could use the method of function match i gues.... But i gues you have an easier way

Steven

Seems to be fixed?

Dear Jindon , i hope you are still active at this forum. Because , i still don't know how to edit some part of the code.

What i want to do , i want to change the combobox source. For the Columns.
In the past i had only 15 headings. Now i want 17. I already found out the parts that correspond with the start of the count. But i cant figure out how to edit to code to fit my headings.

Instead of only be able to pick from in combobox from JKL,NOP,RST,VWX,ZA-AA-AB. I want to be able to pick 2 more. Which are AC and AD
Right now the code looks like this:

Please Login or Register  to view this content.

I have no clue where to edit , i only know the "10" in the code is the starting cell. ( For i = 10 to unbound )

Pls help ! The example worksheet is in the attachments

This is too old to me, so I already forgot what it does.

Can you upload the file with the form and the data that was working?

Sure , give me a moment. ( the code obviously has expanded) The part im interested in is being able to also change the comboboxx2 source (headings)(next to the name "dienst" on userform). So when the userform is active , i can pick from the cell values in row 3 , Columns JKL,NOP,RST,VWX,ZA-AA-AB , AC and AD. In the past the source was JKL,NOP,RST,VWX,ZA-AA-AB.

Note , put the userform in personal.xls (personal macro workbook)otherwise it wont work. ( closes workbook , and reopens so macro related to workbook dissapear)
Next also put module 21 (Removecaption) into the personal macro workbook.

Also it OPENS the file, so somewhere it says

Please Login or Register  to view this content.

Change this location. Else it wont find it.

The only important sheet is the "MAIN" sheet

Thanks

Any help appreciated! , Jindon if you have question pls do ask .. because i really want this fixed soon!

1) Need to comment out all the lines that is

Please Login or Register  to view this content.

And what is the purpose of those lines?

Anyway it is working fine to me.

Dear Jindon

yes it is working, but i want more choices in comboboxx2. Which is the combobox next to the label : " Dienst"

Right now your able to pick 15 choices , and i want 17. Right now you can pick from (Maandag dag , Maandag Avond etc.) but i want to be able to pick Zaterdag and Zondag to. These two values are in cell 3AC and 3AD. How do i get them to the combobox-source?


Note: the purpose of closing , is to save the changes.

This is really annoying!!!

Always closing instantly.

I don't want to touch this annoying code, so can you upload the file without such functionality?

Otherwise, I don't want to opne this file anymore.