Listbox - Header correct, row not

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
771
Hello - So my userforms listbox loads the header row upon initialisation successfully using this code.

VBA 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?

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 "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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,553
Office Version
  1. 2013
Platform
  1. Windows
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:
Code:
 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,652
Messages
5,549,210
Members
410,905
Latest member
Extjel
Top