Textbox with multiple columns and rows using data from another sheet.

**ali84pk** · 10-17-2011, 08:13 AM

Dear Friends,

I have macro to fill textbox in multiple rows and columns. This works only if data is in same sheet. But my data is in another workbook sheet which i need to be used to fill textbox.
Can some one adjust my code to get data from closed workbook?

(code which i use is as below. Sample of file is also attached)

Private Sub UserForm_Initialize()
   
    'Set properties of listbox1
    With Me.ListBox1
        .BoundColumn = 1
        .ColumnCount = 3
        .ColumnHeads = True
        .TextColumn = True
        .RowSource = "Sheet1!A2:C15"
        .ListStyle = fmListStyleOption
        .ListIndex = 0
    End With

End Sub

Thanks in advance.

**royUK** · 10-17-2011, 08:44 AM

You are using a ListBox not a TextBox.

Try this instead

Option Explicit

Private Sub UserForm_Initialize()
   
    'Set properties of listbox1
    With Me.ListBox1
        .BoundColumn = 1
        .ColumnCount = 3
        .ColumnHeads = True
        .TextColumn = True
        .List = Sheet1.Range("A2:C15").Value
        .ListStyle = fmListStyleOption
        .ListIndex = 0
    End With

End Sub

**watersev** · 10-17-2011, 09:31 AM

hi, ali84pk, as an option to get data for listbox from the closed workbook:

Private Sub UserForm_Initialize()

Application.ScreenUpdating = 0   - turn off screenupdating

    'Set properties of listbox1
    With Me.ListBox1
        .BoundColumn = 1
        .ColumnCount = 3
        .ColumnHeads = True
        .TextColumn = True
        Set wb = Workbooks.Open("C:\Test\Book4.xls") ' - your workbook full path
        x = wb.Sheets("Test").Range("a2:c15") ' - your Sheet name that contains the data required
        .List = x
        .ListStyle = fmListStyleOption
        .ListIndex = 0
    End With
wb.Close 0

Application.ScreenUpdating = 1 - turn on screenupdating

End Sub

**ali84pk** · 10-17-2011, 10:23 AM

Thanks Roy UK and Watersev.

Watersev : Your reply fulfills my requirement. thanks again.

**royUK** · 10-17-2011, 10:29 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save