Pasting data retrieved from a list box 1

[bagnall]

Help for an absolute beginner please.
WorkBook A has a user form with a single selection ListBox with seven columns filled from a range in WorkBook B. I need the code to insert the selected cells into a row in Workbook A. starting at the active cell.

 

[chandlm]

Hi Bagnall,

If you want to email me the file I will have a look at it and get back to you.

Cheers,

Matt

 

[rmikesmith]

Hi bagnall

The following code should be placed in the event handler for a button on your Userform named CommandButton1. The user will click this button to copy the information from the ListBox to the ActiveCell in Workbook A:

Private Sub CommandButton1_Click()
Dim i As Integer

  With Me.ListBox1
    If .ListIndex = -1 Then Exit Sub
    For i = 0 To .ColumnCount - 1
      ActiveCell.Offset(0, i).Value = .List(.ListIndex, i)
    Next i
  End With
End Sub

If you need further help implementing this, don't hesitate to ask.

Regards,
Mike

 

[bagnall]

Dear Mike,
Thank you so much for the code, I wonder if I could prevail on your good nature even further.
I would like to put a second command button that would insert, rather than paste the information, and additionally two of the seven columns (D & E ) contain formulae, but they are returned as a result.
Rgds and a star
Simon Bagnall (simon@stagestruck.com)

 

[Nikita6003]

Bagnall - sorry for butting in, but perhaps this'll help

Stealing a bit of Mike's code, use this to insert the ListBox selection:

Private Sub CommandButton2_Click()
Dim i As Integer

  With Me.ListBox1
    If .ListIndex = -1 Then Exit Sub
    'Insert a row above the active cell      
    ActiveCell.EntireRow.Insert

    For i = 0 To .ColumnCount - 1
      'Report selection in the new row above the active cell
      ActiveCell.Offset(-1, i).Value = .List(.ListIndex, i)
    Next i
  End With
End Sub

As for the formulae, what are they? If you want to insert not the 4th and 5th columns of the list box selection but a formula, build a check in your loop to replace column with formula:

    For i = 0 To .ColumnCount - 1
      'Check to see whether to insert a formula or the column contents:
      Select Case i
      Case 3      'You've reached column D
        'Enter formula for column D here: this uses A1 notation for the cell addresses
        ActiveCell.Offset(-1, i).Formula

        'Enter formula for column D here: this uses R1C1 notation for the cell adresses
        ActiveCell.Offset(-1, i).FormulaR1C1
      Case 4      'Column E
         'Same as for Case 3
      Case Else
        'Use listbox column selection 
        ActiveCell.Offset(-1, i).Value = .List(.ListIndex, i)
    Next i

HTH

Cheers!
Nikki

 

[bagnall]

Thank you for all your help.
Using Code from Rmikesmith I now have a UserForm which does what I needed, my only problem is that the I get the value above the one that I pick, and I would like to always make the active cell to be in column A.

Here is the code I am using.



Sub ItemForm()
frmItem.ListBox1.RowSource = "dat.xls!snd"
frmItem.Show
End Sub



Sub CopyData(ByVal Insert As Boolean)
Dim WkbB As Workbook
Dim Wks As Worksheet

Dim i As Integer
Dim OneCell As Range
Dim DataRow As Long


Set WkbB = Workbooks("Dat.xls&quot
Set Wks = WkbB.Worksheets("Sheet1&quot

If Insert Then ActiveCell.EntireRow.Insert
Set OneCell = ActiveCell

With frmItem.ListBox1
If .ListIndex = -1 Then
MsgBox "You must first select an Item!"
Exit Sub
End If
DataRow = .ListIndex



End With

With Wks
For i = 1 To 4
If .Cells(DataRow, i).HasFormula Then
OneCell.Offset(0, i - 1).Value = .Cells(DataRow, i).Formula
Else
OneCell.Offset(0, i - 1).Value = .Cells(DataRow, i).Value
End If
Next i
End With

End Sub

PS I am wading through John Walkenbach's Excel 2000 bible, and will hopefully post less feeble requests for help if I reach the end.

 

[rmikesmith]

Simon,

I noticed you altered some of the code I sent with the demo workbook. No problem, except that you set the row number of the data to copy to the list index with

DataRow = .ListIndex

. ListBox indexes are zero-based while Excel's rows and columns are one-based. Hence, you are getting a data row one less than expected. In my original code, I stored the row number for a given ListBox entry in a hidden column of the ListBox. As a quick fix change the DataRow assignment to read:

DataRow = .ListIndex + 1

Regards,
Mike

 

[bagnall]

Mike,
Thanks again, I thought I had tried every possible syntax with List, ListBox and ListIndex.
It has re-affirmed my faith in human nature to find an electric community of people generous enough to help even the terminally bewildered.
Rgds
Simon