Excel VBA: If Condition + Listing Names

[Manucas]

Hello. I need some help here.

Considerer the following image:
https://www.dropbox.com/s/aegrv5j83yjkurk/Test.png?dl=0

The idea is to click on the list of participants of Formation 1 (F.1) button and, from the column of formation 1, list the names of the trainees in the right table according to the group in which they are inserted ("G.1", "G.2", "G.3"), and so on. The name of the formation in the right table should also change depending on the list of participants button you click.


Imagining that I make changes in the left table and change a trainee from group 1 to group 2, i should have 1 of the following 2 options:

a) A warning message may appear saying that we should update the table because there were changes, and we clicking the button again to appear the new listing or;
b) The table is automatically updated according to the exchanges made (preferential).


Resuming, the code need to be able to define that, "If" a N fella is scheduled to "G.1", then the name is copied to the column of group 1, and so on.

I would appreciate your help.

Thank you in advance,
best regards

 

[mumps]

It's difficult to work with an image. Can you upload a copy of your file?

 

[Manucas]

It's difficult to work with an image. Can you upload a copy of your file?

Yes mumps, no problem.

The file contains the tables, and right table is with the final aspect that should have when you click in the button "List Participants F.1".

Thank you for the help.

https://www.dropbox.com/s/6op9crdh3m1xo38/Listing Test.xlsm?dl=0

 

[mumps]

Click here to download your file.

I have had to make some changes that you will be able to see to make the macro work. First of all, I have eliminated all the merged cells because merged cells almost always create problems for Excel macros. You should avoid using them at all cost. You can most often achieve the same effect by using "Centre Across Selection". You may want to do a little research on how this is done. You will have to add the additional buttons for each "FORMATION". I have had to rename the buttons so please use the same format for the additional ones. You can simply copy any of the existing macros and paste it into the code module for the added buttons. You will only have to make one change for each button. Just change the button number in the button name (highlighted in red) in this line of code:

buttonName = Split(Me.[COLOR="#FF0000"]CommandButton27[/COLOR].Caption, " ")(2) & " " & Split(Me.[COLOR="#FF0000"]CommandButton27[/COLOR].Caption, " ")(3)

to match the name of the new button. If you have any questions, please let me know.

 

[Manucas]

Click here to download your file.

I have had to make some changes that you will be able to see to make the macro work. First of all, I have eliminated all the merged cells because merged cells almost always create problems for Excel macros. You should avoid using them at all cost. You can most often achieve the same effect by using "Centre Across Selection". You may want to do a little research on how this is done. You will have to add the additional buttons for each "FORMATION". I have had to rename the buttons so please use the same format for the additional ones. You can simply copy any of the existing macros and paste it into the code module for the added buttons. You will only have to make one change for each button. Just change the button number in the button name (highlighted in red) in this line of code:

buttonName = Split(Me.[COLOR=#FF0000]CommandButton27[/COLOR].Caption, " ")(2) & " " & Split(Me.[COLOR=#FF0000]CommandButton27[/COLOR].Caption, " ")(3)

to match the name of the new button. If you have any questions, please let me know.

mumps, you're the boss. You saved what were supposed to be my future lost time.

That was already a huge help. I think i can do all the rest from here.

You did a great job, i appreciate a lot.

Thanks Sir

 

[mumps]

You are very welcome.

 

[Manucas]

If you have any questions, please let me know.

In fact, this is harder than i thought would be and the code is so much complex than i imagined

I sent you a soft format of the sheet, because i thought it was the easier way to understand the problem.

Nevertheless, you understood perfectly what it was the purpose, and the "program" runs beautifully.

However, i just cant adapt the code due to it complexity. I'm very hopeless in VBA, despite making an effort to learn.

But, there are 2/3 points that jump out at me:
1. When you have cleared cells at column G, you dont list the names, or, if you have none group defined for all trainees, when you click the button it's just dont supposed to list that ones;
2. Besides that, it's listing in duplicate the names of trainees near the original list, when the group is not defined to all trainees;
3. The names of formations are variable, so when you click the button i think is simple as copy the name of it as the names of the trainees;

I'm sending you the original file, so you can try it (if you can and want it of corse).

I think i applied the center across selection correctly, but check it before you start.

You can make the same if it's easier to you, and apply the code for the first 2 columns, and i think i can do the rest (if it will be only necessary to change names and adapt the cells)

I appreciate your attention, and thank you in advance

PS:. The Excel sheet is in my language, portuguese, but i think there's no problem. If so, tell me that i adapt it.

Link: https://www.dropbox.com/s/uav9xagypnwveth/Teste de Código Listagem.xlsm?dl=0

 

[mumps]

Could you please explain the following statements in more detail referring to specific cells, rows and columns:

1. When you have cleared cells at column G, you dont list the names, or, if you have none group defined for all trainees, when you click the button it's just dont supposed to list that ones;
2. Besides that, it's listing in duplicate the names of trainees near the original list, when the group is not defined to all trainees;
3. The names of formations are variable, so when you click the button i think is simple as copy the name of it as the names of the trainees;

 

[Manucas]

Could you please explain the following statements in more detail referring to specific cells, rows and columns:

Yes, sure.

What i was trying to say is:
1. When the cells have nothing in the G column, and therefore without G.1 / G.2 / G.3, then when you click the button to list the trainees, no list appears. However, in the file you sent, the names that have an associated group are listed and the ones that do not have are not listed, but a list of names appears next to the table on the left;

2. If for example there are no groups selected for any of the people and a list is requested by clicking on the button of the formation that without defined groups, there is an error saying to assign the groups to the trainees (which I can later change to my language);

3. What I was trying to transmit also, and taking into account the new file I sent you, the name of the formations is not just "formation 1", "formation 2", etc, but the name that is in the cell "E5", "H5", etc. This must be copied or matched to the "AO6" cell when the list button of its formation is clicked.

4.Another point that would be interesting would be with the addition of one more row at the end of the table with the same format and characteristics, and therefore all of the code would adapt to the number of rows that would be added, although I think that was what you did in the code you sent earlier.

If you can help me in this points, i would be "stratospherically" satisfied and grateful :D

Thank you in advance

 

[mumps]

Click [URL="https://app.box.com/s/636tm5zhqfa1gchl7c70z1ee7de5xrg3"]here [/URL]to download your file. Please note that I had to make a few changes. Use this version of the file. I have left a few empty cells in column F to give you an idea of how it works.