basically module onboarding asking path of tracker want update. updating details in sheet1 of tracker. setting values of fields in userform 'onboardingform' blank(so values entered last time form not visible when opening form time. opening form 'onboardingform' , entering values in subsequent fields. have put check button in userform 'onboardingform' invisible front end user. in tracker there sheet named 'project tracks' has information of current projects once submit button clicked control go tracker's 'project tracks' sheet. validate track entered in userform 'onboardingform' tracks present in tracker's 'project tracks' sheet. once found other details against particular track fetched tracker's sheet1(this have done not have enter values manually userform 'onboardingform' form looks simple). there no chances of track not matching.
now 1 command button new track has been put in current userform 'onboardingform'. once clicked take control userform2 'projecttracksform'.this put if adding new track, form takes detail , enters in tracker's 'project tracks' sheet.
question 1> current userform's track button combo box. how add values in dropdown tracker's 'project tracker' sheet dropdown.
question 2> once add new track in userform2 'projecttracksform',submit , when come current userform 'onboardingform' added track should shown in dropdown of track combo box. please find below piece of code.
this module onboarding
public sub onboarding() on error goto errorhandler dim owb object dim ran range strtalenttrackerpath = shtracker.cells(2, 2).value 'default form values null onboardingform .combtrackofwork.value = "" .txtfirstname.text = "" .txtlastname.text = "" .combrescat.value = "" .combbfte.value = "" .comblevel.value = "" .combloctype = "" .txtaccessinfo.text = "" end onboardingform.show setfocus.combtrackofwork onboardingform 'details entered in form' strtow = trim$(.combtrackofwork.value) strfn = trim$(.txtfirstname.text) strln = trim$(.txtlastname.text) strrescat = trim$(.combrescat.value) strbilfte = trim$(.combbfte.value) strlevel = trim$(.comblevel.value) strloctype = (.combloctype.value) straccessinfo = (.txtaccessinfo.text) end if onboardingform.chkokbuttonclick = true set oexcel = new excel.application strmyfolder = strtalenttrackerpath set owb = oexcel.workbooks.open(strmyfolder) introwcount = owb.sheets(1).usedrange.rows.count owb.sheets(1) owb.sheets("project tracks") inttrackrowcount = .usedrange.rows.count intcurrentrow = 1 inttrackrowcount if .cells(intcurrentrow, 1) = strtow owb.sheets(1).cells(introwcount + 1, onboardingformcolumn.coltrackofwork) _ = .cells(intcurrentrow, projecttrackscolumn.coltrack) owb.sheets(1).cells(introwcount + 1, onboardingformcolumn.colbpo) = .cells _ (intcurrentrow, projecttrackscolumn.colbpo) owb.sheets(1).cells(introwcount + 1, onboardingformcolumn.colcostcenter) _ = .cells(intcurrentrow, projecttrackscolumn.colcostcenter) owb.sheets(1).cells(introwcount + 1, onboardingformcolumn.colgroup) _ = .cells(intcurrentrow, projecttrackscolumn.colgroup) exit end if next end end .cells(introwcount + 1, onboardingformcolumn.coltrackofwork) = strtow .cells(introwcount + 1, onboardingformcolumn.colfirstname) = strfn .cells(introwcount + 1, onboardingformcolumn.collastname) = strln .cells(introwcount + 1, onboardingformcolumn.colresourcecategory) = strrescat .cells(introwcount + 1, onboardingformcolumn.colbilledfte) = strbilfte .cells(introwcount + 1, onboardingformcolumn.collevel) = strlevel .cells(introwcount + 1, onboardingformcolumn.collocationtype) = strloctype .cells(introwcount + 1, onboardingformcolumn.colaccessinformation) = straccessinfo owb.close true set owb = nothing set oexcel = nothing else exit sub end if exit sub errorhandler: if owb nothing else owb.close false end if if oexcel nothing else set oexcel = nothing end if msgbox "unhandled error. please report" & vbcrlf & "error description: " & _ err.description, vbexclamation end sub this cancel button of onboarding form
private sub cmdbtn_cancel_click() onboardingform.hide msgbox ("no data entered") end sub this onboardingform submit button
private sub cmdbtn_submit_click() if trim(onboardingform.combtrackofwork.value) = "" onboardingform.combtow.setfocus msgbox ("track of work cannot blank") exit sub end if if trim(onboardingform.txtfirstname.value) = "" onboardingform.txtfn.setfocus msgbox ("first name cannot blank") exit sub end if if trim(onboardingform.txtlastname.value) = "" onboardingform.txtln.setfocus msgbox ("last name cannot blank") exit sub end if end sub module project tracks
public sub prjcttracks() on error goto errorhandler dim owb object strtalenttrackerpath = shtracker.cells(2, 2).value projecttracksform .txttow = "" .txtbpo = "" .txtcoce = "" .txtsow = "" .txtgroup = "" end projecttracksform.show projecttracksform strtow = trim$(.txttow.text) strbpo = trim$(.txtbpo.text) strcoce = trim$(.txtcoce.text) strsow = trim$(.txtsow.value) strgroup = trim$(.txtgroup.value) end projecttracksform.hide if projecttracksform.chkbtn_okclick = true set oexcel = new excel.application strmyfolder = strtalenttrackerpath set owb = oexcel.workbooks.open(strmyfolder) owb.sheets("project tracks") intusedrowcount = .usedrange.rows.count .cells(intusedrowcount + 1, trackscolumn.coltrack) = strtow .cells(intusedrowcount + 1, trackscolumn.colbpo) = strbpo .cells(intusedrowcount + 1, trackscolumn.colcostcenter) = strcoce .cells(intusedrowcount + 1, trackscolumn.colsow) = strsow .cells(intusedrowcount + 1, trackscolumn.colgroup) = strgroup end owb.close true set owb = nothing set oexcel = nothing else exit sub end if exit sub errorhandler: if owb nothing else owb.close false end if if oexcel nothing else set oexcel = nothing end if msgbox "unhandled error. please report" & vbcrlf & "error description: " & _ err.description, vbexclamation end sub
question 1> current userform's track button combo box. how add values in dropdown tracker's 'project tracker' sheet dropdown.
i calling combobox "combobox1" in example
the range place in combobox this...
the code populate combobox in userform module.
private sub userform_initialize() dim lstrw long dim rng range dim ws worksheet set ws = sheets("project tracker") ws lstrw = .cells(.rows.count, 1).end(xlup).row set rng = .range("a2:a" & lstrw) end combobox1.list = rng.value end sub question 2> once add new track in userform2 'projecttracksform',submit , when come current userform 'onboardingform' added track should shown in dropdown of track combo box
when activate userform again, can clear combobox , repopulate new list.
private sub userform_activate() dim lstrw long dim rng range dim ws worksheet set ws = sheets("project tracker") ws lstrw = .cells(.rows.count, 1).end(xlup).row set rng = .range("a2:a" & lstrw) end combobox1.clear combobox1.list = rng.value end sub i assume somewhere have code add new item list in sheet("project tracker"),
something like:
private sub commandbutton1_click() 'this in other userform 'add item first blank cell in column sheets("project tracker") dim sh worksheet dim lstrws long set sh = sheets("project tracker") sh lstrws = .cells(.rows.count, "a").end(xlup).row + 1 .cells(lstrws, 1) = "somethingnew" 'whatever adding list end end sub the code add new list in worksheet.
when show form again, new item in combobox.
you can either use button, combobox event, textbox event, add item new list.
Comments
Post a Comment