Listbox Header

[jamescooper]

Have a named range called "AccidentsHeader"

Within my code I have:

Private Sub CommandButton1_Click()

ListBox1.RowSource = "AccidentsHeader"

....

End Sub

ColumHeaders set to TRUE

But I keep getting Column A, Column B, Column C... etc...

Any ideas?

 

[Norie]

James

What exactly do you want in this listbox?

Is it only the headers?

 

[jamescooper]

So my header row is constant.

"$A$1:$AS$1"

Then below this it populates the row depending on what is generated;

Me.ListBox1.Column = Application.Transpose(ary)

Where ary is

    Dim ary As Variant

 ary = Array(TextBox3, ComboBox1, TextBox8, TextBox1, TextBox4, _
                TextBox2, TextBox5, ComboBox2, ComboBox10, _
                ComboBox3, ComboBox7, ComboBox5, ComboBox8, ComboBox13, _
                ComboBox12, ComboBox17, ComboBox15, ComboBox16, TextBox16, _
                ComboBox11, ComboBox4, TextBox13, ComboBox6, "No", "No", "No", "No", "No", "No", "No", "No", ComboBox20, ComboBox22, _
                "N/A", TextBox9, "No", "No", TextBox14, ComboBox9, "N/A", "N/A", "N/A", ComboBox19, TextBox10, TextBox11)

 

[DanteAmor]

You want to fill the headings with this:
ListBox1.RowSource = "AccidentsHeader"

Then you want to fill the listbox with this:
Me.ListBox1.Column = Application.Transpose(ary)

You need that?

Or what you need, you could explain, because I am not understanding.

 

[DanteAmor]

To have headers and data, you need to first fill out the sheet and then fill in the listbox.
This is an example:
First pass the information to the sheet and then load the listbox. All taking your heading as a reference.

Private Sub CommandButton1_Click()
  Dim ary As Variant
  Dim r As Range, sh As Worksheet, lr As Long
  Set sh = Sheets(Range("AccidentsHeader").Parent.Name)
  Set r = sh.Range("AccidentsHeader")
  
  ary = Array(TextBox2, ComboBox1, TextBox8, TextBox1)
  sh.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, UBound(ary) + 1).Value = ary
  
  lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
  ListBox1.RowSource = sh.Name & "!" & r.Offset(1).Resize(lr, r.Columns.Count).Address
End Sub

That is in theory what you should do. But if you don't explain what you need, I'm just trying to guess.

 

[Norie]

You can only have headers in a listbox if you populate it from a range on a worksheet via the RowSource property.

 

[jamescooper]

Thanks, on this line:

    ListBox1.RowSource = sh.Name & "!" & r.Offset(1).Resize(lr, r.Columns.Count).Address

I receive the error

Run-time error '380' Could not set the RowSource property. Invalid property value.


This is my full code:

Private Sub CommandButton1_Click()
    
    Dim Msg As String, UserID As String
    Dim ary As Variant
    Dim Ans As VbMsgBoxResult
    Dim fn As Range
    Dim wsDataAccidents As Worksheet
    Dim Lastrow As Long
    Dim r As Range, sh As Worksheet, lr As Long
    Set sh = Sheets(Range("AccidentsHeader").Parent.Name)
    Set r = sh.Range("AccidentsHeader")
    
  
    reference = TextBox3.Text
    If Len(reference) = 0 Then Exit Sub
  
    Msg = "Do you want overwrite record with reference " & reference & "?"
    Ans = MsgBox(Msg, 36, "Overwrite Record")
     If Ans = vbNo Then Exit Sub
    
     Set wsDataAccidents = ThisWorkbook.Worksheets("Data - Accidents")

    ary = Array(TextBox3, ComboBox1, TextBox8, TextBox1, TextBox4, _
                TextBox2, TextBox5, ComboBox2, ComboBox10, _
                ComboBox3, ComboBox7, ComboBox5, ComboBox8, ComboBox13, _
                ComboBox12, ComboBox17, ComboBox15, ComboBox16, TextBox16, _
                ComboBox11, ComboBox4, TextBox13, ComboBox6, "No", "No", "No", "No", "No", "No", "No", "No", ComboBox20, ComboBox22, _
                "N/A", TextBox9, "No", "No", TextBox14, ComboBox9, "N/A", "N/A", "N/A", ComboBox19, TextBox10, TextBox11)
              
    Set fn = wsDataAccidents.Columns(1).Find(reference, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fn.Resize(, UBound(ary)).Value = ary
            
        Else
            MsgBox "ID " & reference & Chr(10) & "Record Not Found", 48, "Not Found"
        
        End If

Lastrow = Sheets("Data - Accidents").Cells(Rows.Count, "A").End(xlUp).Row
        
    Sheets("Data - Accidents").Range("A2:A" & Lastrow).Select
        With Selection
    .NumberFormat = "General"
    .Value = .Value
    End With
    
    sh.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, UBound(ary) + 1).Value = ary
    lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
    ListBox1.RowSource = sh.Name & "!" & r.Offset(1).Resize(lr, r.Columns.Count).Address
    
'Me.ListBox1.Column = Application.Transpose(ary)
'ListBox1.RowSource = "AccidentsHeader"
        
MsgBox ("Reference " & reference & " overwritten")

ActiveWorkbook.Save

End Sub

 

[Norie]

Does the sheet name have spaces in it?

ListBox1.RowSource = "'" &  sh.Name & "'!" & r.Offset(1).Resize(lr, r.Columns.Count).Address

 

[jamescooper]

Norie - Corrected that so the code doesn't fail - now I have the following:

So upon opening the userform I have that where the second row here is the header row.

Then upon submission:

The header row is then correct, but it is showing 2 rows, rather than just 1.

I am thinking this line of code has something to do with this;

    sh.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, UBound(ary) + 1).Value = ary

Any ideas?

 

[Norie]

In the first image it appears as if you don't actually have any data, is that right?

If it is what do you want to happen?

 

[jamescooper]

yes there will be no data populated to begin with.

I would just like the headers to show initially and then when the macro is initiated for it to populate the row below which is dynamic.