• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Data Validation with VBA without use of worksheet cells

Hi Experts,

In the attached file you will find the code below. It inserts Data Validation in Sheet1!A1 using only VBA. Unfortunately there appears to be a limit to the length of string that can be written to the DV 'Source' box. I have set up the macro so that you can determine the number of string elements (options) there are in the DV. You will find that round about 40 elements is the maximum before Excel crashes - perhaps not immediately but perhaps the next time the DV is tried - so please be careful. I would like the maximum number of elements/options in the DV string to be about 250.

So my question is: How can I get round this limitation?

The restrictions are: There must be no worksheet cells used for the Data Validation.

Can an array be used - if so, please demonstrate with code.

Patrick
Sub DV()
Dim i As Long
Dim j As Long
Dim str1 As String

MsgBox "Beware this might crash Excel" & vbCrLf & "if you enter too large a number (>40)" & vbCrLf & "in the Inputbox." & vbCrLf & "Press CTRL+Break NOW, if you're concerned"
j = InputBox("Max elements in string", "Enter a number")

For i = 1 To j
    str1 = str1 & "TAR" & i & ","
Next i

str1 = Left(str1, Len(str1) - 1)

With Sheets("Sheet1").[a1].Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=str1
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

End Sub

Open in new window

Data-Validation-test-01.xls
0
patrickab
Asked:
patrickab
  • 14
  • 11
  • 6
  • +3
1 Solution
 
TomSchreinerCommented:
It's a max length issue.  If you use cells, validation can reference the list.  If you hardcode a list, it is saved by value so to speak and is therefore limited by length, and not neccessarily the number of delimited items.  You can examine the binary to see how the list is actually saved as a nullchar(0) delimited list.  What is the problem with naming the range that contains your list, hiding that specific worksheet, and then using the name in your validation list?
0
 
patrickabAuthor Commented:
TomSchreiner,

Thanks for your input.

I was aware that it was a max length issue as I had experimented on the matter many times with different string lengths. I have no idea how or indeed where to examine the binary as I haven't had such a piece of software for over ten years - perhaps there is a way that I'm unaware of...

Of course I could do as you suggest, and indeed that is the solution that I have already adopted, but that's an evasion scheme rather than an avoidance scheme. I wanted, and still want a cleverer way to do it - if there is one.

Thanks all the same for looking in.

Patrick
0
 
TomSchreinerCommented:
"an evasion scheme"

A hidden worksheet with a list will use no more space on your drive or in memory than any other method you presribe to.  I think I'll just chock this up to your curiosity but let's not get puritanical.  *smiles*
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
patrickabAuthor Commented:
>A hidden worksheet with a list will use no more space on your drive or in memory than any other method you prescribe to.

That wasn't the point. I wanted/want to find a way to do it.

Patrick
0
 
TomSchreinerCommented:
I hear you and stated that in my commnet.

"I think I'll just chock this up to your curiosity "

Been there, done that, and enjoyed that.
0
 
patrickabAuthor Commented:
TomSchreiner,

>"I think I'll just chock this up to your curiosity"

Nope, I want a solution - if there is one. It's a bit like the different ways that a UserForm ListBox can be populated. I would like to find that for DV there are similarly different VBA ways that don't use worksheet cells.

Patrick
0
 
TomSchreinerCommented:
If you do not get an answer here, then try mrexcel.com or the MS boards though you will likely get the same sort of "Why?" feedback.  If you do find a solution, I would be curious to know about it even though I doubt that I'll ever use it.
0
 
patrickabAuthor Commented:
TomSchreiner,

As a newcomer to Experts Exchange, welcome.

It looks as if Excel/VBA is one of your areas of operations. I trust you're well up to speed in how Experts Exchange works. Just in case you're not here's my canned take on it - for what it's worth. Please don't take offence if you already know all about it.

"Perhaps you are not aware of how the points system works for the answerers. It works as follows. Let's assume it's for a question for which 500 points have been offered by the Questioner:  
 
Points offered    Grade         Multiplier      Answerer receives    Cost to Questionner  
500                       A                4                 2000                             500  
500                       B                3                 1500                             500  
500                       C                2                 1000                             500  
                     
