title: vba dynamical filling combo boxes
body: <p>Here is the set up, (all testing code)</p>
  
  <p>I have a combo box on sheet one called "Devmod"</p>
  
  <p>I have created a module called testing and with in it created the following sub</p>
  
  <pre><code>Sub setcomb()
  
      Sheet1.Devmod.ListFillRange = Range(Sheets("Device_info").Range("l3"), _
                        Sheets("Device_info").Range("l3").End(xlDown)).Select
  End Sub
  </code></pre>
  
  <p>this runs OK but does not fill the combo box with any data (although it does select the right cells I want) </p>
  
  <p>Now I know I can do this be creating a name range and then assigning this to the combo box.</p>
  
  <p>but what I am trying to do is when the user click a "update button" each combo box is updated with the values entered. </p>
  
  <p>In logical terms this would be</p>
  
  <p>Select the range of cells from the Device_info sheet, 
  Start at cell L3 and work out the last cell used. 
  Assign these values to the combo box. </p>
  
  <p>can I do this with one or two lines of code? </p>
  
  <p>Cheers</p>
  
  <p>DevilWAH</p>
  
  <p><strong>ANSWER</strong>
  My simple function ends up as </p>
  
  <pre><code>    Function setcomblist(wsheet As String, startrng As String, Optional endrng As Variant)
  
  'used to populate comboboxes from a dynamic lenth range taking named sheet and starting cell as argument.
  'use: combobox.ListFillRange = setcombolist("sheetname", "startcell")
  
  If IsMissing(endrng) Then
  
  setcomblist = Range(Sheets(wsheet).Range(startrng), Sheets(wsheet).Range(startrng).End(xlDown)).Address(External:=True)
  
  Else
  
  setcomblist = Range(Sheets(wsheet).Range(startrng), Sheets(wsheet).Range(endrng)).Address(External:=True)
  End If
  
  End Function
  </code></pre>
  
  <p>Thanks agin for the help people</p>