Userform textboxs autofill

[Kingchaos64]

I have a UserForm2 with 3 textbox columns.
'Claim Check, City, Employee Int'.
Is there a way to make the City and Employee textboxes auto populate with the City and Employee that was entered right above it?
Claim Check textbox (Name) starts at c1 the one below it is c2 and so on. City textbox is s1 the one below it is s2 and so on and Employee is e1 and below is e2.

I attached a photo of an example of what I would like to see when I enter the first set of data.

Just trying to make this a faster data entry. Claim checks will always change but city and Employee will only change a bit.

 

[Kingchaos64]

Private Sub CommandButton4_Click()

Dim iRow As Long

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")





iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row



If Trim(Me.truck.Value) = "" Then

Me.truck.SetFocus

MsgBox "Please enter truck Number"

Exit Sub

End If



If Trim(Me.date1.Value) = "" Then

Me.truck.SetFocus

MsgBox "Please enter the Date"

Exit Sub

End If



Rows(iRow).Insert





ws.Cells(iRow, 1).Value = Me.date1.Value

ws.Cells(iRow, 2).Value = Me.truck.Value

'row 3 is a count c rows

ws.Cells(iRow, 4).Value = Me.onn.Value

ws.Cells(iRow, 5).Value = Me.c1.Value

ws.Cells(iRow, 6).Value = Me.s1.Value

ws.Cells(iRow, 7).Value = Me.e1.Value

ws.Cells(iRow, 8).Value = Me.c2.Value

ws.Cells(iRow, 9).Value = Me.s2.Value

ws.Cells(iRow, 10).Value = Me.e2.Value

ws.Cells(iRow, 11).Value = Me.c3.Value

ws.Cells(iRow, 12).Value = Me.s3.Value

ws.Cells(iRow, 13).Value = Me.e3.Value

ws.Cells(iRow, 14).Value = Me.c4.Value

ws.Cells(iRow, 15).Value = Me.s4.Value

ws.Cells(iRow, 16).Value = Me.e4.Value

ws.Cells(iRow, 17).Value = Me.c5.Value

ws.Cells(iRow, 18).Value = Me.s5.Value

ws.Cells(iRow, 19).Value = Me.e5.Value

ws.Cells(iRow, 20).Value = Me.c6.Value

ws.Cells(iRow, 21).Value = Me.s6.Value

ws.Cells(iRow, 22).Value = Me.e6.Value

ws.Cells(iRow, 23).Value = Me.c7.Value

ws.Cells(iRow, 24).Value = Me.s7.Value

ws.Cells(iRow, 25).Value = Me.e7.Value

ws.Cells(iRow, 26).Value = Me.c8.Value

ws.Cells(iRow, 27).Value = Me.s8.Value

ws.Cells(iRow, 28).Value = Me.e8.Value

ws.Cells(iRow, 29).Value = Me.c9.Value

ws.Cells(iRow, 30).Value = Me.s9.Value

ws.Cells(iRow, 31).Value = Me.e9.Value

ws.Cells(iRow, 32).Value = Me.c10.Value

ws.Cells(iRow, 33).Value = Me.s10.Value

ws.Cells(iRow, 34).Value = Me.e10.Value

ws.Cells(iRow, 35).Value = Me.c11.Value

ws.Cells(iRow, 36).Value = Me.s11.Value

ws.Cells(iRow, 37).Value = Me.e11.Value

ws.Cells(iRow, 38).Value = Me.c12.Value

ws.Cells(iRow, 39).Value = Me.s12.Value

ws.Cells(iRow, 40).Value = Me.e12.Value

ws.Cells(iRow, 41).Value = Me.c13.Value

ws.Cells(iRow, 42).Value = Me.s13.Value

ws.Cells(iRow, 43).Value = Me.e13.Value

ws.Cells(iRow, 44).Value = Me.c14.Value

ws.Cells(iRow, 45).Value = Me.s14.Value

ws.Cells(iRow, 46).Value = Me.e14.Value

ws.Cells(iRow, 47).Value = Me.c15.Value

ws.Cells(iRow, 48).Value = Me.s15.Value

ws.Cells(iRow, 49).Value = Me.e15.Value

ws.Cells(iRow, 50).Value = Me.c16.Value

ws.Cells(iRow, 51).Value = Me.s16.Value

ws.Cells(iRow, 52).Value = Me.e16.Value

ws.Cells(iRow, 53).Value = Me.c17.Value

ws.Cells(iRow, 54).Value = Me.s17.Value

ws.Cells(iRow, 55).Value = Me.e17.Value

ws.Cells(iRow, 56).Value = Me.c18.Value

