excel - Add Check Boxes to a userform with multiple sections that will input a number located on Sheet -
i new vba, , working on userform, created else. userform has 4 areas(cost code1, cost code 2, exc...) input information(cost code, truck rent, regular hours , overtime hours) specific columns on worksheet. right there text boxes @ top of form enters employee name, job number , date of information.
the new project has 2 job numbers, instead of using job number text box @ top, need add option buttons, or check boxes (whichever easier) choose between 2 different job numbers 4 groups of information. allowed select 1 job number per area on userform. job numbers located on second tab called employees in cells h1 , k1. need job number entered in column number 4. code check boxes, or option buttons, , enter in original code? appreciate help.
private sub cbok_click() dim nextrow long 'variable cycling through cell input dim long dim userdate date = 1 set eesheet = activeworkbook.sheets("employees") set tlisheet = activeworkbook.sheets("worksheet") 'activate worksheet tab sheets("worksheet").activate 'set autocalc off speed things up. application.calculation = xlcalculationmanual 'error handling, go bad. on error goto bad userdate = tbdate.value 'transfer data rows = 1 6 nextrow = application.worksheetfunction.counta(range("a:a")) + 1 if me.controls("tbcc" & & "reghrs").value <> "" cells(nextrow, 1) = tbdate.value cells(nextrow, 2) = cmbemployeename.value cells(nextrow, 3) = application.worksheetfunction.vlookup(cmbemployeename.value, eesheet.range("a:b"), 2, false) cells(nextrow, 4) = tbjobnumber.value cells(nextrow, 5) = me.controls("tbjobextra" & i).value cells(nextrow, 6) = me.controls("cmbcc" & i).value cells(nextrow, 7) = application.worksheetfunction.vlookup(cmbemployeename.value, eesheet.range("a:c"), 3, false) cells(nextrow, 8) = application.worksheetfunction.vlookup(cmbemployeename.value, eesheet.range("a:d"), 4, false) cells(nextrow, 10) = application.worksheetfunction.vlookup(cmbemployeename.value, eesheet.range("a:e"), 5, false) if eetype = 1 cells(nextrow, 9) = "soh" else cells(nextrow, 9) = "reg" end if cells(nextrow, 10) = me.controls("tbcc" & & "reghrs").value end if if me.controls("tbcc" & & "othrs").text <> "" nextrow = application.worksheetfunction.counta(range("a:a")) + 1 cells(nextrow, 1) = tbdate.value cells(nextrow, 2) = cmbemployeename.value cells(nextrow, 3) = application.worksheetfunction.vlookup(cmbemployeename.value, eesheet.range("a:b"), 2, false) cells(nextrow, 4) = tbjobnumber.value cells(nextrow, 5) = me.controls("tbjobextra" & i).value cells(nextrow, 6) = me.controls("cmbcc" & i).value cells(nextrow, 7) = application.worksheetfunction.vlookup(cmbemployeename.value, eesheet.range("a:c"), 3, false) cells(nextrow, 8) = application.worksheetfunction.vlookup(cmbemployeename.value, eesheet.range("a:d"), 4, false) cells(nextrow, 9) = "ovt" cells(nextrow, 10) = me.controls("tbcc" & & "othrs").value end if if + 1 = 7 exit if me.controls("cmbcc" & + 1).value = "" exit next bad: if err.number = 1004 msgbox "this ee not exist or typed name incorrectly. check ee name spelling or enter ee employees tab." nextrow = application.worksheetfunction.counta(range("a:a")) rows(nextrow).delete eepayrollentry.hide sheets("employees").activate end if = 1 = 1 6 cmbemployeename.value = "" me.controls("tbjobextra" & i).text = "" me.controls("cmbcc" & i).text = "" me.controls("tbcc" & & "reghrs").text = "" me.controls("tbcc" & & "othrs").text = "" next eepayrollentry.lbtotalhours.caption = 0 end sub
Comments
Post a Comment