Private Sub UserForm_Initialize() how to combine 2 codes together

[Patriot2879]

Hi hope you can help i want to combine the 2 vba codes below together if possible as i cant have both on same sheet as i get an error hope you can help?

Private Sub UserForm_Initialize()

    cmdUpdate.Enabled = False 'Only enable the button when a row has been returned
    
    'Source for this nifty code is from here:
    'http://stackoverflow.com/questions/10763310/how-to-populate-data-from-a-range-multiple-rows-and-columns-to-listbox-with-vb
    Dim rng As Range
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String
    
    Set rng = Range("ListBox1")

    With Me.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count

        ReDim Myarray(rng.Rows.Count, rng.Columns.Count)

        rw = 0

        For i = 1 To rng.Rows.Count
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
            Next
            rw = rw + 1
        Next

        .List = Myarray
        '.TopIndex = 1
        
    End With
    
    If Val(Me.txtLBSelectionIndex) > 1 Then
        Me.ListBox1.Selected(Val(Me.txtLBSelectionIndex)) = True
    End If

End Sub

And

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 24
ListBox1.RowSource = "B2:F7"
ListBox1.ColumnHeads = True
End Sub

 

[gallen]

What is stopping you adding the 3 lines in the 2nd 1 to the 1st one?

Where do you place them and what error do you get? I've put the lines where I think they should go below

Private Sub UserForm_Initialize()

    cmdUpdate.Enabled = False 'Only enable the button when a row has been returned
   
    'Source for this nifty code is from here:
    'http://stackoverflow.com/questions/10763310/how-to-populate-data-from-a-range-multiple-rows-and-columns-to-listbox-with-vb
    Dim rng As Range
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String
   
    Set rng = Range("ListBox1")

    With Me.ListBox1
        'Set up listbox first
        .ColumnCount = 24
        .RowSource = "B2:F7"
        .ColumnHeads = True

        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count

        ReDim Myarray(rng.Rows.Count, rng.Columns.Count)

        rw = 0

        For i = 1 To rng.Rows.Count
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
            Next
            rw = rw + 1
        Next

        .List = Myarray
        '.TopIndex = 1
       
    End With
   
    If Val(Me.txtLBSelectionIndex) > 1 Then
        Me.ListBox1.Selected(Val(Me.txtLBSelectionIndex)) = True
    End If

End Sub

 

[Patriot2879]

HI i get a error on the line below - method or data not found

 If Val(Me.txtLBSelectionIndex) > 1 Then

 

[gallen]

But if you don't include the three lines in the separate sub you don't get the error?

ListBox1.ColumnCount = 24
ListBox1.RowSource = "B2:F7"
ListBox1.ColumnHeads = True

Naturally you can't have 2 subs named the same nor should you ever need to

 

[Patriot2879]

Hi i have attached my spreadsheet hope it makes more sense, hope you can help please thank you.
listbox view in userform.xlsm

 

[gallen]

Unfortunately I can't download files. Work Firewall blocks them

 

[Norie]

The reason you get the error on this line is because you don't have a control named txtLBSelectionIndex on the userform.

 If Val(Me.txtLBSelectionIndex) > 1 Then

By the way, regarding your original question - why do you want to combine the 2 sets of code?

Part of this code is populating the listbox from a range,

        'Set up listbox first
        .ColumnCount = 24
        .RowSource = "B2:F7"
        .ColumnHeads = True

but then you have this code that's trying to populate it using an array.

        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count

        ReDim Myarray(rng.Rows.Count, rng.Columns.Count)

        rw = 0

        For i = 1 To rng.Rows.Count
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
            Next
            rw = rw + 1
        Next

        .List = Myarray
        '.TopIndex = 1

 

[Patriot2879]

yes i need to put the 2 codes together as i cant have 2 userform initialize on same sheet, but the one code isnt working either which i need to fix somehow. What do you mean by Control?

 

[Norie]

But the 2 sets of code essentially 'cancel' each other.

Do you want to change the way the listbox is populated based on some criteria?

 

[Patriot2879]

yes thats what i want, i have data in sheet1 and when you click on the userform it shows that data in the listbox, then i have textboxes fr when you click on the list it populates the data in the textboxes and this is where you can change the info if required, then i click on the update buton and it should update the data then in sheet1. thats what i am trying to do