Listbox - Header correct, row not

[jamescooper]

Hello - So my userforms listbox loads the header row upon initialisation successfully using this 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

I then want to populate the row upon submission but it isn't quite working, any ideas?

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
    
    ListBox1.RowSource = r.Offset(1).Resize(lr, r.Columns.Count).Address(External:=True)
    
'  sh.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, UBound(ary) + 1).Value = ary
'  lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
          
MsgBox ("Reference " & reference & " overwritten")

ActiveWorkbook.Save

End Sub

Thanks.

 

[JLGWhiz]

It is difficult to analyze your code because you use a named range for your row source, and the sheet name is obscured by using a Parent reference instead of the actual sheet name. I am sure there is a reason for doing that, but it is not helpful in trying to determine which sheet the code that follows is actually appied to. Aslo, the 'lr' variable in this statement:

 ListBox1.RowSource = r.Offset(1).Resize(lr, r.Columns.Count).Address(External:=True)

appears to be invalid since it is not initialize prior to its usage, and should be throwing an error when that line is executed. If it is not throwing an error, it must be returning an invalid address for the 'Resize' function. I stopped there.