ws.Cells(iRow, 57).Value = Me.s18.Value

ws.Cells(iRow, 58).Value = Me.e18.Value

ws.Cells(iRow, 59).Value = Me.c19.Value

ws.Cells(iRow, 60).Value = Me.s19.Value

ws.Cells(iRow, 61).Value = Me.e19.Value

ws.Cells(iRow, 62).Value = Me.c20.Value

ws.Cells(iRow, 63).Value = Me.s20.Value

ws.Cells(iRow, 64).Value = Me.e20.Value

ws.Cells(iRow, 65).Value = Me.c21.Value

ws.Cells(iRow, 66).Value = Me.s21.Value

ws.Cells(iRow, 67).Value = Me.e21.Value

ws.Cells(iRow, 68).Value = Me.c22.Value

ws.Cells(iRow, 69).Value = Me.s22.Value

ws.Cells(iRow, 70).Value = Me.e22.Value

ws.Cells(iRow, 71).Value = Me.c23.Value

ws.Cells(iRow, 72).Value = Me.s23.Value

ws.Cells(iRow, 73).Value = Me.e23.Value

ws.Cells(iRow, 74).Value = Me.c24.Value

ws.Cells(iRow, 75).Value = Me.s24.Value

ws.Cells(iRow, 76).Value = Me.e24.Value

ws.Cells(iRow, 77).Value = Me.c25.Value

ws.Cells(iRow, 78).Value = Me.s25.Value

ws.Cells(iRow, 79).Value = Me.e25.Value

ws.Cells(iRow, 80).Value = Me.c26.Value

ws.Cells(iRow, 81).Value = Me.s26.Value

ws.Cells(iRow, 82).Value = Me.e26.Value

ws.Cells(iRow, 83).Value = Me.c27.Value

ws.Cells(iRow, 84).Value = Me.s27.Value

ws.Cells(iRow, 85).Value = Me.e27.Value

ws.Cells(iRow, 86).Value = Me.c28.Value

ws.Cells(iRow, 87).Value = Me.s28.Value

ws.Cells(iRow, 88).Value = Me.e28.Value

ws.Cells(iRow, 89).Value = Me.c29.Value

ws.Cells(iRow, 90).Value = Me.s29.Value

ws.Cells(iRow, 91).Value = Me.e29.Value

ws.Cells(iRow, 92).Value = Me.c30.Value

ws.Cells(iRow, 93).Value = Me.s30.Value

ws.Cells(iRow, 94).Value = Me.e30.Value

ws.Cells(iRow, 95).Value = Me.c31.Value

ws.Cells(iRow, 96).Value = Me.s31.Value

ws.Cells(iRow, 97).Value = Me.e31.Value

ws.Cells(iRow, 98).Value = Me.c32.Value

ws.Cells(iRow, 99).Value = Me.s32.Value

ws.Cells(iRow, 100).Value = Me.e32.Value

ws.Cells(iRow, 101).Value = Me.c33.Value

ws.Cells(iRow, 102).Value = Me.s33.Value

ws.Cells(iRow, 103).Value = Me.e33.Value

ws.Cells(iRow, 104).Value = Me.c34.Value

ws.Cells(iRow, 105).Value = Me.s34.Value

ws.Cells(iRow, 106).Value = Me.e34.Value

ws.Cells(iRow, 107).Value = Me.c35.Value

ws.Cells(iRow, 108).Value = Me.s35.Value

ws.Cells(iRow, 109).Value = Me.e35.Value

ws.Cells(iRow, 110).Value = Me.c36.Value

ws.Cells(iRow, 111).Value = Me.s36.Value

ws.Cells(iRow, 112).Value = Me.e36.Value

ws.Cells(iRow, 113).Value = Me.c37.Value

ws.Cells(iRow, 114).Value = Me.s37.Value

ws.Cells(iRow, 115).Value = Me.e37.Value

ws.Cells(iRow, 116).Value = Me.c38.Value

ws.Cells(iRow, 117).Value = Me.s38.Value

ws.Cells(iRow, 118).Value = Me.e38.Value

ws.Cells(iRow, 119).Value = Me.c39.Value

ws.Cells(iRow, 120).Value = Me.s39.Value

ws.Cells(iRow, 121).Value = Me.e39.Value

ws.Cells(iRow, 122).Value = Me.c40.Value

ws.Cells(iRow, 123).Value = Me.s40.Value

ws.Cells(iRow, 124).Value = Me.e40.Value

ws.Cells(iRow, 125).Value = Me.c41.Value

ws.Cells(iRow, 126).Value = Me.s41.Value

