Populate Listbox & keep updating with worksheet updates.

[adam]

Hi anyone,

I have a userform with a listbox & a text box in my workbook. When I write a text in the text box field of the userform, if that text is available in the list box; it gets highlighted.

What I' m wanting help is to fill the cell D3 of the worksheet named "bills" with the data from column two of the highlighted text; when I double click the text in the user form's list box.

I also want the list box to be updated as I keep entering data to the rows and columns of the "bills" worksheet. Moreover I want all the 15 columns that I have in my worsheet to appear in the list box.

I hope my question is clear.

Any help would be kindly appreciated.

I have attached the worksheet for your reference.

Thanks for taking your valuable time reading this.

[HansV]

The definition of the ListRange name is now =Control!$A$1:$A$41. To make it include the columns A:O, it should be =Control!$A$1:$O$41.
But if you want the number of rows to be updated automatically, you have to use a more complicated formula so that it becomes a dynamic range:

=OFFSET(Control!$A$1,0,0,COUNTA(Control!$A:$A),15)

This means: start at cell A1. Shift 0 rows down and 0 columns to the right (so we're still at A1), then expand to COUNTA(Control!$A:$A) rows, i.e. the number of non-blank rows in column A, and to 15 columns (A through O).

Next, you have to set the Column Count property of the list box to 15, and make the list box much wider.

Create the following code for the list box in the module of the userform:

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Worksheets("Bills").Range("D3") = Me.ListBox1
End Sub

[adam]

That was awesome. Thanks for the help Hans. I do really appreciate it

[adam]

I have modified the VB code associated with the list box in my userform.

My modification now inserts a row below the row A15 when a name from the list box is double clicked. And inserts the text from column 2 of the list box to the row below the inserted row in the worksheet .

What I'm needing help now is to get a modification of the following code so that when the list box is double clicked a blank row is inserted below the text "BLUE" instead of A15 (in column "A") of the worksheet and the column 2 of the list box gets copied into the new row inserted.

If I double click another text in the list box I want that text to be copied to the row below where I had first inserted the text after double clicking.

I hope I have made my question clear.

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Worksheets("Bills").Range("A16") = Me.ListBox1
  Dim C As Long
    C = 1       'Number of rows to insert
    Range("A15").Select   'below this cell

    ActiveCell.Offset(1, 0).EntireRow. _
       Resize(rowsize:=C).Insert Shift:=xlDown
End Sub

[HansV]

Where is this word "BLUE"?

[adam]

Suppose I write the word in any row of the column "A" of the sheet named bills, I want a row to be inserted below the row where "BLUE" exists, after double clicking the listbox.

[HansV]

Try this code:

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim oCell As Range
    ' Search for the word 'BLUE'
    Set oCell = Worksheets("Bills").Range("A:A").Find(What:="BLUE", LookAt:=xlWhole)
    If oCell Is Nothing Then
        MsgBox "The word 'BLUE' was not found in column A", vbExclamation
        Exit Sub
    End If
    ' Insert row below the cell with the word 'BLUE'
    oCell.Offset(1, 0).EntireRow.Insert
    ' Fill cell in column B
    oCell.Offset(1, 1) = Me.ListBox1.Column(1)
End Sub

Note: we use Me.ListBox1.Column(1) for the second column because listbox columns are counted starting at 0, so the first column is Column(0) etc.

[adam]

Thanks for the help Hans. I do really appreciate it.

[adam]

If I may ask; when I populate the list box's columns with numbers the rows does not get highlighted when I write the number in the search box? what may be the reason for this? I did try to figure out from form properties.

[HansV]

It does work in the sample workbook that you attached to the first post in this thread. What have you changed, apart from the modifications suggested by me?

[adam]

I'm having another listbox where I have rows of numbers & texts. So I applied your code to that also changing the code to suit the workbook.
I have attached the workbook for your reference.

The code you had provided works very well. with the previous list box. When you see the code form the form in the memos sheet it will have given a reference to "previous" sheet which I haven't included in this workbook.

What I want to show is that when you write the number in the form in "memos" sheet the list box wont be highlighting the row containing the number.

Thanks in advance.

[HansV]

The first column of the list box on the frmSearchPreviousMemos form takes its values from column B of the Memos sheet. This column contains numbers such as 694, 695 etc. that are formatted to look like 0694, 0695. To search, you must enter the numeric value, i.e. 694, not 0694.

By the way, the caption of the userform should be "Search Previous Memos" instead of "Seach Previous Memos".

[adam]

Thanks for the spelling correction and for the help. You are great . Now I can highlight what I want to search.

[adam]

If I want to insert rows by copying formulas; where should I place the following code.

I have applied formulas to the column C & D of the sheet where the row where "BLUE" exists.

Code: Select all

Dim row As Single
    row = ActiveCell.row
    Selection.EntireRow.Insert
    Rows(row - 1).Copy
    Rows(row).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Selection.SpecialCells(xlCellTypeConstants).ClearContents
    Application.CutCopyMode = False

with the code

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim oCell As Range
    ' Search for the word 'BLUE'
    Set oCell = Worksheets("Bills").Range("A:A").Find(What:="BLUE", LookAt:=xlWhole)
    If oCell Is Nothing Then
        MsgBox "The word 'BLUE' was not found in column A", vbExclamation
        Exit Sub
    End If
    ' Insert row below the cell with the word 'BLUE'
    oCell.Offset(1, 0).EntireRow.Insert
    ' Fill cell in column B
    oCell.Offset(1, 1) = Me.ListBox1.Column(1)
End Sub

Thanks in advance.

[HansV]

Try adding the following above End Sub:

Code: Select all

    oCell.Offset(0, 2).Resize(2, 2).FillDown

oCell.Offset(0, 2) is the cell 2 columns to the right of oCell, i.e. the cell in column C.
.Resize(2, 2) extends this to 2 rows (the row of oCell and the newly inserted row below it) and 2 columns (columns C and D).
.FillDown fills the formulas down from the first row to the row below.

[adam]

The following formula

Code: Select all

=VLOOKUP(A16,Reference!A5:C41,MATCH(F2,Reference!A4:C4,0),FALSE)

When copied using the code

Code: Select all

oCell.Offset(0, 2).Resize(2, 2).FillDown

Changes to

Code: Select all

=VLOOKUP(A17,Reference!A6:C42,MATCH(F3,Reference!A5:C5,0),FALSE)

How could it be made as

Code: Select all

=VLOOKUP(A17,Reference!A5:C41,MATCH(F2,Reference!A4:C4,0),FALSE)

Because the Vlookup values aren’t working when the formula changes to

Code: Select all

=VLOOKUP(A17,Reference!A6:C42,MATCH(F3,Reference!A5:C5,0),FALSE)

[HansV]

Change the formula to

=VLOOKUP(A16,Reference!$A$5:$C$41,MATCH($F$2,Reference!$A$4:$C$4,0),FALSE)

The $ characters make the cell references absolute, i.e. they won't change when copied.

[adam]

I'm sorry Hans, I guess either I have provided you wrong information or I could not make the code work. I have attached the workbook for your reference. I wonder where I have gone wrong. I have changed the data in my workbook from colors to fruits.

[HansV]

The line I suggested higher up in this thread was

oCell.Offset(0, 2).Resize(2, 2).FillDown

For some reason, you have changed this to

oCell.Offset(0, 2).Resize(1, 1).FillDown

You should change it back to the line that I proposed.

[adam]

You're right Hans. While trying to change the code I might have mistakenly done that. Thanks for the help. I do really appreciate the help