Populate Listbox & keep updating with worksheet updates.

[adam]

The following code inserts a new row below the text “BLUE”when the list box in the Userform is double clicked. Can this code be modified so that when the list box is double clicked it cuts all the rows and columns that is below the text ”BLUE” and paste them one row after the text “BLUE” and inserts the text from the list box to the row below the text “ BLUE”.

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim oCell As Range
    ' Search for the word 'BLUE'
    Set oCell = Worksheets("Sales").Range("A:A").Find(What:="Apple", LookAt:=xlWhole)
    If oCell Is Nothing Then
        MsgBox "The word 'Apple' 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 A
    oCell.Offset(1, 0) = Me.ListBox1.Column(1)
End Sub

[HansV]

I've never seen blue apples, but cutting all rows below a specific word and pasting them one row downwards has exactly the same effect as inserting an empty row below that word. So the code already does what you ask.

[adam]

Hans, what you have said is absolutely correct. By the way, apple Mac products have the blue apple logo.

[adam]

cutting all rows below a specific word and pasting them one row downwards has exactly the same effect as inserting an empty row below that word

how could the following code be changed so that instead of inserting a new row; when the listbox is double clicked, it copies the text from the listbox to the worksheet's row below the text mentioned in the code.

Note: this is because the rows contains formulas.

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim oCell As Range
    ' Search for the word 'BLUE'
    Set oCell = Worksheets("Sales").Range("A:A").Find(What:="Apple", LookAt:=xlWhole)
    If oCell Is Nothing Then
        MsgBox "The word 'Apple' 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 A
    oCell.Offset(1, 0) = Me.ListBox1.Column(1)
End Sub

[HansV]

Remove the line

oCell.Offset(1, 0).EntireRow.Insert

[adam]

Thanks for the reply Hans.

[adam]

I have loaded the worksheet named “Products” from the workbook “Tinas Bakery” into the form containing the listbox1 so that when the user writes the product name in the textbox1, the name matching the product gets highlighted from the listbox1. And when the user double clicks the highlighted product, the product code gets copied to the column A starting from the cell A16 of the “NewMemo” worksheet.

After modifying by removing the line

oCell.Offset(1, 0).EntireRow.Insert



the product’s code gets copied below the text "Code"when I double click the product name form the listbox.

But when I double click the listbox with another product it gets copied to the same row (A16) instead of (A17). Moreover, when I protect the sheet "NewMemo”,I get a runtime error message saying that the cell or chart that you are trying to change is protected and therefore read only.

And the following line gets highlighted in yellow

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


How could I prevent this?

[HansV]

Originally, the code inserted a new row. You then asked how to modify the code NOT to insert a row, so I proposed a change. If that's not what you want either, you'll have to explain very precisely and exactly what you want.

There are two ways around the problem with protected worksheets:

1) You can unprotect the sheet, perform an action, then protect the sheet again:

Code: Select all

Worksheets("NewMemo"),Unprotect
...
oCell.Offset(0, 2).Resize(2, 2).FillDown ' you had Resize(1, 1) but that won't do anything
...
Worksheets("NewMemo").Protect

- or -

2) Protect the worksheet using code, specifying UserInterfaceOnly:=True. The sheet will then be protected for the end user, but not for VBA.
Excel doesn't store this setting, so you have to apply it each time the workbook is opened. You can use the Workbook_Open event procedure in the ThisWorkbook module for this:

Code: Select all

Private Sub Workbook_Open()
  Worksheets("NewMemo").Protect UserInterfaceOnly:=True
End Sub

[adam]

you'll have to explain very precisely and exactly what you want.

What I want in detail is that after each double click in the list box of the user form, the highlighted row in the list box to get copied into the row below the text “Code” in column “A” of the sheet “NewMemo”, which is the cell A16.

And after that, when I double click another row from the listbox in the userform. That row to get copied to the row A17, A18, A19 and so on.

Note: column 1 from the listbox to get copied into column A of the worksheet NewMemo

I hope I have made my question clear

[HansV]

Try this:

Code: Select all

Dim r As Long
With Worksheets("NewMemo")
  r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
  .Range("A" & r) = Me.ListBox1.Column(1)
End With

[adam]

Where should I place the above code? Should it be placed below the line?
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

[HansV]

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim r As Long
  With Worksheets("NewMemo")
    r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & r) = Me.ListBox1.Column(1)
  End With
End Sub

[adam]

When I double click the highlighted row from the listbox the second column of the list box gets copied into the cell A37 of the Sheet "NewMemo". And when I double click another highlighted row it gets copied into A38. How can this be made to start from cell A16 with column 1 from the listbox

[HansV]

The code looks for the last non-blank cell in column A of the NewMemo sheet, and fills the cell below that. If you have content in cell A36, how should we determine where to start?

To use the first column of the list box, use Me.ListBox1.Column(0)

[adam]

The code looks for the last non-blank cell in column A of the NewMemo sheet, and fills the cell below that. If you have content in cell A36, how should we determine where to start?

Can the code be adjusted so that it looks for the empty cell after the text "Code" in column A15 & starts to fill from the cell A16 onwards?

[HansV]

Yes, but if you already have content in cell A36 you'll run into problems at some point...

[adam]

double clicking the rows in listbox will fill upto A35 only. A36 contains the word "Total", which means it would give the total price for the products from A16 to A35.

So I guess this might save me if the above code could be adjusted.

[HansV]

Try

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim r As Long
  With Worksheets("NewMemo")
    If Range("A16") = "" Then
      r = 16
    Else
      r = .Range("A15").End(xlDown).Row + 1
    End If
    .Range("A" & r) = Me.ListBox1.Column(0)
  End With
End Sub

[adam]

Thanks Hans. This was what I was asking & searching for. Finally you had made it happen. Once again thanks for the help & I do really appreciate it.