excel - On Click Command Button Macro -
i'm trying create macro command button when clicked, job number row , file job. if not exist want copy template , save new name, otherwise open file.
however, cannot seem work out how hold of information command button calls macro. have far:
public function shapeexists(onsheet object, name string) boolean on error goto errshapeexists if not onsheet.shapes(name) nothing shapeexists = true end if errshapeexists: exit function end function private sub worksheet_change(byval target excel.range) dim buttonname string buttonname = (target.row - 1) if not shapeexists(activesheet, buttonname) if range("o" & target.row).value = "" , target.column <= 14 , target.row > 1 activesheet.buttons.add(910.5, range("o" & target.row).top, 80, 20).select selection.name = buttonname selection.onaction = "sheet1.jobbutton" activesheet.shapes(buttonname).select selection.characters.text = "open job" end if end if end sub private sub jobbutton() dim newtext string activesheet.shapes(application.caller).select if range("n" & selection.topleftcell.row).value <> "" newtext = "job " & range("n" & selection.topleftcell.row).value dim checkfilename string dim check string check = "n" & selection.topleftcell.row checkfilename = newtext & ".xlsm" if dir(checkfilename) <> "" workbooks.open (newtext) else dim srcbook workbook set srcbook = thisworkbook dim newbook workbook newbook = workbooks.open("job template.xlsm") srcbook.worksheets(1).range("d" & selection.topleftcell.row).copy newbook.worksheets(2).range("b15").pastespecial newbook .title = newtext .subject = newtext .saveas filename:=newtext end end if else errmsg: msgbox ("job should have number."), , "no job number" end if end sub as can see trying activesheet.shapes(application.caller).select, causing "run-time error '13': type mismatch".
any appreciated, thank you!
right-click button --> view code --> put jobbutton code here
Comments
Post a Comment