title: Making a safe entry with a searchable multiple list Excel VBA
body: <p>I am making an entry data for that I implemented an internet code to make a comboBox with the capacity to search inside a range of cells. The code works well if in the data validating list the value of the reference is an absolute range (per ex. <code>='Posibles valores'!$P$4:$P$103</code>). But I would like to not update the reference and instead just create news rows on a table. I have tried these ways:</p>
  <ol>
  <li><p>Create a named range that has a function <code>=INDIRECT(&quot;Tubos_Tipo[Tubos-Tipos]&quot;</code> in it. Making a reference to a table. But in this case the comboBox just shows the table reference. This way worked to me on data validation list but not without using this macro.</p>
  </li>
  <li><p>On the other hand trying to use a function another function <code>=OFFSET('Posibles valores'!$P$4;0;0;COUNTA('Posibles valores'!$P$4:'Posibles valores'!$P$1000);1))</code> but It gives an error <code>you cannot use reference operators (such as unions, intersections, and intervals), array constants, or the LAMBDA function for Validation of specific data</code>.</p>
  </li>
  </ol>
  <p>This is the code of the searcheable comboBox with some comments:</p>
  <pre><code>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  Dim xCombox As OLEObject
  Dim xStr As String
  Dim xWs As Worksheet
  Dim xArr
   
  Set xWs = Application.ActiveSheet
  On Error Resume Next
  Set xCombox = xWs.OLEObjects(&quot;TempCombo&quot;)
  With xCombox
      .ListFillRange = &quot;&quot;
      .LinkedCell = &quot;&quot;
      .Visible = False
  End With
  If Target.Validation.Type = 3 Then
      Target.Validation.InCellDropdown = False
      Cancel = True
      xStr = Target.Validation.Formula1
      xStr = Right(xStr, Len(xStr) - 1)
      Set nameRangeFormula = Application.ActiveWorkbook.Names(xStr)
      nameRangeFormula = Right(nameRangeFormula, Len(nameRangeFormula) - 1)
      Dim rangeFormula As Range
      'Application.ActiveSheet.Select
      'Set rangeFormula = Range(nameRangeFormula).Select
      
      Set rangeFormula = Names(nameRangeFormula).RefersToRange
      If xStr = &quot;&quot; Then Exit Sub
      With xCombox
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 5
          .Height = Target.Height + 5
          '.ListFillRange = rangeFormula
          .ListFillRange = xStr
          If .ListFillRange = &quot;&quot; Then
              xArr = Split(xStr, &quot;,&quot;)
              Me.TempCombo.List = xArr
          End If
          .LinkedCell = Target.Address
      End With
      xCombox.Activate
      Me.TempCombo.DropDown
  End If
  </code></pre>
  <pre><code>Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Select Case KeyCode
          Case 9
              Application.ActiveCell.Offset(0, 1).Activate
          Case 13
              Application.ActiveCell.Offset(1, 0).Activate
      End Select
  End Sub
  </code></pre>
  <p>I don't know much from VBA more than what I have learnt this past two weeks. Firstly I would like to know if there is some way to execute a function and return the Range. In the case that it is not possible the only way in my mind to resolve thisto create a method with the table header referece as parameter create a Range.</p>
  <p>The last two things, the macro functionality of searchable is used on each column with list validation that the code is in it. Is there any way to select exclusive the worksheet and columns they can be used. This code allows to give a wrong result. It just highlights with a green corner the wrong fields, any way to remove if It's wrong or make a pop up windows like the default data validation.</p>
  <p>Thanks in advance</p>
  <p>Edit: I achived to activate the comboBox for just the columns I wanted with this <code>If Target.Column = 3 Or Target.Column = 4 Then</code> before doing the first big <code>if</code>. For what I'm understanding each &quot;page&quot; has it's own code. Is this correct?</p>