automatically select item in drop down list

[babychai]

Hi,

In cell B1 in Sheet1's tab, i want it automatically select 1 menu down each time i click the Macro's run button.
For example, when each time click the Macro's run button then it will automatically select abc126, abc127, abc128, abc124 etc

Thank you.

[karmapala]

If I'm not mistaken to get what you mean, maybe something like this ?

Please Login or Register  to view this content.

At first the code get what is the value displayed in cell B1 Sheet1 (the dropdown box)
Then the code find that value in Sheet2, then get the next row value in Sheet2.
Finally the code put that next-row value it got from Sheet2 to cell B1 Sheet1.
If the value displayed in cell B1 Sheet1 is the last item, then the code put the first item in the dropdown box.
If cell B1 Sheet1 is blank, then the code put the first item in the dropdown box.

2020-05-21_02-18-03.gif

[babychai]

Hi karmapala,

your code is great. in real case my list actually has some is duplicated. i found that your code won't go to the next row if the next row value is same with previous row value. any idea how to fix this?
i attached the new sample with the updated list in Sheet2.
For example, when the value reach abc131 it will remain stay in abc131 whenever i click the Macro's button. Problem also same to abc132.

Hope you able to fix this duplicate issue.

[karmapala]

your code is great. in real case my list actually has some is duplicated.
i found that your code won't go to the next row if the next row value is same with previous row value.

There are three problems :
1. Your list has duplicate values
2. My code assume that there won't be duplicate value in the list

any idea how to fix this?

I'm sorry I can't help about this.

3. In my side,
after I select the first duplicate value in the validation cell manually, (first click the validation cell)
then I select the second duplicate value in the validation cell, (second click the validation cell)
then by the time my third click the validation cell, the highlighted item is the first duplicate value,
it's not the second duplicate value which is highlighted.

I would like to know how is in your side.
Please try to do it manually, by clicking the first duplicate value in the validation cell,
then at the second click, before you choose the item, you will see that the first duplicate value is highlighted.
Go ahead choose the item which is the second duplicate value.
Then at the third click, I would like to know which duplicate value is highlighted ? the first or the second ?

So, even I corrected the code but if the display of the validation cell is the duplicate value,
the code can never know the display duplicate value in the validation cell is the first or the second.
Then the result, the user will always have to click the macro button twice in order to go to the next item in the validation cell.

Also, I have to "cheat" in order the code can works .
The cheat is I make a helper column (column H in this case).
So please remember that there is no value at all in column H whatever row, babychai.

Please have a look in the attachment.

[babychai]

There are three problems :
3. In my side,
after I select the first duplicate value in the validation cell manually, (first click the validation cell)
then I select the second duplicate value in the validation cell, (second click the validation cell)
then by the time my third click the validation cell, the highlighted item is the first duplicate value,
it's not the second duplicate value which is highlighted.

I would like to know how is in your side.
Please try to do it manually, by clicking the first duplicate value in the validation cell,
then at the second click, before you choose the item, you will see that the first duplicate value is highlighted.
Go ahead choose the item which is the second duplicate value.
Then at the third click, I would like to know which duplicate value is highlighted ? the first or the second ?

Hi,

The third click will highlight back the first duplicate value which is same what you had describe. also when selected the second duplicate value it will go back to the first duplicate value when clicked the Macro button.

So, even I corrected the code but if the display of the validation cell is the duplicate value,
the code can never know the display duplicate value in the validation cell is the first or the second.
Then the result, the user will always have to click the macro button twice in order to go to the next item in the validation cell.

Also, I have to "cheat" in order the code can works .
The cheat is I make a helper column (column H in this case).
So please remember that there is no value at all in column H whatever row, babychai.

Please have a look in the attachment.

It's okay if need to click the Macro button twice because in my list it really have the duplicates value.
Your code is what i need. thanks for your understand my problem. but is it cheat in column H is the only way to solve the duplicates issue? because in column H is just copied from the column A in Sheet2 which is the same value. i wondering why can't just link back to the original source i.e. column A in Sheet2.

