excel - how can i bring information from one sheet to my userform and transfer it to another sheet? -
i'm new coding vba, got information online , book. im trying ask user part# after userfrom bring information in sheet 2 information testbox2 , testbox3. user input quantity , date entered , due date initialize. once user form complete user click next part , boxes clear , inserted data worksheet. problem cant not working , dont know did wrong. in bottom added code can please me.
private sub commandbutton1_click() dim emptyrow long 'make sheet1 active sheet1.activate 'determine emptyrow emptyrow = worksheetfunction.counta(range("a:a")) + 1 dim ws worksheet dim lastrow long, lrow long, trow long dim tempvalue string dim targetsheet string dim templist(1 4) string dim long dim match boolean match = false 'set targetsheet , clear previous contents targetsheet = "sheet1" trow = 15 lastrow = sheets(sheet2).range("a3" & rows.count).end(xlup).row 'set array of strings, based on index matching column search each templist(1) = textbox1.text 'column "a" (1) templist(2) = textbox2.text 'column "b" (2) templist(3) = textbox3.text 'column "c" (3) 'search through each worksheet each ws in worksheets if ws.name <> sheet2 'get last row of sheet lastrow = ws.range("a" & rows.count).end(xlup).row 'search through sheet lrow = 2 lastrow 'using array of values textboxes, 'each column number matches index of array. 'only testing array values have text in them, 'if don't match loop broken , returns main search. = 1 4 if templist(i) <> "" if ws.cells(lrow, i).text = templist(i) match = true else match = false exit 'if of values false, exit loop end if end if next 'if there match, copy data searched ws targetsheet if match = true 'get first empty row on target sheet lcol = 1 8 sheets(sheet2).cells(trow, lcol).value = ws.cells(lrow, lcol).value next lcol trow = trow + 1 end if next lrow end if next ws end sub 'values need enter in order move on if textbox1.value = "" cancel = 1 msgbox "please enter part #" textbox1.setfocus exit sub end if if textbox4.value = "" cancel = 1 msgbox "please enter part quantity" textbox4.setfocus exit sub end if '-------- 'transfer data sheet1 cells(emptyrow, 1).value = textbox1.value cells(emptyrow, 2).value = textbox2.value cells(emptyrow, 3).value = textbox3.value cells(emptyrow, 4).value = textbox4.value cells(emptyrow, 5).value = textbox5.value cells(emptyrow, 6).value = dtpicker1.value textbox1.value = null textbox2.value = null textbox3.value = null textbox4.value = null end sub buttom code
private sub commandbutton2_click() 'close userform. unload me end sub 'input needs numbers
private sub textbox4_change() if typename(me.activecontrol) = "textbox" me.activecontrol if not isnumeric(.value) , .value <> vbnullstring msgbox "sorry, numbers allowed" .value = vbnullstring end if end end if end sub initialize
private sub userform_initialize() textbox5.value = format(date, "mm/dd/yyyy") dtpicker1.value = date 'formulas sheet 1 application.screenupdating = false cells(2, 11).autofill destination:=range(cells(2, 11), cells(500, 11)), type:=pastespecial cells(2, 12).autofill destination:=range(cells(2, 12), cells(500, 12)), type:=pastespecial cells(2, 13).autofill destination:=range(cells(2, 13), cells(500, 13)), type:=pastespecial application.screenupdating = true 'text align in sheet1 dim wks worksheet each wks in worksheets activesheet.cells.verticalalignment = xlcenter activesheet.cells.horizontalalignment = xlcenter next wks end sub
Comments
Post a Comment