excel vba - Type mismatch Error in VBA when creating ActiveX control drop down list -
i got 3 activex combo boxes in sheet1. used code in this workbook populate first combo box list. have created function next set of combo box cascading values. below function:
function cascadechild(targetchild oleobject) dim myconnection connection dim cmd adodb.command dim myrecordset recordset dim myworkbook string dim strsql string set myconnection = new adodb.connection set cmd = new adodb.command set myrecordset = new adodb.recordset 'identify workbook referencing myworkbook = application.thisworkbook.fullname 'open connection workbook myconnection.open "--" select case targetchild.name case = "directorate" strsql = "select distinct directorate [tgtfield] dbtable division = '" & sheet1.division.value & "' or 'all' = '" & sheet1.division.value & "'" case = "area" strsql = "select distinct area [tgtfield] dbtable ( division = '" & sheet1.division.value & "' or 'all' = '" & sheet1.division.value & "') , (directorate = '" & sheet1.directorate.value & "' or 'all' = '" & sheet1.directorate.value & "')" end select 'load query recordset myrecordset.open strsql, myconnection, adopenstatic 'fill target child listbox targetchild.object .clear .additem myrecordset![tgtfield] myrecordset.movenext loop until myrecordset.eof .value = .list(0) '<<automatically selects first value in listbox end 'clean myconnection.close set myrecordset = nothing set myconnection = nothing end function then have written code in sheet1 in vba:
private sub division_change() call cascadechild(activesheet.oleobjects(sheet1.directorate.name)) end sub private sub directorate_change() call cascadechild(activesheet.oleobjects(sheet1.area.name)) end sub first combo box giving values, when select value activex control, error msg populating with
runtime error, type mismatch
the error coming debug mode here .additem myrecordset![tgtfield] help
try
.additem myrecordset.fields(0).value
Comments
Post a Comment