It is also important  to point out that if you're a Premium Service Member you can ask as many questions as you like and offer up to 500 points for a question. As you can see it does not cost more to award an A. However if you feel that your question has not been answered fully or for example you have only been given a partial answer then a B grade is fair. However it is only right that you say in what way your question has not been fully answered and before you 'Accept' an answer with the intention of awarding a B grade you ensure you have specified in detail what you are needing and that you have given answerers ample opportunity to provide an answer that solves your problem.
 
Please do not leave questions open for ever. If you abandon a question, after 21 days it will be considered as abandoned and will be dealt with by the clean-up volunteers. If a good answer has been given then the points and grade will be awarded to the answerer. So you cannot 'save' points by not 'Accepting' an answer.  Apart from which as a Premium Service Member 'saving' points does not affect the cost of your subscription.
 
On the matter of obtaining good answers, it is very much up to the questionner to respond to comments and to supply further information or feed-back. These are crucial elements to the whole EE process. You will find that the better your feedback the better the answers will be. The answerers are all unpaid volunteers, many but not all of whom work in the IT industry. So they are here because they want to be, not because it earns them a living - because it doesn't!
 
I do hope that helps and that you will accept these comments as just helpful - as that's how they are intended."

As you will have already found out you can become a Premium Service Member fairly easily - and with your expertise, very easily.

Patrick
0
 
patrickabAuthor Commented:
>Why?

That's the usual question posed of mountaineers. And the usual answer is because it's there and because I enjoy it.

Patrick
0
 
Patrick MatthewsCommented:
Hello Patrick,

Data Validation has its uses; my favorite use for it is to enable a drop-down list to select from, as I eschew
placing ActiveX or Form controls on the worksheet.

That said, if there is any possibility that users will copy/paste into the range(s) where I might consider using
DV, I start thinking instead about enforcing validation in a Worksheet_Change event sub.  Pasting can
obliterate your DV, and then you are stuck :(

Regards,

Patrick
0
 
TomSchreinerCommented:
Thanks for all the info.  Whenever I do searches for this or that, this website comes up.  That's why I joined.
0
 
patrickabAuthor Commented:
Hi Patrick,

Happy New Year to you.

>Data Validation has its uses; my favorite use for it is to enable a drop-down list to select from, as I eschew placing ActiveX or Form controls on the worksheet.

I avoid them like the plague. I don't like them and I find them inflexible. You make a good point about the shortcomings of DV with regards to copying and pasting.

Regards

Patrick
0
 
Patrick MatthewsCommented:
Tom,

Welcome aboard.  EE is always in need of a few good Experts, and we're glad you decided to join the party.

Your first task will be to make sure you can keep Patrick and I straight in your head :)

Patrick
0
 
TomSchreinerCommented:
Thanks for the welcome....Patrick.  Will I be required to change my name to Patrick?
0
 
patrickabAuthor Commented:
Tom,

>Your first task will be to make sure you can keep Patrick and I straight in your head :)

That's easy. The other one is the clever one.

I've just discivered that you're an escapee from MrExcel. Or perhaps just doing a bit of moonlighling here?

Patrick (A-B that is)
0
 
MakriniCommented:
Lol - I think everyone has dabbled both here and Mrexcel
0
 
TomSchreinerCommented:
I have been away from the boards for several years and away from programming in general.  Mr Excel is still a friendly sort of place unlike some other boards that are full of stuffy know it alls.  I enjoy this and don't mind being wrong every now and then.  I hope this is a friendly place as well...
0
 
TomSchreinerCommented:
Guys.  I spent quite a bit of time working on a filter and then it failed to save.  I then noticed that the site is in beta.  I am unsure if I did something wrong or not but would like to set up a filter.  Any known issues?
0
 
patrickabAuthor Commented:
Tom,

We're highly competitive but friendly with it. Not all EE zones are the same but this one retains and maintains a good approach. We're pretty down to earth here and tend to keep clear of the theory and instead keep to practical solutions. It's rare to get a contribution here like yours on frame controls :)). I have to say I don't understand it even though I create UserForms on a regular basis - but that's just me no doubt.

