Updating Cell values via Userform textbox (issues)

[DWRgt2885]

Hey All,

I have a user form for tracking orders i need to follow up on. When the status of the order changes I update the sheet via the same form I used to submit the data initially. I have an "update" button to do this. The problem i'm having is, if the information is being offset by 1 based on the active cell. The code below has the column letter at the end of each line of code.

ActiveCell.Row.Value = txtDateSUB.Value 'a
ActiveCell.Offset(0, 1) = txtvendor.Value 'b
ActiveCell.Offset(0, 2) = txtCust.Value 'c
ActiveCell.Offset(0, 3) = txtSSPO.Value 'd
ActiveCell.Offset(0, 4) = txtVOrder.Value 'e
ActiveCell.Offset(0, 5) = txtREQSD.Value 'f
ActiveCell.Offset(0, 6) = txtIHD.Value 'g
ActiveCell.Offset(0, 7) = chkREQ.Value 'h
ActiveCell.Offset(0, 8) = chkRCVD.Value 'i
ActiveCell.Offset(0, 9) = chkAPPR.Value 'j
ActiveCell.Offset(0, 10) = txtTrack.Value 'k
ActiveCell.Offset(0, 11) = txtASD.Value 'l
ActiveCell.Offset(0, 12) = cbSTAT.Value 'm
ActiveCell.Offset(0, 13) = txtVendEML.Value 'n
[code]

 

[DWRgt2885]

Sorry, I realize i didn't quite ask a question.

My question is, how do make this code update the cell each textbox is referencing regardless of what the active cell on the sheet is?

 

[Jan Mach]

Sorry, your code is near to the most complicated one you could write.

