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]

You'll need to change the code so that when there's no data you use the blank row below the headers to populate the listbox.

 

[jamescooper]

Sorry I am not sure how this would be achieved?

 

[Norie]

If you want the headers to show 'initially' why not set them in the userform's Initialize event?

 

[jamescooper]

How would this look please?

Private Sub UserForm_Initialize()

    Set sh = Sheets(Range("AccidentsHeader").Parent.Name)
    Set r = sh.Range("AccidentsHeader")

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

End Sub

 

[Norie]

Perhaps.

Private Sub UserForm_Initialize()

    Set sh = Sheets(Range("AccidentsHeader").Parent.Name)
    Set r = sh.Range("AccidentsHeader")

    ListBox1.RowSource = r.Offset(1).Address(External:=True

End Sub

 

[DanteAmor]

How would this look please?

Try this

Private Sub UserForm_Initialize()
  Dim sh As Worksheet, r As Range
  Set sh = Sheets(Range("AccidentsHeader").Parent.Name)
  Set r = sh.Range("AccidentsHeader")
  ListBox1.RowSource = "'" & sh.Name & "'!" & r.Offset(1).Resize(1, r.Columns.Count).Address
End Sub

 

[jamescooper]

So that is progress, that is now showing the header rows correctly, but also the second row (reference 5520) upon initialisation.

So almost there.

Image below;

 

[Norie]

Instead of using the range with the original data use the range where you are copying data to.

 

[DanteAmor]

So that is progress, that is now showing the header rows correctly, but also the second row (reference 5520) upon initialisation.

So almost there.

Image below;


View attachment 4028

As Norie explained in post #6, to show the headings, you must show a row of data.
If in row 2 you have data then they will be displayed in the listbox.

To display headers without data, then row 2 must be empty.

 

[jamescooper]

Ok, so I now have a header but it is not populating the variable row, this is my code:

Private Sub UserForm_Initialize()
 
  Dim sh As Worksheet, r As Range
  Set sh = Sheets(Range("AccidentsHeader").Parent.Name)
  Set r = sh.Range("AccidentsHeader")
 
  ListBox1.RowSource = "'" & sh.Name & "'!" & r.Offset(1).Resize(1, r.Columns.Count).Address

End Sub

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 "reference " & 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("A3:A" & Lastrow).Select
        With Selection
    .NumberFormat = "General"
    .Value = .Value
    End With
    
'   sh.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, UBound(ary) + 2).Value = ary
'  lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
          
MsgBox ("Reference " & reference & " overwritten")

ActiveWorkbook.Save

End Sub