ws.Cells(iRow, 127).Value = Me.e41.Value

ws.Cells(iRow, 128).Value = Me.c42.Value

ws.Cells(iRow, 129).Value = Me.s42.Value

ws.Cells(iRow, 130).Value = Me.e42.Value

ws.Cells(iRow, 131).Value = Me.c43.Value

ws.Cells(iRow, 132).Value = Me.s43.Value

ws.Cells(iRow, 133).Value = Me.e43.Value

ws.Cells(iRow, 134).Value = Me.c44.Value

ws.Cells(iRow, 135).Value = Me.s44.Value

ws.Cells(iRow, 136).Value = Me.e44.Value

ws.Cells(iRow, 137).Value = Me.c45.Value

ws.Cells(iRow, 138).Value = Me.s45.Value

ws.Cells(iRow, 139).Value = Me.e45.Value

ws.Cells(iRow, 140).Value = Me.c46.Value

ws.Cells(iRow, 141).Value = Me.s46.Value

ws.Cells(iRow, 142).Value = Me.e46.Value

ws.Cells(iRow, 143).Value = Me.c47.Value

ws.Cells(iRow, 144).Value = Me.s47.Value

ws.Cells(iRow, 145).Value = Me.e47.Value

ws.Cells(iRow, 146).Value = Me.c48.Value

ws.Cells(iRow, 147).Value = Me.s48.Value

ws.Cells(iRow, 148).Value = Me.e48.Value

ws.Cells(iRow, 149).Value = Me.c49.Value

ws.Cells(iRow, 150).Value = Me.s49.Value

ws.Cells(iRow, 151).Value = Me.e49.Value

ws.Cells(iRow, 152).Value = Me.c50.Value

ws.Cells(iRow, 153).Value = Me.s50.Value

ws.Cells(iRow, 154).Value = Me.e50.Value

ws.Cells(iRow, 155).Value = Me.c51.Value

ws.Cells(iRow, 156).Value = Me.s51.Value

ws.Cells(iRow, 157).Value = Me.e51.Value

ws.Cells(iRow, 158).Value = Me.c52.Value

ws.Cells(iRow, 159).Value = Me.s52.Value

ws.Cells(iRow, 160).Value = Me.e52.Value

ws.Cells(iRow, 161).Value = Me.c53.Value

ws.Cells(iRow, 162).Value = Me.s53.Value

ws.Cells(iRow, 163).Value = Me.e53.Value

ws.Cells(iRow, 164).Value = Me.c54.Value

ws.Cells(iRow, 165).Value = Me.s54.Value

ws.Cells(iRow, 166).Value = Me.e54.Value

ws.Cells(iRow, 167).Value = Me.c55.Value

ws.Cells(iRow, 168).Value = Me.s55.Value

ws.Cells(iRow, 169).Value = Me.e55.Value

ws.Cells(iRow, 170).Value = Me.c56.Value

ws.Cells(iRow, 171).Value = Me.s56.Value

ws.Cells(iRow, 172).Value = Me.e56.Value

ws.Cells(iRow, 173).Value = Me.c57.Value

ws.Cells(iRow, 174).Value = Me.s57.Value

ws.Cells(iRow, 175).Value = Me.e57.Value

ws.Cells(iRow, 176).Value = Me.c58.Value

ws.Cells(iRow, 177).Value = Me.s58.Value

ws.Cells(iRow, 178).Value = Me.e58.Value

ws.Cells(iRow, 179).Value = Me.c59.Value

ws.Cells(iRow, 180).Value = Me.s59.Value

ws.Cells(iRow, 181).Value = Me.e59.Value

ws.Cells(iRow, 182).Value = Me.c60.Value

ws.Cells(iRow, 183).Value = Me.s60.Value

ws.Cells(iRow, 184).Value = Me.e60.Value

ws.Cells(iRow, 185).Value = Me.c61.Value

ws.Cells(iRow, 186).Value = Me.s61.Value

ws.Cells(iRow, 187).Value = Me.e61.Value

ws.Cells(iRow, 188).Value = Me.c62.Value

ws.Cells(iRow, 189).Value = Me.s62.Value

ws.Cells(iRow, 190).Value = Me.e62.Value

ws.Cells(iRow, 191).Value = Me.c63.Value

ws.Cells(iRow, 192).Value = Me.s63.Value

ws.Cells(iRow, 193).Value = Me.e63.Value

ws.Cells(iRow, 194).Value = Me.c64.Value

ws.Cells(iRow, 195).Value = Me.s64.Value

ws.Cells(iRow, 196).Value = Me.e64.Value

