populate listbox

[DB73]

Hi,

i try to populate a listbox from a table (table25)
i want headers but i only want the listbox populated from the 3th row

so in my search on the big www i found that i can add a second listbox and use this as a header and the 1st one only populating with table items without the header

i put this code in the userform;

 Dim ws1 As Worksheet, rng1 As Range
 Set ws1 = Sheets("adressen")
 Set rng1 = ws1.Range("A2:I" & ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row)
 
 With Me.ListBox1
        .BorderStyle = 1
        .BorderColor = &H80000012
        .BackColor = &H80FFFF
        .ColumnHeads = False
        .ColumnCount = rng1.Columns.Count
        .ColumnWidths = "140;130;35;65;100;45;90;85;50"
        .RowSource = rng1.Parent.Name & "!" & rng1.Resize(rng1.Rows.Count - 1).Offset(1).Address
 End With
 
 With Me.ListBox2

        .BorderStyle = 1
        .BorderColor = &H80000012
        .BackColor = &H80FFFF
        .ColumnHeads = True
        .ColumnCount = Columns.Count
        .ColumnWidths = "140;130;35;65;100;45;90;85;50"
        .RowSource = Sheets("adressen").Range("A1:I1").Address
  End With

End Sub

this seems to work as i run the code...but...only with the table sheet open.

when i open another sheet and run the code, the 2nd listbox (header) uses the rowsource of that sheet.
for me its really strange because i had the rowsource as the sheet where the table is on.

so, just 2 questions;
can i populate and use only 1 listbox with headers from table and than only see the lsit from the 3th row (i want the data from the 2nd row safe so nobody can delete it bcause thy cant see it)
or ist it better to use 2 listboxes...but how to populate only the header or first row.

i hope i made it a bit clear what i mean...english is not my native language

thanks in advance

 

[DanteAmor]

Hi @DB73.

I hope you are well.​

I show you how the 2 options could be.

Option 1:

"ist it better to use 2 listboxes"​

I made several changes to your code for you to review in detail.

Private Sub UserForm_Activate()
 Dim ws1 As Worksheet, rng1 As Range
 
 Set ws1 = Sheets("adressen")
 Set rng1 = ws1.Range("A3:I" & ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row)
 
 With Me.ListBox1
    .BorderStyle = 1
    .BorderColor = &H80000012
    .BackColor = &H80FFFF
    .ColumnHeads = False
    .ColumnCount = rng1.Columns.Count
    .ColumnWidths = "140;130;35;65;100;45;90;85;50"
    .RowSource = ws1.Name & "!" & rng1.Address
 End With
 
 With Me.ListBox2
    .BorderStyle = 1
    .BorderColor = &H80000012
    .BackColor = &H80FFFF
    .ColumnHeads = False
    .ColumnCount = rng1.Columns.Count
    .ColumnWidths = ListBox1.ColumnWidths
    .RowSource = ws1.Name & "!" & Range("A1:I1").Address
  End With
End Sub

-----------------------------​

Option 2:

"can i populate and use only 1 listbox with headers from table and than only see the lsit from the 3th row (i want the data from the 2nd row safe so nobody can delete it bcause thy cant see it)"​

For this option to work we need an auxiliary sheet, so create a sheet and name it "aux".

Private Sub UserForm_Activate()
 Dim ws1 As Worksheet, ws2 As Worksheet
 Dim rng1 As Range
 Dim lr As Long
 
 Set ws1 = Sheets("adressen")
 Set ws2 = Sheets("aux")
 
 lr = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
 If lr < 3 Then lr = 3
 ws2.Cells.ClearContents
 ws1.Rows(1).Copy ws2.Range("A1")
 ws1.Range("A3:I" & lr).Copy ws2.Range("A2")
 
 With Me.ListBox1
    .BorderStyle = 1
    .BorderColor = &H80000012
    .BackColor = &H80FFFF
    .ColumnHeads = True
    .ColumnCount = 9
    .ColumnWidths = "140;130;35;65;100;45;90;85;50"
    .RowSource = ws2.Name & "!" & ws2.Range("A2:I" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Address
 End With
End Sub

Here you can find other options to put headers:

Combo Box Fill in Header

Hi, I have a problem with my combo box. In the screenshot that I have provided, you can see "Contact Person", "Basic Wage" and "Time Wage". I want these items to fill the header in the combo box. Can someone help me? Thank you. Here is the code that I use: Public Sub cmFillWorker1(cmName As...

www.mrexcel.com

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[DB73]

Hi @DB73.

I hope you are well.​

I show you how the 2 options could be.

Option 1:

"ist it better to use 2 listboxes"​

I made several changes to your code for you to review in detail.

Private Sub UserForm_Activate()
 Dim ws1 As Worksheet, rng1 As Range
 
 Set ws1 = Sheets("adressen")
 Set rng1 = ws1.Range("A3:I" & ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row)
 
 With Me.ListBox1
    .BorderStyle = 1
    .BorderColor = &H80000012
    .BackColor = &H80FFFF
    .ColumnHeads = False
    .ColumnCount = rng1.Columns.Count
    .ColumnWidths = "140;130;35;65;100;45;90;85;50"
    .RowSource = ws1.Name & "!" & rng1.Address
 End With
 
 With Me.ListBox2
    .BorderStyle = 1
    .BorderColor = &H80000012
    .BackColor = &H80FFFF
    .ColumnHeads = False
    .ColumnCount = rng1.Columns.Count
    .ColumnWidths = ListBox1.ColumnWidths
    .RowSource = ws1.Name & "!" & Range("A1:I1").Address
  End With
End Sub

-----------------------------​

Option 2:

"can i populate and use only 1 listbox with headers from table and than only see the lsit from the 3th row (i want the data from the 2nd row safe so nobody can delete it bcause thy cant see it)"​

For this option to work we need an auxiliary sheet, so create a sheet and name it "aux".

Private Sub UserForm_Activate()
 Dim ws1 As Worksheet, ws2 As Worksheet
 Dim rng1 As Range
 Dim lr As Long
 
 Set ws1 = Sheets("adressen")
 Set ws2 = Sheets("aux")
 
 lr = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
 If lr < 3 Then lr = 3
 ws2.Cells.ClearContents
 ws1.Rows(1).Copy ws2.Range("A1")
 ws1.Range("A3:I" & lr).Copy ws2.Range("A2")
 
 With Me.ListBox1
    .BorderStyle = 1
    .BorderColor = &H80000012
    .BackColor = &H80FFFF
    .ColumnHeads = True
    .ColumnCount = 9
    .ColumnWidths = "140;130;35;65;100;45;90;85;50"
    .RowSource = ws2.Name & "!" & ws2.Range("A2:I" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Address
 End With
End Sub

Here you can find other options to put headers:

Combo Box Fill in Header

Hi, I have a problem with my combo box. In the screenshot that I have provided, you can see "Contact Person", "Basic Wage" and "Time Wage". I want these items to fill the header in the combo box. Can someone help me? Thank you. Here is the code that I use: Public Sub cmFillWorker1(cmName As...

www.mrexcel.com

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Thank u DanteAmor...just what i needed.

i like both options, but i prefere option 1....more easy for me, and then i dont need that extra sheet

srry for the late reaction, but i just came home from work, think we have a little time difference...as i write this, its 18.30PM