Change Data Validation List with VBA

[carrob]

Here is my current code:

Please Login or Register  to view this content.

The end of the code is where I am having the issue. It is supposed to change the data validation list of some cells. It is assigned to a button and when the button is clicked and error occurs and when I click debug it highlights

Please Login or Register  to view this content.

Any help would be greatly appreciated.

[MarvinP]

Hi carrob,

Without looking too hard at your code here is my guess.
It has to do with scope (not the mouthwash). The idea is that you have your code behind a single sheet (based on the _Click) or behind a userform. When the code is in a subdivision of a userform or single sheet, it doesn't know about other sheets or userforms. The answer to these problems is to move the code to a MODULE that then can see all the other objects and run the code from there.
See http://www.cpearson.com/Excel/Scope.aspx
If this is not the problem - attach a workbook and we can look further.

[carrob]

Thanks for responding MarvinP. The code I posted is already in my Module1. I tried attaching my workbook but an error occured and I'm not sure what the problem is...is there a size limit? my workbook is 1.44 MB.

[MarvinP]

Yep - there is a size limit on posted attachements.

[carrob]

I created a zip file to get it under 1 MB but that also did not work, are zip files also not allowed?

[carrob]

I fixed it by changing xlvalidatecustom to xlvalidatelist. Custom is used when the list is defined in the VBA, whereas list is used when the list is in a worksheet.