How can Rowsource be used for a combobox on a form?

[Matt]

I am breaking this question out of the other thread so its visible
better


I am now (trying) to use Rowsource to select the range for
the drop down menu on a form that is macro started.

I need to adjust the rnage of the combobox based on the amount of cells
I have (varies all the time).

Now the problem is that if I select rowsource before I open the form it
gives an error and otherwiese the dropdown menu wont adjust its size
until after the user clicks somehting..

How can I use the rowsource for a combox command before the form is
visible?

Thanks,

Matt

 

[Matt]

I use:

MainMenu.Show

to bring up the form.

I cant use rowsource before MainMenu.Show or it gives an error. I cant
use it after MainMenu.Show because then the form is already there and
the rows not adjusted ...

Matt

 

[Dick Kusleika]

I use:

MainMenu.Show

to bring up the form.

I cant use rowsource before MainMenu.Show or it gives an error. I cant
use it after MainMenu.Show because then the form is already there and
the rows not adjusted ...

Matt:

I think you can use

Load MainMenu

MainMenu.Combobox1.RowSource = "Whatever"

MainMenu.Show

However, you shouldn't ever use RowSource. See here

http://www.dicks-blog.com/archives/2004/05/07/populating-one-column-listboxcombobox/

 

[Tom Ogilvy]

Load MainMenu
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
MainMenu.Combobox1.Rowsource = rng.Address(External:=True)
MainMenu.Show

although I have never had trouble with
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
MainMenu.Combobox1.Rowsource = rng.Address(External:=True)
MainMenu.Show

As soon as you reference MainMenu, it is loaded.

 

[baj]

Hi,

Suppose the list of items you're after are in column A, and suppose
that your list will never be longer than 100 items, which is very
reasonable for a picklist...

Than you can select a range in column A that is bigger than the longest
expected length and add the items untill you meet a blank cell...

This is the startup-procedure for the Userform Try1 that fills Listbox1
with the items found in tha range
ThisWorkbook.Sheets("1").Range("a1:a200")

Private Sub UserForm_Activate()
Dim list1 As Variant

With ThisWorkbook.Sheets("1")
list1 = .Range("a1:a200")
End With

i = 1
For i = 1 To 200
If list1(i, 1) = "" Then
Exit Sub
End If
With Try1.ListBox1
..AddItem (list1(i, 1))
End With
Next

End Sub


Ofcourse you can refine this further...

Bye
Baj

 

[Matt]

Works like a charm! Thanks to all the guys who helped !!!!!!!