none
Creating a Validation List using new Range Name

    Question

  • Hello!

     

    I am creating a Excel Validation to a cell.

    Before this i am creating a Name to as a reference to some cells

    The problem is that the validation dosn´t acept my new Name. Is this a Error or i have some error?

    i also tried to set the validation formula like this ==Ayudas!j8,Ayudas!j9 but i recived an error

     

    Thanks

     

    this si my code

     

    'Create name

    Dim name As Excel.Name = Globals.ThisAddIn.Application.ActiveWorkbook.Names.Add("Test", "=Ayudas!j8,Ayudas!j9") 

    Try

                With sheet2.Range("f1").Validation

     

                    .Delete()

                    .Add(Type:=Excel.XlDVType.xlValidateList, Formula1:="=Test")              --- Here is the problem

                    .IgnoreBlank = True

                    .InCellDropdown = True

                    .InputTitle = ""

                    .ErrorTitle = ""

                    .InputMessage = ""

                    .ErrorMessage = ""

                    .ShowInput = True

                    .ShowError = True

                    .ErrorTitle = "Error validación"

     

                End With

            Catch ex As Exception

                MsgBox(ex.Message)

            End Try


     

     

    Wednesday, June 23, 2010 2:58 PM

Answers

  • Hello Saul_l

    I have worked with a thread for excel validation. There is a code sample in this thread: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/24787ab3-474c-46e0-ae25-cb048f9e8c32.

    This code block is written by C#. If you have any further question for this link, or this link does not help you, please feel free to follow up.

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by saul Laniado Tuesday, June 29, 2010 1:49 PM
    Friday, June 25, 2010 8:37 AM
  • Hi, Bessie

     

    Sorry about delay i was trying to test this.

    The link you send me was the correct solution. i want to attach my code in Vb.

    tanks for your time

     

    Dim _rango As Excel.Range
          _rango = sheet2.Range("H12:H14")
        
    
          Dim str = New StringBuilder("=").Append(_rango.Address(True, True, Excel.XlReferenceStyle.xlA1, True, True)).ToString
    
    
          Dim name3 As Excel.Name = Globals.ThisAddIn.Application.ActiveWorkbook.Names.Add("Hola3", str, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing)
          
          With sheet2.Range("f2").Validation
    
            .Delete()
            .Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "=Hola3", System.Reflection.Missing.Value)
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
            .ErrorTitle = "Error validación"
    
          End With
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try

    • Marked as answer by saul Laniado Tuesday, June 29, 2010 1:49 PM
    Tuesday, June 29, 2010 1:49 PM

All replies

  • Hello Saul_l

    I have worked with a thread for excel validation. There is a code sample in this thread: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/24787ab3-474c-46e0-ae25-cb048f9e8c32.

    This code block is written by C#. If you have any further question for this link, or this link does not help you, please feel free to follow up.

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by saul Laniado Tuesday, June 29, 2010 1:49 PM
    Friday, June 25, 2010 8:37 AM
  • Hello again,

    How this question is going on in your side? If you have any concern for this issue, please feel free to follow up.

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, June 29, 2010 1:51 AM
  • Hi, Bessie

     

    Sorry about delay i was trying to test this.

    The link you send me was the correct solution. i want to attach my code in Vb.

    tanks for your time

     

    Dim _rango As Excel.Range
          _rango = sheet2.Range("H12:H14")
        
    
          Dim str = New StringBuilder("=").Append(_rango.Address(True, True, Excel.XlReferenceStyle.xlA1, True, True)).ToString
    
    
          Dim name3 As Excel.Name = Globals.ThisAddIn.Application.ActiveWorkbook.Names.Add("Hola3", str, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing)
          
          With sheet2.Range("f2").Validation
    
            .Delete()
            .Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, "=Hola3", System.Reflection.Missing.Value)
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
            .ErrorTitle = "Error validación"
    
          End With
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try

    • Marked as answer by saul Laniado Tuesday, June 29, 2010 1:49 PM
    Tuesday, June 29, 2010 1:49 PM
  • Thanks very much for coming back to report this VB code. It will probably help others in this forum.

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, June 29, 2010 3:55 PM