Patrick
0
 
patrickabAuthor Commented:
Tom,

I believe that EE's site is in the process of being altered - certainly it has changed in the last few days.

Patrick
0
 
Patrick MatthewsCommented:
Tom,

EE keeps a beta site up to test new functionality; a number of the newer features make their debut on the
beta site for extra tire-kicking before being promoted to production.

For help with the the filter, you may want to post a question to the New to EE zone.

If you will be sticking around, you may also want to look at a 3rd party app, QuickEE, developed by Excel
and VB.Net habitue webtubbs.  His EE profile has a link to download the program.  I use it extensively
when I am working questions.

Patrick
0
 
TomSchreinerCommented:
10-4  Thanks.  I did, btw, for the fun of it, try and edit in data validation by opening up a workbook in binary mode.  I was able to replace the existing validation successfully but if I at all changed the actual length in bytes,  Excel would report the file as corrupt.  Anyway, I must be done with it as the solution, even if it worked, would require outside the box techniques.
0
 
Patrick MatthewsCommented:
Sorry to hijack your thread there, Patrick :)
0
 
ahammarCommented:
Hi Patrickab,
I can't duplicate your problem, but I am using 2007 and I don't know if you are.  I can enter up to 1150 and it works flawlessly and repeatively.  Just for kicks, change your "With" statement to this and see if you still have the same problem...

With Sheets("Sheet1").[a1].Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=str1
    .IgnoreBlank = True
    .InCellDropdown = True
End With
0
 
TomSchreinerCommented:
I am replying from QuckEE.  Nice tool!  The download page did not provide any info about the author.  I would like to send him or her my thanks.  Any info?  Thanks...
0
 
patrickabAuthor Commented:
Hi Albert,

I'm afraid I'm still working with the steam-driven version 2002. It must be that they've fixed the length limitation in v.2007. Perhaps I'll get the new version when I buy a new pc - one day...

Meantime I'll experiment with a shortened With statement to see if that helps.

Patrick
0
 
patrickabAuthor Commented:
Albert,

I'm afraid the macro errors on:

.Add Type:=xlValidateList, Formula1:=str1

and on my edition:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=str1

when the options in this instance are more than 40.

I guess it's time to buy a new pc and associated software - well at least I can dream about it.

Patrick
0
 
Patrick MatthewsCommented:
TomSchreiner said:
>>The download page did not provide any info about the author.  I would like to send him or her my thanks.  
>>Any info?

Wayne is a Zone Advisor, so he will have an email address of webtubbs "at" e-e "dot" com.
0
 
Patrick MatthewsCommented:
Patrick said:
>>Perhaps I'll get the new version when I buy a new pc - one day...

If you do upgrade, be sure to go to Office 2010 and not Office 2007.  2007 --> 2010 is a smaller leap than
2003 --> 2007 was, but at least in Excel the 2010 version adds some very cool new data visualization, such
as Sparklines, and it also makes it *much* easier to customize the Ribbon than in 2007.

Cheers,

Patrick
0
 
patrickabAuthor Commented:
Patrick,

Thanks for that advice. I'm not planning on buying a new pc anytime soon as my current one meets my needs reasonably well. All the same I'll bear your advice in mind when the time comes.

Regards

Patrick
0
 
Rory ArchibaldCommented:
Patrick,
As regards your original question, if you want an in-cell dropdown, and to avoid cells, then you are out of luck. You are stuck with the length limitation.
Regards,
Rory
0
 
patrickabAuthor Commented:
Tom,

Thanks for your input. I guess with v2002 I'm stuck with what I've got.

Everyone else,

Thanks for your various contributions and comments.

Happy New Year to you all.

Patrick
0
 
TomSchreinerCommented:
Thanks for the points.  Patrick.  Am now a premium member and can leech away!    :)
0
 
patrickabAuthor Commented:
Tom,

>Am now a premium member and can leech away!

Good stuff. I think you actually need to get your status changed. Not sure how you go about that. Try this link:

http://www.experts-exchange.com/Community_Support/General/

...if you haven't already organised it.

Patrick
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 11
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now