CODEWITHSUNDEEP
vbaexcelexcel-2007
Astr0zombi3s
Astr0zombi3s

Reputation: 11

Set character limit equal to cell value

I have a range of cells (B5:B61) to which I would like to add a data validation list when the value = "Choose" and for all other values to add a character limit = the string length of the cell.

The code works to add the data validation list and text length. But how do I make formula2 = string length of the cell's value?

Sub Attribute_Options_Cell_Validation()

For Each cell In Range("B5:B61").Cells
    With cell
        .Formula = "=myformula"
        .Value = .Value
        If cell = "Choose" Then
    With .Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=new_DDM3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
         End With
         Else
        With .Validation
        .Delete
        .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="1", Formula2:="20"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
End With
End If
End With
Next cell
End Sub

Upvotes: 0

Views: 851

Answers (1)

Tim Williams
Tim Williams

Reputation: 166331

This works for me:

    Dim maxLen as long
    '.....
    maxLen = Len(cell.Value)
    With cell.Validation
        .Delete
        .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="1", Formula2:=maxLen
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Entry is too long!"
        .InputMessage = "Max length: " & maxLen
        .ErrorMessage = "Please enter no more than " & maxLen & " characters"
        .ShowInput = True
        .ShowError = True
    End With

Upvotes: 1

Related Questions