Listbox in userform to show data in sheet1

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,227
Office Version
  1. 2010
Platform
  1. Windows
hi good afternoon, i hope you can help me, i have 10 textboxes in my userform where once submitted the data goes to sheet1 from columns A to S and to last row. i want the data to be seen in my listbox in the userform so i can view it i have tried the code below but its not working i just see the heading in A1 and the line below not anything else.
VBA Code:
Private Sub UserForm_Initialize()
  ListBox1.RowSource = Sheets("Sheet1").Range("A1:S3000").Address(external:=True)
End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
VBA Code:
Private Sub UserForm_Initialize()
Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row    '<---- ??????
    With ListBox1
        .ColumnCount = 9    '<---- Change to 18??????
        .ColumnWidths = "50;50;50;50;50;50;50;50;50"    '<---- 18 x each column width
        .List = Range("A1:I" & LastRow).Value    '<---- Change the "I" to the column number used above ( S = 18)
    End With
End Sub
 
Upvote 0
hi thankyou for the code i have updated it with this but nothing happens now the listbox is empty there is no data in it, the data source is in' sheet1 ' which needs to go into the listbox in the userform
 
Upvote 0
hi i have tried updating it the code below with sheet1 in it but not working, hope you can help still please?
VBA Code:
Private Sub UserForm_Initialize()
Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row    '<---- ??????
    With ListBox1
    ListBox1.RowSource = Sheets("Sheet1")
        .ColumnCount = 19    '<---- Change to 18??????
        .ColumnWidths = "50;50;50;50;50;50;50;50;50"    '<---- 18 x each column width
        .List = Range("A1:S" & LastRow).Value    '<---- Change the "I" to the column number used above ( S = 18)
    End With
 
Upvote 0
Hi i have also tried amending your code to the below to add in sheet1 but no joy, hope you can help?
VBA Code:
Private Sub UserForm_Initialize()
Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row    '<---- ??????
    With ListBox1
        .RowSource = Sheets('Sheet1')
        .ColumnCount = 9    '<---- Change to 18??????
        .ColumnWidths = "50;50;50;50;50;50;50;50;50"    '<---- 18 x each column width
        .List = Range("A1:I" & LastRow).Value    '<---- Change the "I" to the column number used above ( S = 18)
    End With
End Sub
 
Upvote 0
If you don't use the code that is supplied, I can't help.
 
Upvote 0
Your range is unqualified so if sheet1 is not the active sheet, you will not get required result

Try without any alteration, this update to your code & see if helps

VBA Code:
Private Sub UserForm_Initialize()
    Dim rng As Range
    With ThisWorkbook.Worksheets("Sheet1")
        Set rng = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 19)
    End With

    With ListBox1
        .RowSource = ""
        .ColumnCount = rng.Columns.Count
        .List = rng.Value
    End With
End Sub

Dave
 
Upvote 0
The reason I said what I said in Post #6 because both in Post #4 and Post #5 you have the following line.
.RowSource = Sheets('Sheet1')
That line should not be there and if you used the code supplied in Post #2 AS IS it would have worked if the sheet with the data is the active sheet.
Like "dmt32" mentioned, if the data sheet is not the active sheet, you'll have to change the code to take that in account.
Code:
Private Sub UserForm_Initialize()
Dim LastRow As Long, sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")    '<---- Change to actual sheet name
    LastRow = sh1.Range("A" & Rows.Count).End(xlUp).Row    '<----- If indeed column A
    With ListBox1
        .ColumnCount = 9    '<----- However many columns you want
        .ColumnWidths = "25;25;25;25;25;25;25;25;25"    '<----- same amount as in ColumnCount. Her for 9 columns
        .List = sh1.Range("A1:I" & LastRow).Value
    End With
End Sub
@dmt32's code should work for you though.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top