locked
Trim text strings in named array used to create validation list RRS feed

  • Question

  • I am using a worksheet change event to create an adaptive validation list.

    It works fine, however because of the length of some of the device names I would like validation list items to be a "cropped" version of the full device name.

    I can acchieve this by creating a second list on the worksheet, redfining the named range to align to this new list and then use the following formula, "=RIGHT(C239,LEN(C239)-(FIND("-",C239)+1))" to remove everything before the "-" which is a common system description.

    However I assume this is easy to acchive directly in the VBA Sub, if I knew how, without containing the "patch" on the worksheet.

    I have copied by sub in below, I'm learning as I go so please be gentle.

    '_______________________________________________________

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("ControlRange")) Is Nothing Then
            On Error Resume Next
            With Range("ControlDevice").Validation
                .Delete
                .Add _
                Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, _
                Formula1:="=" & Replace(Range("ControlRange").Value, " ", "")
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = "Invalid Control Choice"
                .InputMessage = ""
                .ErrorMessage = "You have tried to enter an invalid control choice." _
                & vbNewLine & "Please use the in the in cell drop down list provided." _
                & vbNewLine & "Press ""Cancel"" to continue."
                .ShowInput = False
                .ShowError = True
            End With
        End If
    End Sub

    '_______________________________________________________

    Monday, July 30, 2012 3:01 PM

Answers

  • Woohoo, solved it myself and learnt new things.  Thank you for the split function Bernie, pitty I copied it in wrong the first time, using a (2) instead of the (1) which made sense to me as I wanted the second result of the split not the first. But I live and learn.

    My final solution where;

    "ControlRange" is a named range with its own validation list

    "ControlList" is a variable which uses the trimmed "ControlRange" value to identify another named range to construct the validation list from

    Including a function/loop to edit the text in each cell to create the abbreviated dynamic validation list

    is,

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("ControlRange")) Is Nothing Then
            If Target.Cells.Count > 1 Then Exit Sub
            With ThisWorkbook.Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
            End With
           
            Dim Cell As Range
            Dim ValidationItem As String
            Dim ValidationList As Collection
            Dim csvValidationList As String
            Dim ControlList As Variant
            Set ValidationList = New Collection
            ControlList = Replace(Range("ControlRange").Value, " ", "")
            For Each Cell In Worksheets("Trade Price List").Range(ControlList)
                ValidationItem = Cell.Value
                If InStr(1, ValidationItem, " - ") > 0 Then
                    ValidationItem = Split(ValidationItem, " - ", 2, vbTextCompare)(1)
                End If
                ValidationList.Add CStr(ValidationItem)
            Next Cell
            For n = 1 To ValidationList.Count
                csvValidationList = csvValidationList & "," & ValidationList(n)
            Next n
               
            On Error Resume Next
            With Range("ControlDevice")
            With .Validation
                .Delete
                .Add _
                Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, _
                Formula1:=csvValidationList
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = "Invalid Control Choice"
                .InputMessage = ""
                .ErrorMessage = "You have tried to enter an invalid control choice." _
                & vbNewLine & "Please use the in cell drop down list provided." _
                & vbNewLine & "Press ""Cancel"" to continue."
                .ShowInput = False
                .ShowError = True
            End With
            .ClearContents
            End With

            With ThisWorkbook.Application
                .Calculation = xlCalculationAutomatic
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        End If
    End Sub


    • Marked as answer by Antony White Thursday, August 2, 2012 1:09 PM
    • Edited by Antony White Thursday, August 2, 2012 1:10 PM
    Thursday, August 2, 2012 1:09 PM

All replies

  • Easier would be to simply control what can be placed in the data validation list source:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("ControlRange")) Is Nothing Then Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub
        Application.EnableEvents = False
        Target.Value = Replace(Target.Value, " ", "")
        If InStr(1, Target.Value, "-") > 0 Then
            Target.Value = Split(Target.Value, "-")(1)
        End If
        Application.EnableEvents = True
    End Sub

    HTH, Bernie


    Monday, July 30, 2012 4:59 PM
  • Hi Bernie,

    Thank you for your response.

    It is drawing off of a common product list (1,000 or so lines) which is linked to a language file (a dozen or so languages) and the full description of the products is required for display in the output worksheets.

    While your change event changes the text after selecting the product from the dropdown, the full text descrion is too wide and the key part of the description is trimmed off while trying to select the product from the dropdown itself.

    My original plan was to;

    create an array or collection from the named range,

    use the split function you have introduced me to above to reduce the description,

    create a CSV string from the array/colection entries,

    insert that string as the validation formula.

    I just dont know how to acchieve this in VBA.


    Thursday, August 2, 2012 10:18 AM
  • Woohoo, solved it myself and learnt new things.  Thank you for the split function Bernie, pitty I copied it in wrong the first time, using a (2) instead of the (1) which made sense to me as I wanted the second result of the split not the first. But I live and learn.

    My final solution where;

    "ControlRange" is a named range with its own validation list

    "ControlList" is a variable which uses the trimmed "ControlRange" value to identify another named range to construct the validation list from

    Including a function/loop to edit the text in each cell to create the abbreviated dynamic validation list

    is,

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("ControlRange")) Is Nothing Then
            If Target.Cells.Count > 1 Then Exit Sub
            With ThisWorkbook.Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
            End With
           
            Dim Cell As Range
            Dim ValidationItem As String
            Dim ValidationList As Collection
            Dim csvValidationList As String
            Dim ControlList As Variant
            Set ValidationList = New Collection
            ControlList = Replace(Range("ControlRange").Value, " ", "")
            For Each Cell In Worksheets("Trade Price List").Range(ControlList)
                ValidationItem = Cell.Value
                If InStr(1, ValidationItem, " - ") > 0 Then
                    ValidationItem = Split(ValidationItem, " - ", 2, vbTextCompare)(1)
                End If
                ValidationList.Add CStr(ValidationItem)
            Next Cell
            For n = 1 To ValidationList.Count
                csvValidationList = csvValidationList & "," & ValidationList(n)
            Next n
               
            On Error Resume Next
            With Range("ControlDevice")
            With .Validation
                .Delete
                .Add _
                Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, _
                Formula1:=csvValidationList
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = "Invalid Control Choice"
                .InputMessage = ""
                .ErrorMessage = "You have tried to enter an invalid control choice." _
                & vbNewLine & "Please use the in cell drop down list provided." _
                & vbNewLine & "Press ""Cancel"" to continue."
                .ShowInput = False
                .ShowError = True
            End With
            .ClearContents
            End With

            With ThisWorkbook.Application
                .Calculation = xlCalculationAutomatic
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        End If
    End Sub


    • Marked as answer by Antony White Thursday, August 2, 2012 1:09 PM
    • Edited by Antony White Thursday, August 2, 2012 1:10 PM
    Thursday, August 2, 2012 1:09 PM