besides that, by using Offset(1, 0) formula the value should automatically go to the next row no matter there are duplicates value or not. i wondering why it didn't happen.

Actually your code already solved my problem but i ask these question just to understand more about the codes.

Thank you

[karmapala]

Hi,

The third click will highlight back the first duplicate value which is same what you had describe. also when selected the second duplicate value it will go back to the first duplicate value when clicked the Macro button.

So the same thing happen in your side .

in column H is just copied from the column A in Sheet2 which is the same value.

You are correct.

is it cheat in column H is the only way to solve the duplicates issue?
i wondering why can't just link back to the original source i.e. column A in Sheet2.

Because in a situation
that there is a duplicate values ... and what I can find so far of my knowledge is to have a "pointer" in the list.
It is for the code "to know" that to go to the next row is after the active cell (the pointer).

This "pointer" need to be in the same sheet, and need to be in the display area of the Excel app in the monitor.
If I put the copied list somewhere far (say column AAA), then you will see your Excel screen going crazy,
which is the Excel screen will go to column AAA. That's why I put the copied list in column H.

by using Offset(1, 0) formula the value should automatically go to the next row
no matter there are duplicates value or not. i wondering why it didn't happen.

Because if just use the offset(1,0)
without the addition "offset(1,0).select", then the code will going over and over again once it hits the duplicate value.
The offset(1,0) (without .select) is not telling to move to the next cell, but to just get the value under it.

Example:
Say, just after we open the workbook, the display of the validation box is abc131, the duplicate value.
Then we run the macro, where the code is to find abc131 on the list.
So the code find the FIRST abc131 in the list, then the code is to get the value one row below abc131.
After it get that value, the code is to put the value into the validation cell.
But since the value one row below abc131 is abc131 again, this cause the validation cell still abc131.
So, the validation cell show abc131 again.
The next time we run the macro, the validation cell still show abc131.
So, the macro will never move to abc132 because each time we run the macro,
what the macro find is the first abc131 since the validation value never move to abc132, always abc131.

Now, if we put the pointer (by activating the cell contains abc131 in the list),
then we can tell the macro to just move the pointer one row below.
So, the next time we run the macro, the pointer is on the second duplicate value in the list.
Then the macro use that pointer to get the value one row below the pointer, then put it in the validation cell.

To be honest, I'm also still a beginner.
So, my knowledge is limited.

But I think you can make a new thread asking about this specific problem.
I'm quite sure our seniors can help about this.

Actually your code already solved my problem
but i ask these question just to understand more about the codes.

