Solved: Paste Listbox Variable to cells

[slamet Harto]

Hi there,

Can you help me for the following code. I want to paste some variable on the listbox to some cells in a sheet.

Thanks in advance

Option Explicit
'Set reference to the Microsoft ActiveX Data Objects x.x Library!
'code adapted from ken plus (excelguru)
Const glob_sdbPath = "F:\MY\CTC\Db\Master.mdb"
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"

Public Notclick As Boolean

Private Sub userform_initialize()
PopulateComp
End Sub

Private Sub PopulateComp()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rcArray As Variant
Dim sSQL As String

sSQL = "SELECT tblCompanyDB2.CompName, tblCompanyDB2.ContPerson, tblCompanyDB2.Occupation, " & _
"tblCompanyDB2.CompAdd1, tblCompanyDB2.ProdOffer, tblCompanyDB2.UserID " & _
"FROM tblCompanyDB2 ORDER BY tblCompanyDB2.CompName;"
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

cnt.Open glob_sConnect

rst.Open sSQL, cnt
rcArray = rst.GetRows
Notclick = True

With Me.LBoxComp
.Clear
.ColumnCount = 6
.List = Application.Transpose(rcArray)
.ListIndex = -1
.ColumnWidths = "150;100;120;120;35;35"
End With

rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
Notclick = False
End Sub

Private Sub LBoxComp_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

If Notclick = True Then Exit Sub
Sheets("MainAppt").Activate
With ActiveSheet

'error in the following line :banghead: say "Variable Not Define" if I declared but still not working at all

Cells(2, 5).Value = CompName
Cells(3, 5).Value = ContPerson
Cells(4, 5).Value = CompAdd1
End With
End Sub

[Bob Phillips]

Looks to me that CompName, ContPerson, CompAdd1 are not defined.

[Kenneth Hobs]

As Bob said, we don't know what those variables mean.

For starter's, I would recommend this:
Private Sub LBoxComp_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

If Notclick = True Then Exit Sub
With Sheets("MainAppt")
.Range("E2").Value = CompName
.Range("E3").Value = ContPerson
.Range("E4").Value = CompAdd1
End With
End Sub
I think your variables may be in Columns of the listbox so you may need something like this. My listbox is called ListBox1. I pre-filled the List with multi-column data. I put the first 3 columns for the selected index into Sheet2.
Private Sub ListBox1_Click()
Dim sr As Integer, ws As Worksheet
Set ws = Worksheets("Sheet2")
With ListBox1
sr = .ListIndex
If sr < 0 Then Exit Sub
ws.Range("A1").Value = .List(sr, 0)
ws.Range("A2").Value = .List(sr, 1)
ws.Range("A3").Value = .List(sr, 2)
End With
End Sub

[slamet Harto]

Mr Kenneth

Sorry for the late reply and Thanks for the advise.
Here is the update one and it is fine now.

Thank you so much,
Rgds, Harto

Private Sub ListBox1_Click()

Dim sr As Integer, ws As Worksheet
Set ws = Worksheets("MainAppt")
With Me

If ListBox1.ListIndex >= 0 Then
ws.Range("E2").Value = .ListBox1.List(Me.ListBox1.ListIndex, 0)
ws.Range("E4").Value = .ListBox1.List(Me.ListBox1.ListIndex, 1)
ws.Range("E6").Value = .ListBox1.List(Me.ListBox1.ListIndex, 2)
ws.Range("E8").Value = .ListBox1.List(Me.ListBox1.ListIndex, 3)
ws.Range("E10").Value = .ListBox1.List(Me.ListBox1.ListIndex, 4)
ws.Range("E11").Value = .ListBox1.List(Me.ListBox1.ListIndex, 5)

End If
End With
End Sub