2

Hi I am trying to assign values to a Combobox, but am getting run-time error 381. Does anyone know why? Here is the code where the error occurs -

LastAddress = Sheets("TempList").Range("A100").End(xlUp).Address
ComboBox1.List = "TempList!A2:" & LastAddress

Thanks so much!

Here is the revised code that worked. Thanks much.

Dim myArray As Variant
Dim wss As Worksheet

Set wss = Sheets("TempList")

With wss
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    myArray = Sheets("TempList").Range("A2:A" & LastRow)
    Sheets("CompRecTool").Activate
    ComboBox1.List = myArray
End With
Community
  • 1
  • 1
Chris2015
  • 1,030
  • 7
  • 28
  • 42
  • 1
    Does [this thread](http://stackoverflow.com/questions/17946317/how-to-add-items-to-a-combobox-in-a-form-in-excel-vba) help any? – BruceWayne Jan 08 '16 at 19:08
  • Does a comboBox have to include an array? I'm not sure why I can't add a range of cells from a sheet? – Chris2015 Jan 08 '16 at 19:11
  • you can click on the DATA tab, go into Data Validation, and set it to a "list" with a reference, ie if the list is located on a worksheet called "List" in cells A1:A10, the reference would be =List!$A$1:$A$10 – justkrys Jan 08 '16 at 19:15
  • Dim myArray As Variant myArray = Sheets("TempList").Range("A2:A10") Sheets("CompRecTool").Activate ComboBox1.List = myArray – Chris2015 Jan 08 '16 at 19:17
  • The code above worked. I just need to figure out how to declare myArray based on an undefined LastRow – Chris2015 Jan 08 '16 at 19:17
  • is `LastAddress` a String Type? Also maybe use the `(0,0)` arguments for the `Address` to remove the absolute referencing `$`? – Scott Holtzman Jan 08 '16 at 19:20
  • It would help if you included more of your macro, not just the two lines that cause the issue. As you can see, without seeing more, we can just offer some ideas..which may work in the two lines above, but not when you run your whole macro. – BruceWayne Jan 08 '16 at 19:36
  • 2
    I posed the revised code that ended up working for me. Thanks everyone. – Chris2015 Jan 08 '16 at 19:51

0 Answers0