none
Data validation in Excel application RRS feed

  • Question

  • hi

    I have an Excel app in which I display project-specific information according to user selections in a series of list boxes

    I want to use validation on cells, referencing the visible contents of a list box

    I don't know how to reference the list box contents though - when I originally wrote the code as a macro, I referenced a range of cells in a data sheet and used 'between'

    However now the simplest way would be to reference the 'Formula1:=' to be the contents of the list

    Everything I've tried though has either failed, or displayed only the first item in the list

    This is the code that works in my macro

    Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=data!A1:A10)

    data! being the worksheet that contained the validation info, but I need to know how to achieve the same goal with the list box contents

    thanks in advance!

    Friday, September 6, 2013 3:12 PM

Answers

  • Hi,

    Except referencing  Fomula1 to a range, you can also reference it to Comma-delimited string (which I provided in my last reply).

    Here is a VSTO sample for your reference.

    object missing = Type.Missing;
    Excel.Range myRange = myWorksheet.get_Range("A1:A10", missing);
    myRange.Validation.Add(Excel.XlDVType.xlValidateList, missing, missing, "Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec", missing);
    

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by DavB-UK Wednesday, September 11, 2013 10:14 AM
    Tuesday, September 10, 2013 6:32 AM
    Moderator

All replies

  • Hi,

    According to your description, you want to reference the Formula1 of validation to content of a list box.

    As far as I know, the Formula1 property of validation is not able to reference to a ActiveX control.

    To work around, I suggest you put the list of content in cells and load them to list box when document being opened.

    Private Sub Workbook_Open()
    ListBox1.Clear
    For i = 1 To 12
        itemText = Worksheets("data!").Range("A" & i).Text
        ListBox1.AddItem itemText
    Next
    
    Dim content As String
    For i = 1 To ListBox1.ListCount
        content = content + ListBox1.List(i - 1) + ","
    Next
    leng = Len(content)
    If content = "" Then
    Else
        content = Left(content, leng - 1)
    End If
    
    With Range("A2:A13").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=content
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End Sub


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, September 9, 2013 12:20 PM
    Moderator
  • Hi

    Yes, the v1 of my app (when it was a macro) worked like this by having the data in cells in a hidden worksheet, but I assumed in moving it to a an AddIn at application level  I could do something a bit neater?

    Are you basically saying there is no way of using validation on a cell that doesn't rely on a range of cells for the corresponding values? Cos the other thing I tried was putting the contents of the list into a StringCollection, but that didn't work either

    Thanks in advance

    Monday, September 9, 2013 3:28 PM
  • Hi,

    Except referencing  Fomula1 to a range, you can also reference it to Comma-delimited string (which I provided in my last reply).

    Here is a VSTO sample for your reference.

    object missing = Type.Missing;
    Excel.Range myRange = myWorksheet.get_Range("A1:A10", missing);
    myRange.Validation.Add(Excel.XlDVType.xlValidateList, missing, missing, "Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec", missing);
    

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by DavB-UK Wednesday, September 11, 2013 10:14 AM
    Tuesday, September 10, 2013 6:32 AM
    Moderator