Re-Transfer Row from Listbox to another sheet VBA

  • Hi Ri,

    Not sure if you are getting the error of the height changing on the listbox, it is the integralheight property on the listbox. Change it from true to false and the listbox will stop size changing.

    For the search I finally got my head around the .rowsource syntax and this appears to be the only way you can get over 10 columns in your listbox. As far as I can tell a helper sheet is the best way to get this to work.

    I created a new sheet, sheet4 then used the code below:

  • I don't what's happening, but I'm out.

    I've already said that you can't use RowSource withall those Columns, also that RowSource is not the best way to load the ListBox.

  • Good job, Mr. Justin, thank you very much. the code works well.

    thank you very much for the sacrifice of your time and thoughts.

    to Mr. royUK also I thank you very much for your attention and understanding so far

    always healthy everything

  • Cheers Ri,

    I am trying to learn the art, was wrong about the method for getting the rows into the listbox apparently it becomes limited if you use .additem for some reason (or that is the best I can work out).

    I am trying to get the search results to fill an array and use that array as the list for the listbox, I will post the code if I work it out.. it is a much better way to do it than using a helper sheet.



  • Here it is using an array instead of a helper sheet :)

    Let me know how it goes.

  • I was also going to suggest you change your naming of the textboxes to something sequential like TB1 through TB31, then you could use a loop to fill them:

    Private Sub TABELPENDUDUK_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'Perintah memasukkan data dari ListBox ke TextBox
    Dim I As Long
    For I = 1 To 31
    Me.Controls("TB" & I).Value = Me.TABELPENDUDUK.Column(I - 1)
    Next I
    End Sub
  • Yes, thank you Mr. Justin all very helpful.

    oh yes, the second code for the search engine looks better, but I have problems to display the date format how to make the format to "dd-mm-yyyy"?

  • Maybe you can help me as well?

    First I apologize if I am posting in the wrong forum, however it seems related to me.

    I am learning VBA and checkbox (listbox maybe) is difficult for me.

    I have a workbook with a lot of sheets. However, I need help with 2 sheets.

    Sheet ("Alert") have a table with 5 columns I wish to copy to Sheet("Alert2") using a button or when the checkbox on Column M is checked (however only the information in the row that have the checkbox checked need to be copied).

    I am trying all the code I can find, but nothing works the way I need.

    Thank you so much for your time.

  • Hi Flavia,

    You are in the right forum but you need to post a new thread, the moderators need to make sure you read the rules here as they like to keep it organised so that people searching for solutions in future can easily find a solution if it has already been provided. Also you will need to post a representative file showing what your problem is and describing the solution you hope to obtain as it is often a waste of time for others to try and do this on your behalf.

    Best of luck


  • HI Ri,

    That is a strange one, the date is stored dd/mm/yyyy in the array and then changes to mm/dd/yyyy in the listbox. This appears to fix it, but I do not know why it needs fixing.

  • Ri,

    This also fixes the date problem, put .text on the end of the cell reference when filling the array keeps the existing format - I am not sure if it will introduce other errors with the numbers though:

  • Mr. Justin, help me again, I have a problem in the search box.

    after I get the first search result, then when I type for the next search it generates an error: Subscript Out of Range with debugs:

    Dim ws1 As Worksheet: Set ws1 = Sheets (Me.cmbNSheet.Value)

  • Yes sir, there really is no problem when the workbook that is opened is only the file. but when I open another workbook the problem appears

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!