title: Creating a drop down list only if one cell is empty
body: <p>Suppose that I have a catalog with shapes (Square, Circle, Triangle) and colors (red, blue, green). Each product has it's unique code (SR = Red Square, TB= Blue Triangle, etc).
  In column A, the user can input the catalog number, and in column B and C the shape and color, respectively.</p>
  
  <p>What I want is that, if the user types the catalog number, then automatically B and C are filled (wich I know how to do), or alternatively, if the user doesn't input the cat. number, then B and C have drop-down menus for him to choose.</p>
  
  <p>I've been trying to create a function that does it, but I haven't been able to succeed.</p>
  
  <p>Here is what I've been doing:</p>
  
  <pre><code>Function DropDown(Clave As String)
      If Len(Clave) =0 Then
          With ActiveCell.Validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                  xlBetween, Formula1:="=Shapes"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With
      Else
          'Code that finds the shape depending on Clave input
      End If
  End Function
  </code></pre>
  
  <p>So far, I've managed to create a function that if the user inputs the cat. number, then the drop-downs only have one element with the correct choices, but the cells of the drop-downs are empty until the user selects the menus, wich defeats the purpose entirely. This could be solved if the drop-down could autoselect the only choice or, as defect, the first choice.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>The code above is capable of creating a drop-down if the input is empty, the problem is that a pop-up window immediatly shows that data is not consistent (wich is obvious, as it shows the function <code>=DropDown(A1)</code>). The other problem is that, if the user selects one of the options, the function gets erased from the cell and the functionality is loss.</p>
  
  <p>I've been thinking of solutions to this problem, still to no avail. One thought is to run a macro that checks all drop-downs in the document, and if there is only one option on the list, the macro auto-select it. The thing is I want to do this <em>on the fly</em>, meaning that if the user inputs the cat. no. the drop-down option gets selected, but if he erases it, then the drop-down goes blank.</p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>I've been thinking that I can use an event changer that depends on the Cat. No. cell, to solve the issue. For example, suppose that A1 is the Cat. No. cell. If is empty, then in B1 and C1, the macro code detects the empty cell and creates the drop-downs for shapes and colors respectively. When the user types something in the cell, then the event changer detects it, and the macro fills the cells with the code information, as it detects A1 is no longer empty. </p>
  
  <p>What do you guys think? I could really use some input here.</p>