With [LEFT][COLOR=#574123][FONT=Tahoma]ActiveCell[/FONT][/COLOR][/LEFT].Resize(, 14)
    [LEFT][COLOR=#574123][FONT=Tahoma].Item(1) = txtDateSUB 'a[/FONT][/COLOR]
[COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#222222][FONT=Verdana]    [/FONT][/COLOR][COLOR=#574123][FONT=Tahoma].Item(2)[/FONT][/COLOR][/LEFT] = txtvendor 'b
    [/FONT][/COLOR][COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#574123][FONT=Tahoma].Item(3) [/FONT][/COLOR][/LEFT]= txtCust 'c
    . . . . . [/FONT][/COLOR]
[COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#574123][FONT=Tahoma]    [/FONT][/COLOR][COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#574123][FONT=Tahoma].Item(14)[/FONT][/COLOR][/LEFT][/FONT][/COLOR][/LEFT] = txtVendEML 'n
End With

or

Dim r As Range
Set r = [LEFT][COLOR=#574123][FONT=Tahoma]ActiveCell[/FONT][/COLOR][COLOR=#222222][FONT=Verdana].Resize(, 14)
[/FONT][/COLOR][/LEFT][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]r[/FONT][/COLOR][COLOR=#574123][FONT=Tahoma](1) = txtDateSUB 'a[/FONT][/COLOR]
[COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#222222][FONT=Verdana]r[/FONT][/COLOR][COLOR=#574123][FONT=Tahoma](2)[/FONT][/COLOR][/LEFT] = txtvendor 'b
r[/FONT][/COLOR][COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#574123][FONT=Tahoma](3) [/FONT][/COLOR][/LEFT]= txtCust 'c
. . . . . [/FONT][/COLOR]
[COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#574123][FONT=Tahoma]r[/FONT][/COLOR][COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#574123][FONT=Tahoma](14)[/FONT][/COLOR][/LEFT][/FONT][/COLOR][/LEFT] = txtVendEML 'n

THESE are normal codes. Still, they aren't clever enough. Adjust ordinal numbers of your textboxes to the range indices. Start with an empty userform and place a label on it; the label will have the ordinal number 0. Then sequentially place your textboxes to have the ordinal numbers 1 to 14. Finaly, place the update and cancel buttons and the other labels. Then you can write the following code in the update button procedure:

Const nCells = 14&
[LEFT][COLOR=#574123][FONT=Tahoma]Dim r As Range, i&[/FONT][/COLOR]
[COLOR=#574123][FONT=Tahoma]Set r = [/FONT][/COLOR][COLOR=#574123][FONT=Tahoma]ActiveCell[/FONT][/COLOR][COLOR=#222222][FONT=Verdana].Resize(, nCells)[/FONT][/COLOR][/LEFT][B][I][U][SUB][SUP]<strike>
</strike>[/SUP][/SUB][/U][/I][/B][/FONT][/COLOR][/LEFT]For i = 1 To nCells
    r(i) = Controls(i)
Next

And the best way is to NAME your range, for instance "forTxbs", and write:

[LEFT][COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#574123][FONT=Tahoma]Dim r As Range, i&[/FONT][/COLOR]
[COLOR=#574123][FONT=Tahoma]Set r = [/FONT][/COLOR][COLOR=#574123][FONT=Tahoma][[LEFT][COLOR=#222222][FONT=Verdana]forTxbs[/FONT][/COLOR][/LEFT]][/FONT][/COLOR][/LEFT][B][I][U][SUB][SUP]<strike>
</strike>[/SUP][/SUB][/U][/I][/B][/FONT][/COLOR][COLOR=#222222][FONT=Verdana]For i = 1 To r.Columns.Count[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    r(i) = Controls(i)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Next[/FONT][/COLOR][/LEFT]

 

[Jan Mach]

I don't know why the poster formatted my codes in such ugly way. I'm trying again:

The better coding:

[LEFT][COLOR=#333333][FONT=monospace]With [/FONT][/COLOR][/LEFT][COLOR=#574123][FONT=Tahoma]ActiveCell[/FONT][/COLOR][LEFT][COLOR=#333333][FONT=monospace].Resize(, 14)

    [/FONT][/COLOR][/LEFT][COLOR=#574123][FONT=Tahoma].Item(1) = txtDateSUB 'a[/FONT][/COLOR]
[LEFT][COLOR=#333333][FONT=monospace][COLOR=#574123][FONT=Tahoma][LEFT][COLOR=#574123][FONT=Tahoma]    .Item(2) [/FONT][/COLOR]= txtvendor 'b
[/LEFT]
    [/FONT][/COLOR][COLOR=#574123][FONT=Tahoma][COLOR=#574123][FONT=Tahoma].Item(3) [/FONT][/COLOR]= txtCust 'c
    . . . . . 
    [/FONT][/COLOR][COLOR=#574123][FONT=Tahoma][COLOR=#574123][FONT=Tahoma][COLOR=#574123][FONT=Tahoma].Item(14)[/FONT][/COLOR][/FONT][/COLOR] = txtVendEML 'n
End With

or

[LEFT][COLOR=#574123][FONT=monospace]Dim r As Range
Set r = [/FONT][/COLOR][/LEFT][COLOR=#574123][FONT=Tahoma]ActiveCell[/FONT][/COLOR][COLOR=#222222][FONT=Verdana].Resize(, 14)
[/FONT][/COLOR]<strike></strike>[FONT=Verdana][SIZE=2]r[/SIZE][/FONT][COLOR=#574123][LEFT][COLOR=#574123][FONT=monospace][FONT=Tahoma](1) = txtDateSUB 'a[/FONT][/FONT][/COLOR][/LEFT][/COLOR][LEFT][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR][/LEFT][COLOR=#574123][LEFT][COLOR=#574123][FONT=monospace][FONT=Tahoma][LEFT][COLOR=#222222][FONT=Verdana]r[/FONT][/COLOR][COLOR=#574123][FONT=Tahoma](2)[/FONT][/COLOR] = txtvendor 'b
[/LEFT]
r[/FONT][/FONT][/COLOR][/LEFT][/COLOR][COLOR=#574123][LEFT][COLOR=#574123][FONT=monospace][FONT=Tahoma][COLOR=#574123][FONT=Tahoma](3) [/FONT][/COLOR]= txtCust 'c
. . . . . [/FONT][/FONT][/COLOR][/LEFT][/COLOR][LEFT][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR][/LEFT][/FONT][/COLOR][LEFT][COLOR=#574123][FONT=Tahoma][COLOR=#574123][LEFT][COLOR=#574123][FONT=monospace][FONT=Tahoma][COLOR=#574123][FONT=Tahoma]r[/FONT][/COLOR][COLOR=#574123][FONT=Tahoma][COLOR=#574123][FONT=Tahoma](14)[/FONT][/COLOR][/FONT][/COLOR] = txtVendEML 'n

THESE are normal codes. Still, they aren't clever enough. Adjust ordinal numbers of your textboxes to the range indices. Start with an empty userform and place a label on it; the label will have the ordinal number 0. Then sequentially place your textboxes to have the ordinal numbers 1 to 14. Finaly, place the update and cancel buttons and the other labels. Then you can write the following code in the update button procedure:

[LEFT][COLOR=#574123][FONT=monospace]Const nCells = 14&
[/FONT][/COLOR][COLOR=#574123][FONT=monospace][COLOR=#574123][FONT=Tahoma]Dim r As Range, i&[/FONT][/COLOR]
[COLOR=#574123][FONT=Tahoma]Set r = [/FONT][/COLOR][COLOR=#574123][FONT=Tahoma]ActiveCell[/FONT][/COLOR][COLOR=#222222][FONT=Verdana].Resize(, nCells)
[LEFT][COLOR=#333333][FONT=monospace]For i = 1 To nCells
    r(i) = Controls(i)
Next[/FONT][/COLOR][/LEFT]

And the best way is to NAME your range, for instance "forTxbs", and write:
​

[FONT=Tahoma][LEFT][COLOR=#574123][FONT=Tahoma]Dim r As Range, i&[/FONT][/COLOR]
[COLOR=#574123][FONT=Tahoma]Set r = [/FONT][/COLOR][COLOR=#574123][FONT=Tahoma][[COLOR=#222222][FONT=Verdana]forTxbs[/FONT][/COLOR]][/FONT][/COLOR][/LEFT]
[/FONT][COLOR=#222222][LEFT][COLOR=#222222][FONT=monospace][FONT=Verdana]For i = 1 To r.Columns.Count[/FONT][/FONT][/COLOR][/LEFT][/COLOR][LEFT][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR][/LEFT][COLOR=#222222][LEFT][COLOR=#222222][FONT=monospace][FONT=Verdana]    r(i) = Controls(i)[/FONT][/FONT][/COLOR][/LEFT][/COLOR][LEFT][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR][/LEFT][COLOR=#222222][LEFT][COLOR=#222222][FONT=monospace][FONT=Verdana]Next[/FONT][/FONT][/COLOR][/LEFT][/COLOR]

_{^{<strike style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; border-bottom-color: rgb(87, 65, 35); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(87, 65, 35); border-left-style: none; border-left-width: 0px; border-right-color: rgb(87, 65, 35); border-right-style: none; border-right-width: 0px; border-top-color: rgb(87, 65, 35); border-top-style: none; border-top-width: 0px; direction: ltr; font-family: monospace; font-size: 5.73px; line-height: 12px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left;">
</strike>}}[/FONT][/COLOR]​

[/FONT][/COLOR][/LEFT][/FONT][/FONT][/COLOR][/LEFT][/COLOR][/FONT][/COLOR]
[/LEFT]
[/FONT][/COLOR][/LEFT]

 

[Jan Mach]

No, no, and no again. Who programmed the poster routine? It created awful garble twice I haven't had written. No more posts I swear!