loops - Looping to get data row by row to the userform rows -
i using vbs excel 2010, trying write loop transfer cells in 17 rows, 5 columns ws userform has 17 rows, 5 columns of textboxes.
each row of textboxes indexed row in. eg. txt.name1, txt.city1...txt,county1. next row of textboxes txt.name2, txt.city2...txt,county2...
how can loop through each row , transfer awaiting row of textboxes out writing code every row?
cominterm correct, “you need put textbox controls control array”. unfortunately, don’t find suggested links particularly helpful.
i have not needed access form controls number many years rusty. tackled answer in way have done myself: isolate functionality don’t know , write macros explore functionality. remembered controls collection not how access type of control looked up.
the details of every control on form recorded in controls collection details of every worksheet recorded in worksheets collection. if txtname1 6th control can access properties controls(5)since controls numbered zero. example, instead of txtname1.text = "xxxxxx", can write controls(5) .text = "xxxxxx".
i created user form , filled 1 or 2 of every type of control. added 3 rows in format want give:
i created module containing:
option explicit sub test1() load userform1 userform1.show vbmodal end sub i wrote following code within form:
private type typectrl height single left single name string parent string top single typename string width single end type dim ctrldtl() typectrl private sub cmdexit_click() unload me end sub private sub userform_initialize() dim inxctrl long dim namelenmax long dim parentlenmax long dim typenamelenmax long redim ctrldtl(0 controls.count - 1) inxctrl = 0 controls.count - 1 ctrldtl(inxctrl).name = controls(inxctrl).name ctrldtl(inxctrl).top = controls(inxctrl).top ctrldtl(inxctrl).left = controls(inxctrl).left ctrldtl(inxctrl).width = controls(inxctrl).width ctrldtl(inxctrl).height = controls(inxctrl).height ctrldtl(inxctrl).parent = controls(inxctrl).parent.name ctrldtl(inxctrl).typename = typename(controls(inxctrl)) if namelenmax < len(ctrldtl(inxctrl).name) namelenmax = len(ctrldtl(inxctrl).name) end if if parentlenmax < len(ctrldtl(inxctrl).parent) parentlenmax = len(ctrldtl(inxctrl).parent) end if if typenamelenmax < len(ctrldtl(inxctrl).typename) typenamelenmax = len(ctrldtl(inxctrl).typename) end if next if namelenmax < len("name") namelenmax = len("name") end if if parentlenmax < len("parent") parentlenmax = len("parent") end if debug.print "inx name" & space(namelenmax - 2) & _ " top left height width parent" & _ space(parentlenmax - 6) & " type name" inxctrl = 0 ubound(ctrldtl) ctrldtl(inxctrl) debug.print right(" " & inxctrl, 3) & " " & _ left(.name & space(namelenmax), namelenmax) & " " & _ right(space(8) & format(.top, "#,###.00"), 8) & " " & _ right(space(8) & format(.left, "#,###.00"), 8) & " " & _ right(space(8) & format(.height, "#,###.00"), 8) & " " & _ right(space(8) & format(.width, "#,###.00"), 8) & " " & _ left(.parent & space(parentlenmax), parentlenmax) & " " & _ .typename end next end sub the first block of code reads down controls collection, extracts values of selected properties , stores in array of type typectrl have specified hold selected properties. second block outputs values array immediate windows give:
inx name top left height width parent type name 0 label1 12.00 24.00 18.00 72.00 userform1 label 1 label2 12.00 114.00 18.00 72.00 userform1 label 2 textbox1 12.00 210.00 18.00 72.00 userform1 textbox 3 textbox2 12.00 294.00 18.00 72.00 userform1 textbox 4 combobox1 12.00 390.00 18.00 72.00 userform1 combobox 5 combobox2 48.15 24.00 18.00 72.00 userform1 combobox 6 listbox1 42.00 120.00 72.00 72.00 userform1 listbox 7 checkbox1 48.00 294.00 18.00 108.00 userform1 checkbox 8 optionbutton1 138.00 12.00 18.00 108.00 userform1 optionbutton 9 listbox2 42.00 204.00 72.00 72.00 userform1 listbox 10 checkbox2 72.00 294.00 18.00 108.00 userform1 checkbox 11 optionbutton2 162.00 12.00 18.00 108.00 userform1 optionbutton 12 togglebutton1 132.00 144.00 40.00 36.00 userform1 togglebutton 13 togglebutton2 132.00 192.00 40.00 36.00 userform1 togglebutton 14 frame1 120.00 246.00 42.00 66.00 userform1 frame 15 frame2 120.00 324.00 54.00 72.00 userform1 frame 16 label3 18.00 12.00 18.00 48.00 frame2 label 17 cmdexit 126.00 414.00 24.00 72.00 userform1 commandbutton 18 commandbutton2 162.00 414.00 24.00 72.00 userform1 commandbutton 19 tabstrip1 192.00 18.00 54.00 90.00 userform1 tabstrip 20 tabstrip2 198.00 120.00 48.00 66.00 userform1 tabstrip 21 multipage1 198.00 186.00 66.00 102.00 userform1 multipage 22 multipage2 204.00 306.00 60.00 72.00 userform1 multipage 23 scrollbar1 204.00 390.00 63.80 12.75 userform1 scrollbar 24 spinbutton1 204.00 462.00 25.50 12.75 userform1 spinbutton 25 image1 270.00 18.00 72.00 72.00 userform1 image 26 spinbutton2 204.00 486.00 25.50 12.75 userform1 spinbutton 27 scrollbar2 204.00 414.00 63.80 12.75 userform1 scrollbar 28 txtname1 282.00 108.00 18.00 42.00 userform1 textbox 29 txtstreet1 282.00 156.00 18.00 42.00 userform1 textbox 30 txttown1 282.00 204.00 18.00 42.00 userform1 textbox 31 txtcounty1 282.00 252.00 18.00 42.00 userform1 textbox 32 txtpostcode1 282.00 300.00 18.00 42.00 userform1 textbox 33 txtname2 306.00 108.00 18.00 42.00 userform1 textbox 34 txtstreet2 306.00 156.00 18.00 42.00 userform1 textbox 35 txttown2 306.00 204.00 18.00 42.00 userform1 textbox 36 txtcounty2 306.00 252.00 18.00 42.00 userform1 textbox 37 txtpostcode2 306.00 300.00 18.00 42.00 userform1 textbox 38 txtname3 330.00 108.00 18.00 42.00 userform1 textbox 39 txtstreet3 330.00 156.00 18.00 42.00 userform1 textbox 40 txttown3 330.00 204.00 18.00 42.00 userform1 textbox 41 txtcounty3 330.00 252.00 18.00 42.00 userform1 textbox 42 txtpostcode3 330.00 300.00 18.00 42.00 userform1 textbox i believe need access name coding refreshed memory of possible. sort of macro have written myself when first needed know control collection , recommend approach. have selected properties exist every type of control. however, checking type, have accessed properties restricted particular types.
having refreshed memory, ready code macro matches requirement.
you not name 5 of columns made 5 names of own. have defined 3 rows enough demonstrate approach. can see them @ bottom of list above.
i create worksheet “data” , placed data within it:
i not know if data in fixed or movable demonstration have made fixed , have defined location constants. can replace values of constants or replace constants variables necessary.
i discarded original code form , replaced with:
option explicit const wshtname string = "data" const rowdatatop long = 3 const coldataleft long = 2 const rowdatabot long = 5 const coldataright long = 6 dim ctrlforaddresselement(1 coldataright - coldataleft + 1, _ 1 rowdatabot - rowdatatop + 1) long private sub cmdexit_click() unload me end sub private sub userform_initialize() dim coldatacrnt long dim colformcrnt long dim inxctrl long dim rowdatacrnt long dim rowformcrnt long redim ctrldtl(0 controls.count - 1) ' store index number each control of address block inxctrl = 0 controls.count - 1 if left(controls(inxctrl).name, 7) = "txtname" rowformcrnt = val(mid(controls(inxctrl).name, 8)) ctrlforaddresselement(1, rowformcrnt) = inxctrl elseif left(controls(inxctrl).name, 9) = "txtstreet" rowformcrnt = val(mid(controls(inxctrl).name, 10)) ctrlforaddresselement(2, rowformcrnt) = inxctrl elseif left(controls(inxctrl).name, 7) = "txttown" rowformcrnt = val(mid(controls(inxctrl).name, 8)) ctrlforaddresselement(3, rowformcrnt) = inxctrl elseif left(controls(inxctrl).name, 9) = "txtcounty" rowformcrnt = val(mid(controls(inxctrl).name, 10)) ctrlforaddresselement(4, rowformcrnt) = inxctrl elseif left(controls(inxctrl).name, 11) = "txtpostcode" rowformcrnt = val(mid(controls(inxctrl).name, 12)) ctrlforaddresselement(5, rowformcrnt) = inxctrl end if next ' move data worksheet form worksheets(wshtname) rowdatacrnt = rowdatatop rowdatabot rowformcrnt = rowdatacrnt - rowdatatop + 1 coldatacrnt = coldataleft coldataright colformcrnt = coldatacrnt - coldataleft + 1 inxctrl = ctrlforaddresselement(colformcrnt, rowformcrnt) controls(inxctrl).text = .cells(rowdatacrnt, coldatacrnt).value next next end end sub i have global array ctrlforaddresselement size necessary handle range defined constants.
the first block of code loads array indices controls collection 15 text boxes.
the second block of code uses array move data worksheet form give:
the second block of code should readily adaptable requirement.
Comments
Post a Comment