patrickeab
New Member
- Joined
- Jan 3, 2010
- Messages
- 11
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."
</PRE>
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."
Code:
[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>