Weird error when adding an specific data validation list

Hi guys, hope you're all doing amazing today.

I'm new to VBA, still learning every day and I'm definitely loving it. At this moment, I'm trying to build something up for an specific process and at some point I had to make a dynamic list for people to select a value from it, this list will vary depending on another parameter. The thing is that whenever I assign that list to a data validation, in a cell, and save the document, when I reopen it I get the so called "We found a problem with some content" error. I've tried everything suggested on the internet to fix this error but nothing seems to solve it. Right now, and after realizing that the problem was being triggered by this list, I made a patch on the code to remove the list from the cell before I save the document so it is working great for me to keep coding this thing but... you know, I do want to address the problem and actually solve it, not patched it.

Error image:

image-4.png

And so, I began to break my head, tracking down what on the list was making this to happen. After a few minutes, adding the names of the list one by one, saving and reopening to get the error, I realized that I could have the list on the cell with no error as long as it doesn't had the 9th name on it, which had no sense to me as the name had no weird characters or anything, just another regular string. So I kept digging down the problem by adding the name to the list but this time, word by word, saving and reopening until I've got the error again. Turns out that I can have this name on the list as long as it doesn't had it's last word (or the last two words, I don't remember now). We're talking about a Spanish 5 word name.

I thought it was happening with only this name of the list but when I removed it, placed all the names again and saved the document, the lovely error message showed up again. So I guess it should be happening to another of the names on the list.

Again, I've tried every suggestion about this error. The list is given by a connection with a google sheets but it has nothing to do with that, even if you manually put the list on any cells data validation, the error will come for you. ?What is going on here?. I just want to properly address the error so I don't get any other related erros on the road.

I will attach a copy of the patched file to this post. To replicate the error just go to the "This Workbook" object and comment the whole "Before Save" private Sub. The list is being added on cell selection so Afterwards, you could just click on cell C3 for the list to be assigned to that cell, save the document, close it and reopen it.

The info on the sheet is only for demonstration purposes.

Thanks for your help.

You are almost certainly exceeding the character limit of a DV list. You will need to put the list into a range of cells & refer to that.

Originally Posted by Fluff13

You are almost certainly exceeding the character limit of a DV list. You will need to put the list into a range of cells & refer to that.

Hi Fluff13, thanks for your response. So, even if I don't reach the limit it would give errors when I get close to the limit?. And about your suggestion, actually the list is in a range of cells right now. I am getting it from a table on a different sheet.

You will be ok as long as the list does not exceed 255 characters. But if you already have the list why not just use that?

Originally Posted by Fluff13

You will be ok as long as the list does not exceed 255 characters. But if you already have the list why not just use that?

Oh, I think I know where you're going. So basically what my code is doing is taking a range, storing the values on a string and then putting that string on the DV. Can you give me an example of how I can do that, refer to the list as a range on the DV with VBA?

Thanks.

As you already have the list you can use

Formula:

Please Login or Register  to view this content.

in the "source" box of the DV, no need for VBA

Originally Posted by Fluff13

As you already have the list you can use

Formula:

Please Login or Register  to view this content.

in the "source" box of the DV, no need for VBA

I'll try that when I get to the office next week and let you know...

Thank you bro!

Originally Posted by Fluff13

As you already have the list you can use

Formula:

Please Login or Register  to view this content.

in the "source" box of the DV, no need for VBA

Hey man, just to let you know. I tried what you suggested and it did worked. What I'm doing now is that I have a hidden dynamic table to put in there all elements that matches the criteria of the 1st list and then take the range of that table to the DV code.

Thanks!

How can I edit this post to mark it as solved?

Click on the Thread Tools option at the top of the thread.