combo headache

zaahir

Board Regular
Joined
Oct 11, 2006
Messages
55
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!!!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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'

Code:
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

Board Regular
Joined
Oct 11, 2006
Messages
55
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

Active Member
Joined
Jun 27, 2006
Messages
345
Code:
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

Board Regular
Joined
Oct 11, 2006
Messages
55

ADVERTISEMENT

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

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
you need to clear RowSource property, otherwise error...
assuming "bookB" is open
Code:
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

Board Regular
Joined
Oct 11, 2006
Messages
55
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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...
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top