ws.Cells(iRow, 197).Value = Me.c65.Value

ws.Cells(iRow, 198).Value = Me.s65.Value

ws.Cells(iRow, 199).Value = Me.e65.Value

ws.Cells(iRow, 200).Value = Me.c66.Value

ws.Cells(iRow, 201).Value = Me.s66.Value

ws.Cells(iRow, 202).Value = Me.e66.Value

ws.Cells(iRow, 203).Value = Me.c67.Value

ws.Cells(iRow, 204).Value = Me.s67.Value

ws.Cells(iRow, 205).Value = Me.e67.Value

ws.Cells(iRow, 206).Value = Me.c68.Value

ws.Cells(iRow, 207).Value = Me.s68.Value

ws.Cells(iRow, 208).Value = Me.e68.Value



Unload UserForm2











End Sub

 

[Kingchaos64]

Above is my code. Took forever lol. I would like a code that will count all the "c" textboxes that have data in them and send the count number to ws.Cells(iRow, 3).Value

 

[Kingchaos64]

Also still looking of a code or help with my original question. How to get "s" and "e" textboxes to autofill with the previous "s" and "e" entered data. Please help. I don't care if I have to manually put the code in for each textbox, in the end this will save me so much time its worth it.

 

[Akuini]

I don't care if I have to manually put the code in for each textbox, in the end this will save me so much time its worth it.

I think I can write a macro to do that.

But I'm curious, could you explain your data entry process?
Do you really need that many "rows" of textboxes?
How about just one rows of textboxes and a listbox. So after you fill in the textboxes then send the data to the listbox (maybe by clicking a button), then repeat the step as needed. So all data will be in the listbox. It will be easier to code.

 

[Kingchaos64]

Also still looking of a code or help with my original question. How to get "s" and "e" textboxes to autofill with the previous "s" and "e" entered data. Please help. I don't care if I have to manually put the code in for each textbox, in the end this will save me so much time its worth it

I think I can write a macro to do that.

But I'm curious, could you explain your data entry process?
Do you really need that many "rows" of textboxes?
How about just one rows of textboxes and a listbox. So after you fill in the textboxes then send the data to the listbox (maybe by clicking a button), then repeat the step as needed. So all data will be in the listbox. It will be easier to code.

Well it depends on the day sometimes I'll have to enter 10 claim checks and sometimes 120. So when I enter the data in the userform and click done and the data transfers to the sheet it all goes into one row. Just one really long row lol. I'm not really sure what your suggesting I can't visualize it

 

[Zot]

Also still looking of a code or help with my original question. How to get "s" and "e" textboxes to autofill with the previous "s" and "e" entered data. Please help. I don't care if I have to manually put the code in for each textbox, in the end this will save me so much time its worth it.

So, each time you fill the userform, it is for same person. After submit, then start for next person? If so, you can loop through all textbox and check for their "s' and "c" and fill accordingly to defined values. Something like this

Dim y As String
Dim c As Control

For Each c In Me.Controls
        If TypeName(c) = "TextBox" Then
            y = Left(c.Name, 1)
            If y = "s" Then
                ..... your code here .....
            Else if y = "e" Then
                ..... your code here .....
            End If
        End If
    Next c

 

[Akuini]

I'm not really sure what your suggesting I can't visualize it

Ok, here's an example:
You enter data in the texboxes then hit ENTER > data in textboxes will be sent to the listbox.
And you see the previous entry of 2 textboxes is still there, this is the part that you want:
"How to get "s" and "e" textboxes to autofill with the previous "s" and "e" entered data."
Repeat the steps to fill in more rows of data then hit FINISH > data from listbox will be sent to cell A1 onwards ( as you said "the data transfers to the sheet it all goes into one row. Just one really long row lol.")

Kingchaos64_-_USERFORM_1.xlsm

 

[Kingchaos64]

So, each time you fill the userform, it is for same person. After submit, then start for next person? If so, you can loop through all textbox and check for their "s' and "c" and fill accordingly to defined values. Something like this

Dim y As String
Dim c As Control

For Each c In Me.Controls
        If TypeName(c) = "TextBox" Then
            y = Left(c.Name, 1)
            If y = "s" Then
                ..... your code here .....
            Else if y = "e" Then
                ..... your code here .....
            End If
        End If
    Next c

No it will vary city and employee in the one userform before the submit. Could be up to 12 different people and 200 plus different citys for one truck

 

[Zot]

No it will vary city and employee in the one userform before the submit. Could be up to 12 different people and 200 plus different citys for one truck

If that is so, then using @Akuini method is the best I believe. Don't use TextBox except for data entry