Data Validation with VBA without use of worksheet cells

[patrickeab]

The code below inserts Data Validation (DV) 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 (for that string length) 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.

I am fully aware of the various ways to produce DV so please do not take your precious time explaining them to me - thanks all the same.

To the question of "Why?" My answer is: "Because that's the way I'd like to do it."

[COLOR=#000000]Sub DV()[/COLOR]
[COLOR=#000000]Dim i As Long[/COLOR]
[COLOR=#000000]Dim j As Long[/COLOR]
[COLOR=#000000]Dim str1 As String[/COLOR]
[COLOR=#000000]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"[/COLOR]
[COLOR=#000000]j = InputBox("Max elements in string", "Enter a number")[/COLOR]
[COLOR=#000000]For i = 1 To j[/COLOR]
[COLOR=#000000]   str1 = str1 & "TAR" & i & ","[/COLOR]
[COLOR=#000000]Next i[/COLOR]
[COLOR=#000000]str1 = Left(str1, Len(str1) - 1)[/COLOR]
[COLOR=#000000]With Sheets("Sheet1").[a1].Validation[/COLOR]
[COLOR=#000000]   .Delete[/COLOR]
[COLOR=#000000]   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _[/COLOR]
[COLOR=#000000]   xlBetween, Formula1:=str1[/COLOR]
[COLOR=#000000]   .IgnoreBlank = True[/COLOR]
[COLOR=#000000]   .InCellDropdown = True[/COLOR]
[COLOR=#000000]   .InputTitle = ""[/COLOR]
[COLOR=#000000]   .ErrorTitle = ""[/COLOR]
[COLOR=#000000]   .InputMessage = ""[/COLOR]
[COLOR=#000000]   .ErrorMessage = ""[/COLOR]
[COLOR=#000000]   .ShowInput = True[/COLOR]
[COLOR=#000000]   .ShowError = True[/COLOR]
[COLOR=#000000]End With[/COLOR]
[COLOR=#000000]End Sub[/COLOR]
 


</PRE>

</PRE>

 

[mikerickson]

That's a foolish restriction.

Make a new sheet.
Choose a column for your list and give the cells a Name.
Put the data in the cells,
Use the Name as the source for data validation
Perhaps, hide (or very hide) the new sheet,
The cells and/or the definition of the name can be changed with code.

Looking forward, its much easier to maintain a list on a worksheet than to go back and re-hard-code list entries.

 

[patrickeab]

Thanks for the quick response. However the suggestions are not what I'm looking for.

 

[patrickeab]

BTW, the restrictions you may consider foolish, but I placed them in the vain hope that no one would waste their time telling me how to create Data Validation dropdowns in a conventional manner. It seems my attempt failed at the first hurdle - but I live in hope.

Any other offers?