combo headache

[zaahir]

Hi
I have 2 workbooks A & B. A contains a userform & on that userform is a combox. The combobox needs to draw data off bookB colA.

when using the rowsource function in the properties of the combobox it returns an error msg "invalid property function".
How else can i read the data off bookB using the combobox using code?
Please help!!!

 

[patrickmuldoon99]

This crude code shows one approach. You can iterate down the list in excel, using the additem method of the combo box. The code below for example will start at A1 and go down to A100 adding each item to the combo box named 'mycombo'

x=1
do while x < 100
mycombo.additem cells(x,1)
x=x+1
loop

Hope this sets you in teh right direction

patrick

 

[zaahir]

hi PatrickMuldoon99
thanx, but i how do i link off 1 workbook to another workbook i.e.
get data off workbook Clients onto combobox1 on userform1 in workbook NewJob
??????
i cant get that link to work..
Can you Help or any1 else
Please do!!!!!!!

 

[patrickmuldoon99]

x=1 
do while x < 100 
mycombo.additem Workbooks("Clients").sheets("Sheet1").cells(x,1) 
x=x+1 
loop

Presumes the workbook 'Clients' is already open.

 

[zaahir]

hi.....
nope that aint it...
I have entered the path bt still no good....
Regards

 

[jindon]

Hi
you need to clear RowSource property, otherwise error...
assuming "bookB" is open

Private Sub UserForm_Initialize()
With Workbook("bookB.xls").Sheets("sheet1") '<- change here to suite
   a = .Range("a1",.Range("a" & rows.count).end(xlUp)).value
end with
me.ComboBox1.List = a
End Sub

 

[zaahir]

Hi
when adding this code into "Private Sub UserForm_Initialize()" the button

Private Sub cmdNewEntry_Click()
UserForm1.Show
End Sub
returns with an error code "Run-time error 9" "Subscript out of range"
is the code for the button in the wrong place?

 

[jindon]

Hi
when adding this code into "Private Sub UserForm_Initialize()" the button

Private Sub cmdNewEntry_Click()
UserForm1.Show
End Sub
returns with an error code "Run-time error 9" "Subscript out of range"
is the code for the button in the wrong place?

No
you don't need to add CommandButton, just paste the code as it is onto form module.

It's an event driven code.

It will execute when form loaded...