Sheet, range in vba

[tlindeman]

I know the following is not the right code, however could someone help me point an array to a spreadsheet called "MAIN", cell A1.

Private Sub ComboBox1_GotFocus()
ComboBox1.List = Array(Range(sheet("MAIN)("a1").Value)

End Sub

Thank You
Tony

 

[Smitty]

How can you have an array from one cell?

Do you have a bunch of values in there that you want to use to populate the combo box?

 

[tlindeman]

Sorry, I meant cells A1:A5

 

[tlindeman]

I do have a range of values, I know how to create that, however, I need to point to a range of cells, because the values may change in the future and I have to build multiple combo boxes.

Thank you
Tony

 

[Smitty]

See if this does it for you:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_GotFocus()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 5<br>            Me.ComboBox1.AddItem Sheets("MAIN").Range("A" & i).Value<br>        <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[VoG]

One way

Private Sub ComboBox1_GotFocus()
Dim X As Variant
X = Sheets("MAIN").Range("A1:A5")
ComboBox1.List = X
End Sub

 

[tlindeman]

Thank you both, either solution works great.

Tony