Using ActiveX spin button within the worksheet to navigate through a cell drop-down list

[Sergey1989]

Hello experts,

I am trying to find a way to use an ActiveX spin button control to navigate through a drop-down list that is pulled from a table. I know how to do that in the userform, but now I need to do it within the worksheet. Its easy enough to do it when the list of values is in nice increments, than I just add or subtract values, but I have been banging my head against google on how to move through a table or a named range. Please, help me find a way.

Thank you.

[BadlySpelledBuoy]

If you're using a spin button to change the values then why do you need the drop down at all?

Just use the spin button to +1 or -1 on the index of the list.

Maybe it would make more sense if you could attach a desensitized version of your workbook so we can understand what you're trying to accomplish.

BSB

[Sergey1989]

Ok, could you, please, share this magical code that moves index of the list +1 and -1 in the cell via spin button?

I would like to have both options. Drop down - because a list is fairly long. I have some over hundreds of values.

Spin button - to quickly jump to next value.

Thanks

[Sergey1989]

here is a test workbook that, i hope, shows what i want to do

[dotchiejack]

For your example In spinbutton properties set linked cell I10.

[Sergey1989]

I must not have explained clear enough. Adding a linked cell will just change the number in the cell by whatever increment. I need to change a value to a next or previous one in the list or table. That is why the table values are not in order. Because it wont be just number. Its going to be names, properties, etc.

[dotchiejack]

Like this then?

[Sergey1989]

This works precisely how I wanted. Only on this test worksheet. When I try to insert it into my workbook it throws an error 1004. Method 'Range' of object '_Worksheet' failed.
Is it because my table is located on other worksheet in the workbook?

[Sergey1989]

Ok. I just added "ThisWorkbook.Worksheets(" name of worksheet with table ").Range(" table name ").Rows.Count" and now it is working. Thank you very much!

[dotchiejack]

Thanks for the rep.
Have a nice day.