Listbox in userform to show data in sheet1

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,689
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081

ADVERTISEMENT

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
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,689
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
If you don't use the code that is supplied, I can't help.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,369
Office Version
  1. 2019
Platform
  1. Windows
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
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,689
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,914
Members
413,952
Latest member
JGer

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