Load combo box 1

[kimtp]

I have two workbooks open, one I am copying from and the other copying to. Workbook from could have multiple worksheets. I would like to load the worksheets into a user form in workbook to.

The first form I asked for the name of the workbook from which to copy. After the user fills in the name of the book, I trap for vb err.number 9 'subscript out of range' which I think means that the workbook is not open.

If the book is open it is at that point that I would like to load the worksheets names into a combo box on form 2. What I have so far is

frm2.cboWorksheet.AddItem Sheets(frm1.txtWorkBook.Text)

which gives an subscript out of range error also. Am familiar with VB but my familiarity with Excel is limited.

Any help is appreciated.

Kim

 

[PHV]

Something like this ?
For Each sht In Workbooks(frm1.txtWorkBook.Text).Sheets
frm2.cboWorksheet.AddItem sht.Name
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kimtp]

phv,

right on target. many thanx.