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

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

Matt wrote:
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/2...stboxcombobox/


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

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.

--
Regards,
Tom Ogilvy



"Matt" wrote in message
oups.com...
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

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

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