slamet Harto
03-17-2009, 05:00 AM
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
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