Is it possible to increase the limit of 256 characters in the validation drop-down lists?

Question

I am dynamically creating a check and have reached the limit of 256 characters. My check looks something like this:

Level 1, Level 2, Level 3, Level 4..... 

Is there a way around a character’s limitation other than what the range indicates?

Validation is already done in VBA. Increasing the limit is the easiest way to avoid any influence on how this sheet works.

Answer 1

I'm sure the 256 character limit is limited, Joel Spolsky explains why here: http://www.joelonsoftware.com/printerFriendly/articles/fog0000000319.html .

However, you can use VBA to get closer to replicating inline validation functionality by encoding the Worksheet_Change event. Here's a layout to give you this idea. You might want to reorganize it to cache ValidValues, handle changes in cell ranges, etc.

 Private Sub Worksheet_Change(ByVal Target As Range) Dim ValidationRange As Excel.Range Dim ValidValues(1 To 100) As String Dim Index As Integer Dim Valid As Boolean Dim Msg As String Dim WhatToDo As VbMsgBoxResult 'Initialise ValidationRange Set ValidationRange = Sheet1.Range("A:A") ' Check if change is in a cell we need to validate If Not Intersect(Target, ValidationRange) Is Nothing Then ' Populate ValidValues array For Index = 1 To 100 ValidValues(Index) = "Level " & Index Next ' do the validation, permit blank values If IsEmpty(Target) Then Valid = True Else Valid = False For Index = 1 To 100 If Target.Value = ValidValues(Index) Then ' found match to valid value Valid = True Exit For End If Next End If If Not Valid Then Target.Select ' tell user value isn't valid Msg = _ "The value you entered is not valid" & vbCrLf & vbCrLf & _ "A user has restricted values that can be entered into this cell." WhatToDo = MsgBox(Msg, vbRetryCancel + vbCritical, "Microsoft Excel") Target.Value = "" If WhatToDo = vbRetry Then Application.SendKeys "{F2}" End If End If End If End Sub