Select next/previous item from data validation list using Command Button

**msn.niyasu** · 03-04-2014, 10:04 AM

I have a data validation list in cell "A1", the validation list is sourced from items which are listed under column "AA". And i also have two command buttons to display the next/previous item from the list while clicking the respective buttons (the same should display in cell A1)

The incell dropdown button working very well to select the desired item. But if i use the command buttons to change the item from the list, from the next time the sheet get activated I am getting the following error message

“Run-time error ' -2147417848(80010108) Method 'add' of object 'validation' failed”

i also wanted the first item from the list to be displayed each time the sheet get activated

The file is attached for reference,

Please help

**nathansav** · 03-04-2014, 11:07 AM

I'd use something like

range("a1").value =worksheetfunction.Index(range(Range("a1").Validation.Formula1),WorksheetFunction.Match(Range("a1").value,range(Range("a1").Validation.Formula1),false)+1,1)

for next

and

range("a1").value =worksheetfunction.Index(range(Range("a1").Validation.Formula1),WorksheetFunction.Match(Range("a1").value,range(Range("a1").Validation.Formula1),false)-1,1)

for prev

with range A1 being the cell with the dropdown validation. You'll need to add a check to make sure the result is never 0 or more rows than in the list.

**msn.niyasu** · 03-05-2014, 02:43 AM

Dear nathansav:
Thank you for your suggestion;

I have removed all the previous codes for "NEXT" and "PREVIOUS" command buttons and replace with your codes; but that just helped me to reduce the length of cording. Still I am facing the same error message, when I go from and come back to the sheet1.

find the attached file for reference

**msn.niyasu** · 03-09-2014, 01:20 AM

Can anyone help me on this, I just want the data validation list to be updated each time the sheet get activated (sheet1- A1), and I want to use command buttons to select next/previous item from the list. But the problem that I am facing is that, whenever I use the command buttons to select items from the data validation list an error occurring the next time when the sheet get activated.

Please find the file attached in my previous reply

**msn.niyasu** · 03-09-2014, 07:26 AM

please help

Select next/previous item from data validation list using Command Button

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Select next/previous item from data validation list using Command Button

Re: Select next/previous item from data validation list using Command Button

Re: Select next/previous item from data validation list using Command Button

Re: Select next/previous item from data validation list using Command Button

Re: Select next/previous item from data validation list using Command Button

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Select item of a dropdown list (data validation) and refreshes values of all pivot tables

macro to select each item in data validation list

Macro to select an item from a data validation list

easy way to select an item from a very long data validation list

Macro to select an item in a Validation List

Bookmarks

Bookmarks

Posting Permissions