Data Validation with VBA without use of worksheet cells


New Member
Jan 3, 2010
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]




MrExcel MVP
Jan 15, 2007
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.
Last edited:


New Member
Jan 3, 2010
Thanks for the quick response. However the suggestions are not what I'm looking for.


New Member
Jan 3, 2010
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?

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...