new condition to populate data on userform for name is existed sheet , not in another

[ABDELFATTA]

Hello
I would add another condition to adjusting code for Private Sub GetData()
what I search for it if the name is new in balance first of duration sheet and is not existed in sheet1 should show in listbox when select all from combobox or when clear in combobox .as to select the name will show as code does it .
thanks.

[MSDols]

Hi ABDELFATTA,
I have a habit of saving lists in a separate sheet.
I created a "Setup" sheet, where I inserted a list of names....
1. either you will update the list manually, or
2. you will do "some" automation (see attachment). The condition is the formula in the sheet "sheet1", in column "N". In all filled lines. Before opening the userform, the program checks the names in the sheet "sheet1" - if it finds new names, it inserts them into the "Setup" sheet. And at the same time it updates the variable "Name2". The variable "Name2" is reflected in the combobox.

It can certainly be done differently, but this is the first thing that came to mind.
m.s.

[ABDELFATTA]

I will check it , thanks.

[ABDELFATTA]

the program checks the names in the sheet "sheet1" - if it finds new names, it inserts them into the "Setup" sheet

misunderstood
please read again this

if the name is new in balance first of duration sheet and is not existed in sheet1 should show in listbox when select all from combobox or when clear in combobox

[ABDELFATTA]

when there is new name in balance first of duration sheet and contains minus value then should show plus value in credit column in listbox and if new name in balance first of duration sheet and contains plus value then should show plus value in debit column as marked in picture.

[JohnTopley]

And what, if any, is the impact on the Listbox2 calculations?

[ABDELFATTA]

And what, if any, is the impact on the Listbox2 calculations?

I expect showing in listbox2 based on showed data in listbox1 as Private Sub LB2() does it.

[MSDols]

Hi ABDELFATTA,
test the attached version.
m.s.

[ABDELFATTA]

seem to wok greatly !
but the question is : how convert minus to plus amount when showing in listbox?
I don't see that in code for rersult1 module sheet!

[MSDols]

Hi ABDELFATTA,
I don't understand.

how convert minus to plus amount when showing in listbox?

in listbox1 or listbox2? In which column? And why?

I don't see that in code for rersult1 module sheet!

should the report from listbox2 be reflected in the sheet "result1"?

Note: if there are currently two items from "balance first of duration" in "listbox1", in my opinion the others should be there too.
m.s.

[ABDELFATTA]

I don't understand.

I mean when there is balance minus for name in balance first of duration sheet will change to plus when show in listbox1 and yes this is what I want , but I try to understand your code and I don't see any line do that!
by the way AMER show in combobox1 ,but why KARREM doesn't show in combobox?!

[JohnTopley]

KARREM does not appeat because he is not in "Setup" list (Named range "Name2")

Change Refers to: of "Name2" to ....

Please Login or Register  to view this content.

[MSDols]

Hi ABDELFATTA,
1. code in which "balance" from the "balance first of duration" sheet is assigned to either "debut" or "credit".

Please Login or Register  to view this content.

2. missing name "KARREM": my mistake. Added.
3. total balance is reflected in the "result1" sheet after filling "listbox2".
4. I modified the overall code a little so that the balances fit.
5 . in the "Setup" sheet, the total balance is added. Just for checking. It can be deleted.
m.s.

[ABDELFATTA]

@MSDols

it will repeat balance twice in listbox1 when select new name is existed balance first of duration!

3. total balance is reflected in the "result1" sheet after filling "listbox3".

I don't see listbox3 !

[MSDols]

Hi ABDELFATTA,

I don't see listbox3

- typo. It should have been listbox2.

it will repeat balance twice in listbox1 when select new name is existed balance first of duration!

- I don't know what you mean by that. Try to describe it differently. It would be ideal to give an example.
m.s.

[MSDols]

Hi Hi ABDELFATTA,
I fixed one mistake. Maybe you meant this mistake.
Test it.
m.s.

[ABDELFATTA]

mismatch error when select AMER name from combobox1!

Please Login or Register  to view this content.

[MSDols]

Hi ABDELFATTA,
I missed this. Fixed.
m.s.

[ABDELFATTA]

when brings balance for name from balance first of duration sheet should be in second row in listbox1 when select name , not as you did it in lastrow.

[MSDols]

Hi ABDELFATTA,
first from "balance first of duration", then from "sheet1". It's there.
m.s.

[ABDELFATTA]

thanks , but where amounts total for all of names in row2 in listbox1 when select all or clear in combobox1 ?!
should sum all of balances for all of names in balance first of duration and show in listbox1 in row2.

[MSDols]

you need to decide what you want to have in "Listbox1":
- either the balance summary,
- or the individual balances,
(you can't have both - see post #5)
Note 1: you can see the balance summary in "Textbox3".
Note 2: if you want to see the balances in the report as well, we can add another column.
m.s.

[ABDELFATTA]

- either the balance summary,
- or the individual balances,
(you can't have both - see post #5)

they're both could really be because in post# 5 is existed and the original code does it !

Note 1: you can see the balance summary in "Textbox3".

regardless show in textbox3 when see to listbox1 shows me movements .

Note 2: if you want to see the balances in the report as well

not really understand it.

[MSDols]

Hi ABDELFATTA,

the second line of "Listbox1" is the sum of transfers - this number is not counted in the balance in "Listbox1".

m.s.

[ABDELFATTA]

thanks for your effort !
Q. why doesn't sum correctly amounts for columnS DEBIT,CREDIT when show in TB4,TB5?
just test mohammed,amer names as examples will notice that in TB4,TB5.

[MSDols]

you need to look at it including TB3.

In one line you have TB3, TB4, TB5 and TB6 - they are evenly distributed and therefore equivalent and represent a "standard set".

______Balance before__/__ Debit__/__Credit__/__Balance after
__________A________________B___________C________= B - C + A

m.s.

[ABDELFATTA]

my bad about Balance before , sorry!
but I think there is still error in calculation , look at Amer name when select from CB1
in lastrow in listbox1 doesn't show in TB5 and the right calculation should be -20000 in TB6,LISTBOX1 for lastrow in last column, but show -10000 that's wrong!

[MSDols]

the calculation is correct.
You should rather think about the structure of "userform1" and "listboxes1-2". From the point of view of an uninterested observer, it is chaos - without a manual, one gets lost.

m.s.

[ABDELFATTA]

after I see many times your way then your showing for data in listbox1 will be so confused for the users !
you made showing balance for name twice . first show balance in first row , second row showing data for same balance from same first of duration sheet , the user think second row is new process is existed in sheet1 , not the same balance from balance first of duration sheet ,so no need to show balance twice in listbox1 if the name doesn't contain process in sheet1.

[MSDols]

latest version.
If you don't like it, I suggest you close this thread (the original task has been completed).
And open a new thread where you define your other requirements.
I'm sorry, but I can't do more for you at the moment.
m.s.

[ABDELFATTA]

well done!

If you don't like it

of course I like it.

And open a new thread where you define your other requirements.

no need that because you achieved that almost 99% of requirements .
I have question about balances for all of names in balance first of duration sheet and show in listbox1 in row2
obviously doesn't show in listbox1 . does it make problem if you show ?
I mean you have to change codes from scratch?