Yes, it's good to learn how the code works.
Make a copy of the code, and name the macro to another.
Then open the Local Window then do step-run (by F8) and see what happen in Local Window and what happen on the sheet.
Then try to playing around with the code, for example try to remove one line that you are curious...
or try to change any number you see in the code...
and see what happen in Local Window and what happen on the sheet.
In this case, maybe you can try to put comment sign (') on the code line which has ".select" or ".activate",
then see what happen.

Anyway, I will rethink how to write the code, hoping that I can find a way to solve this.
And this is what I can do : (this code doesn't need column helper)

Please Login or Register  to view this content.

But then this code, once it hits the duplicate value (in this example abc131), (the validation box show abc131)
it will go directly to abc132 then put it in the validation box.
Then although abc132 also a duplicate value, after the validation box show abc132
then the next time we run the macro, it will show abc133.

Now this is the vice-versa of the code before.
In this code, it looks like that there is no duplicate value,
so no matter how many duplicate values are there in a consecutive row,
it looks like that there is no duplicate value at all,
because each time we run the macro the validation box will always show different value.

[babychai]

Because in a situation
that there is a duplicate values ... and what I can find so far of my knowledge is to have a "pointer" in the list.
It is for the code "to know" that to go to the next row is after the active cell (the pointer).

This "pointer" need to be in the same sheet, and need to be in the display area of the Excel app in the monitor.
If I put the copied list somewhere far (say column AAA), then you will see your Excel screen going crazy,
which is the Excel screen will go to column AAA. That's why I put the copied list in column H.

i get your idea. that's the reason why you put Application.ScreenUpdating code inside.

Your latest code without the helper is great if the list doesn't have the duplicates value in the consecutive row. i did the testing just now and it will go back to the first duplicate value if the value is not in the consecutive row haha. at the moment my original list only have the duplicate value in the consecutive row.

so i think i will just keep these 2 codes first and decide which code is the most suitable for me. i can say with the helper is the best option because it solved all the duplicates value issue no matter it's in consecutive row or not, is just that i need to add another column for the helper.

i will play around with your codes again and see how it goes. if no additional issue then i will mark this thread as Solved.

I truly appreciate your time to help me this issue.

Thanks a lot

[karmapala]

i did the testing just now and it will go back to the first duplicate value
if the value is not in the consecutive row haha.

LOL. Yes, it will do that because I use ".find" in the code
where the result will always be the first duplicate value in the list.

If the duplicate value in the list is not in a consecutive row, say row 3 and row 10,
soon after the code display the duplicate value from row 10 into the validation cell,
if we run the macro again, then it will go back to get the value in row 3 in the list .

Soon after you told me that there is a duplicate value in the list,
I've tried to do an index using "match" function (hoping that I can use the index as the pointer), but it turn out I can't do it,
because the validation doesn't show the next index for the next duplicate value, the index number just stay.
For example, in the first duplicate value in the validation cell - it show the index is 9 (as it's in the 9th row in the list)
But without running the code, just manually selecting the next duplicate value in the validation cell,
it doesn't want to show 10 ... it still show 9.
And if I select the item after the second duplicate value in the validation cell, the index jump to 11.

i will play around with your codes again

Just now I'm playing around again with the code.
This time I do another way to cheat, but it takes quite a process,
and I'm not sure if it's acceptable for you or not.

Anyway, the cheating process is like this :
1. Manually I create a dynamic named range for the list, with a name "SourceList"
2. Manually I create a pivot table with that "SourceList" as the data source.
Now I have a pivot table without duplicate value in the list (pivot table show only the unique item).
3. Manually I create a dynamic named range for those unique items list of the pivot table, with a name "unique".

Coding :
4. copy the "SourceList" to the next column, then name this new range as "cheat".
5. loop to each item in "unique" to check if the item count in "SourceList" is more than 1 (Yes or No)
6. if Yes, make a variable for that count result of this item, the variable is n.
7. find this item in "SourceList"
8. Do as many as the count result (for i = 1 to n)
9. make a new name to the found item in "SourceList" by adding i after the item name.
10. put this new name next column of the found cell.
11. find next duplicate value (find next)
12. loop (next i)
13. change the validation source range to "cheat".

Now when the user click the down arrow in the validation cell, what he see is like this :
abc123
abc126
abc127
abc128-1
abc128-2
abc125
abc129
abc130
abc131-1
abc131-2
abc131-3
abc132
abc131-4
abc134
abc135
abc136



But I don't know if it's acceptable or not in your condition.

[babychai]

Hi karmapala,

Now when the user click the down arrow in the validation cell, what he see is like this :
abc123
abc126
abc127
abc128-1
abc128-2
abc125
abc129
abc130
abc131-1
abc131-2
abc131-3
abc132
abc131-4
abc134
abc135
abc136

Truly appreciate your suggestion. I think i will not re-create list with the new unique value because i have other formula link to the validation cell. I guess i will use pivot table as the new column source to remove the duplicates value. initially i thought i prefer to retain the duplicate value in my list but this duplicate value give quite headache for the formula haha.

Again, thanks for your effort. your idea & suggestion is brilliant and creative. Thank you

[karmapala]

this duplicate value give quite headache for the formula haha.

I wonder how in the first time the list was made .

I mean if the existence of the list is coming from running code,
then I think it's better to make this code to put only the unique item for the list.

use pivot table as the new column source to remove the duplicates value

Yes, and then use the unique item from the pivot table list as the source for the validation.

Thank you

You're welcome, babychai.

[babychai]

Actually the the list or running number is from Invoice number but with different quantity. that's why it has duplicates invoice number.

So i will mark this thread was solved.

Thank you very much