PDA

View Full Version : can this be simplified with a loop?



JordanGoodch
01-11-2012, 09:25 PM
Is there a way in which to simplify this code with loops.
There a two sections one that clears all of the text boxes on the sheet and the second section is used to save the data in the text boxes onto the sheet.


Private Sub UserForm_Open()
ComboBox1.Clear
TextBox2.Clear
TextBox3.Clear
TextBox4.Clear
TextBox5.Clear
TextBox6.Clear
TextBox7.Clear
TextBox8.Clear
TextBox9.Clear
TextBox10.Clear
TextBox11.Clear
TextBox12.Clear
TextBox13.Clear
TextBox14.Clear
TextBox15.Clear
TextBox16.Clear
TextBox17.Clear
TextBox18.Clear
TextBox19.Clear
TextBox20.Clear
TextBox21.Clear
TextBox22.Clear
TextBox23.Clear
TextBox24.Clear
TextBox25.Clear
TextBox26.Clear
TextBox27.Clear
TextBox28.Clear
TextBox29.Clear
TextBox30.Clear
TextBox31.Clear
TextBox32.Clear
TextBox33.Clear
TextBox34.Clear
TextBox35.Clear
TextBox36.Clear
TextBox37.Clear
TextBox38.Clear
TextBox39.Clear
TextBox40.Clear
TextBox41.Clear
TextBox42.Clear
TextBox43.Clear
TextBox44.Clear
TextBox45.Clear
TextBox46.Clear
TextBox47.Clear
TextBox48.Clear
TextBox49.Clear
TextBox50.Clear
TextBox51.Clear
TextBox52.Clear
TextBox53.Clear
TextBox54.Clear
TextBox55.Clear
TextBox56.Clear
TextBox57.Clear
TextBox58.Clear
TextBox59.Clear
TextBox60.Clear
TextBox61.Clear
TextBox62.Clear
TextBox63.Clear
TextBox64.Clear
TextBox65.Clear
TextBox66.Clear
TextBox67.Clear
TextBox68.Clear
TextBox69.Clear
TextBox70.Clear
TextBox71.Clear
TextBox72.Clear
TextBox73.Clear
TextBox74.Clear
TextBox75.Clear
TextBox76.Clear
TextBox77.Clear
TextBox78.Clear
TextBox79.Clear
TextBox80.Clear
TextBox81.Clear
TextBox82.Clear
TextBox83.Clear
TextBox84.Clear
TextBox85.Clear
TextBox86.Clear
TextBox87.Clear
TextBox88.Clear
TextBox89.Clear
TextBox90.Clear
TextBox91.Clear
TextBox92.Clear
TextBox93.Clear
TextBox94.Clear
TextBox95.Clear
TextBox96.Clear
TextBox97.Clear
TextBox98.Clear
TextBox99.Clear
TextBox100.Clear
TextBox101.Clear
TextBox102.Clear
TextBox103.Clear
TextBox104.Clear
TextBox105.Clear
TextBox106.Clear
TextBox107.Clear
TextBox108.Clear
TextBox109.Clear
TextBox110.Clear
TextBox111.Clear
TextBox112.Clear
TextBox113.Clear
TextBox114.Clear
TextBox115.Clear
TextBox116.Clear
TextBox117.Clear
TextBox118.Clear
TextBox119.Clear
TextBox120.Clear
TextBox121.Clear
TextBox122.Clear
TextBox123.Clear
TextBox124.Clear
TextBox125.Clear
TextBox126.Clear
TextBox127.Clear
TextBox128.Clear
TextBox129.Clear
TextBox130.Clear
TextBox131.Clear
TextBox132.Clear
TextBox133.Clear
TextBox134.Clear
TextBox135.Clear
TextBox136.Clear
TextBox137.Clear
TextBox138.Clear
TextBox139.Clear
TextBox140.Clear
TextBox141.Clear
TextBox142.Clear
TextBox143.Clear
TextBox144.Clear
TextBox145.Clear
TextBox146.Clear
TextBox147.Clear
TextBox148.Clear
TextBox149.Clear
TextBox150.Clear
TextBox151.Clear
TextBox152.Clear
TextBox153.Clear
End Sub




Private Sub CommandButton1_Click()
Dim I As Long
Dim X As Long
X = Worksheets("Export").Range("A375").End(xlUp).Row + 1
I = Worksheets("Stockpile").Range("A375").End(xlUp).Row + 1


Worksheets("Export").Cells(X, 1) = dt
Worksheets("Export").Cells(X, 2) = Me.TextBox2.Value
Worksheets("Export").Cells(X, 3) = Me.TextBox3.Value
Worksheets("Export").Cells(X, 4) = Me.ComboBox1.Value

Worksheets("Stockpile").Cells(I, 1) = dt
Worksheets("Stockpile").Cells(I, 2) = Me.TextBox2.Value
Worksheets("Stockpile").Cells(I, 3) = Me.TextBox3.Value
Worksheets("Stockpile").Cells(I, 4) = Me.ComboBox1.Value

Worksheets("Stockpile").Cells(I, 5) = Me.TextBox4.Value
Worksheets("Stockpile").Cells(I, 6) = Me.TextBox5.Value
Worksheets("Stockpile").Cells(I, 7) = Me.TextBox6.Value
Worksheets("Stockpile").Cells(I, 8) = Me.TextBox7.Value
Worksheets("Stockpile").Cells(I, 9) = Me.TextBox8.Value
Worksheets("Stockpile").Cells(I, 10) = Me.TextBox9.Value
Worksheets("Stockpile").Cells(I, 11) = Me.TextBox10.Value
Worksheets("Stockpile").Cells(I, 12) = Me.TextBox11.Value
Worksheets("Stockpile").Cells(I, 13) = Me.TextBox12.Value
Worksheets("Stockpile").Cells(I, 14) = Me.TextBox13.Value
Worksheets("Stockpile").Cells(I, 15) = Me.TextBox14.Value
Worksheets("Stockpile").Cells(I, 16) = Me.TextBox15.Value
Worksheets("Stockpile").Cells(I, 17) = Me.TextBox16.Value
Worksheets("Stockpile").Cells(I, 18) = Me.TextBox17.Value
Worksheets("Stockpile").Cells(I, 19) = Me.TextBox18.Value
Worksheets("Stockpile").Cells(I, 20) = Me.TextBox19.Value
Worksheets("Stockpile").Cells(I, 21) = Me.TextBox20.Value
Worksheets("Stockpile").Cells(I, 22) = Me.TextBox21.Value
Worksheets("Stockpile").Cells(I, 23) = Me.TextBox22.Value
Worksheets("Stockpile").Cells(I, 24) = Me.TextBox23.Value
Worksheets("Stockpile").Cells(I, 25) = Me.TextBox24.Value
Worksheets("Stockpile").Cells(I, 26) = Me.TextBox25.Value
Worksheets("Stockpile").Cells(I, 27) = Me.TextBox26.Value
Worksheets("Stockpile").Cells(I, 28) = Me.TextBox27.Value
Worksheets("Stockpile").Cells(I, 29) = Me.TextBox28.Value
Worksheets("Stockpile").Cells(I, 30) = Me.TextBox29.Value
Worksheets("Stockpile").Cells(I, 31) = Me.TextBox30.Value
Worksheets("Stockpile").Cells(I, 32) = Me.TextBox31.Value
Worksheets("Stockpile").Cells(I, 33) = Me.TextBox32.Value
Worksheets("Stockpile").Cells(I, 34) = Me.TextBox33.Value
Worksheets("Stockpile").Cells(I, 35) = Me.TextBox34.Value
Worksheets("Stockpile").Cells(I, 36) = Me.TextBox35.Value
Worksheets("Stockpile").Cells(I, 37) = Me.TextBox36.Value
Worksheets("Stockpile").Cells(I, 38) = Me.TextBox37.Value
Worksheets("Stockpile").Cells(I, 39) = Me.TextBox38.Value
Worksheets("Stockpile").Cells(I, 40) = Me.TextBox39.Value
Worksheets("Stockpile").Cells(I, 41) = Me.TextBox40.Value
Worksheets("Stockpile").Cells(I, 42) = Me.TextBox41.Value
Worksheets("Stockpile").Cells(I, 43) = Me.TextBox42.Value
Worksheets("Stockpile").Cells(I, 44) = Me.TextBox43.Value
Worksheets("Stockpile").Cells(I, 45) = Me.TextBox44.Value
Worksheets("Stockpile").Cells(I, 46) = Me.TextBox45.Value
Worksheets("Stockpile").Cells(I, 47) = Me.TextBox46.Value
Worksheets("Stockpile").Cells(I, 48) = Me.TextBox47.Value
Worksheets("Stockpile").Cells(I, 49) = Me.TextBox48.Value
Worksheets("Stockpile").Cells(I, 50) = Me.TextBox49.Value
Worksheets("Stockpile").Cells(I, 51) = Me.TextBox50.Value
Worksheets("Stockpile").Cells(I, 52) = Me.TextBox51.Value
Worksheets("Stockpile").Cells(I, 53) = Me.TextBox52.Value
Worksheets("Stockpile").Cells(I, 54) = Me.TextBox53.Value
Worksheets("Stockpile").Cells(I, 55) = Me.TextBox54.Value
Worksheets("Stockpile").Cells(I, 56) = Me.TextBox55.Value
Worksheets("Stockpile").Cells(I, 57) = Me.TextBox56.Value
Worksheets("Stockpile").Cells(I, 58) = Me.TextBox57.Value
Worksheets("Stockpile").Cells(I, 59) = Me.TextBox58.Value
Worksheets("Stockpile").Cells(I, 60) = Me.TextBox59.Value
Worksheets("Stockpile").Cells(I, 61) = Me.TextBox60.Value
Worksheets("Stockpile").Cells(I, 62) = Me.TextBox61.Value
Worksheets("Stockpile").Cells(I, 63) = Me.TextBox62.Value
Worksheets("Stockpile").Cells(I, 64) = Me.TextBox63.Value
Worksheets("Stockpile").Cells(I, 65) = Me.TextBox64.Value
Worksheets("Stockpile").Cells(I, 66) = Me.TextBox65.Value
Worksheets("Stockpile").Cells(I, 67) = Me.TextBox66.Value
Worksheets("Stockpile").Cells(I, 68) = Me.TextBox67.Value
Worksheets("Stockpile").Cells(I, 69) = Me.TextBox68.Value
Worksheets("Stockpile").Cells(I, 70) = Me.TextBox69.Value
Worksheets("Stockpile").Cells(I, 71) = Me.TextBox70.Value
Worksheets("Stockpile").Cells(I, 72) = Me.TextBox71.Value
Worksheets("Stockpile").Cells(I, 73) = Me.TextBox72.Value
Worksheets("Stockpile").Cells(I, 74) = Me.TextBox73.Value
Worksheets("Stockpile").Cells(I, 75) = Me.TextBox74.Value
Worksheets("Stockpile").Cells(I, 76) = Me.TextBox75.Value
Worksheets("Stockpile").Cells(I, 77) = Me.TextBox76.Value
Worksheets("Stockpile").Cells(I, 78) = Me.TextBox77.Value
Worksheets("Stockpile").Cells(I, 79) = Me.TextBox78.Value
Worksheets("Stockpile").Cells(I, 80) = Me.TextBox79.Value
Worksheets("Stockpile").Cells(I, 81) = Me.TextBox80.Value
Worksheets("Stockpile").Cells(I, 82) = Me.TextBox81.Value
Worksheets("Stockpile").Cells(I, 83) = Me.TextBox82.Value
Worksheets("Stockpile").Cells(I, 84) = Me.TextBox83.Value
Worksheets("Stockpile").Cells(I, 85) = Me.TextBox84.Value
Worksheets("Stockpile").Cells(I, 86) = Me.TextBox85.Value
Worksheets("Stockpile").Cells(I, 87) = Me.TextBox86.Value
Worksheets("Stockpile").Cells(I, 88) = Me.TextBox87.Value
Worksheets("Stockpile").Cells(I, 89) = Me.TextBox88.Value
Worksheets("Stockpile").Cells(I, 90) = Me.TextBox89.Value
Worksheets("Stockpile").Cells(I, 91) = Me.TextBox90.Value
Worksheets("Stockpile").Cells(I, 92) = Me.TextBox91.Value
Worksheets("Stockpile").Cells(I, 93) = Me.TextBox92.Value
Worksheets("Stockpile").Cells(I, 94) = Me.TextBox93.Value
Worksheets("Stockpile").Cells(I, 95) = Me.TextBox94.Value
Worksheets("Stockpile").Cells(I, 96) = Me.TextBox95.Value
Worksheets("Stockpile").Cells(I, 97) = Me.TextBox96.Value
Worksheets("Stockpile").Cells(I, 98) = Me.TextBox97.Value
Worksheets("Stockpile").Cells(I, 99) = Me.TextBox98.Value
Worksheets("Stockpile").Cells(I, 100) = Me.TextBox99.Value
Worksheets("Stockpile").Cells(I, 101) = Me.TextBox100.Value
Worksheets("Stockpile").Cells(I, 102) = Me.TextBox101.Value
Worksheets("Stockpile").Cells(I, 103) = Me.TextBox102.Value
Worksheets("Stockpile").Cells(I, 104) = Me.TextBox103.Value
Worksheets("Stockpile").Cells(I, 105) = Me.TextBox104.Value
Worksheets("Stockpile").Cells(I, 106) = Me.TextBox105.Value
Worksheets("Stockpile").Cells(I, 107) = Me.TextBox106.Value
Worksheets("Stockpile").Cells(I, 108) = Me.TextBox107.Value
Worksheets("Stockpile").Cells(I, 109) = Me.TextBox108.Value
Worksheets("Stockpile").Cells(I, 110) = Me.TextBox109.Value
Worksheets("Stockpile").Cells(I, 111) = Me.TextBox110.Value
Worksheets("Stockpile").Cells(I, 112) = Me.TextBox111.Value
Worksheets("Stockpile").Cells(I, 113) = Me.TextBox112.Value
Worksheets("Stockpile").Cells(I, 114) = Me.TextBox113.Value
Worksheets("Stockpile").Cells(I, 115) = Me.TextBox114.Value
Worksheets("Stockpile").Cells(I, 116) = Me.TextBox115.Value
Worksheets("Stockpile").Cells(I, 117) = Me.TextBox116.Value
Worksheets("Stockpile").Cells(I, 118) = Me.TextBox117.Value


Worksheets("Export").Cells(X, 5) = Me.TextBox118.Value
Worksheets("Export").Cells(X, 6) = Me.TextBox119.Value
Worksheets("Export").Cells(X, 7) = Me.TextBox120.Value
Worksheets("Export").Cells(X, 8) = Me.TextBox121.Value
Worksheets("Export").Cells(X, 9) = Me.TextBox122.Value
Worksheets("Export").Cells(X, 10) = Me.TextBox123.Value
Worksheets("Export").Cells(X, 11) = Me.TextBox124.Value
Worksheets("Export").Cells(X, 12) = Me.TextBox125.Value
Worksheets("Export").Cells(X, 13) = Me.TextBox126.Value
Worksheets("Export").Cells(X, 14) = Me.TextBox127.Value
Worksheets("Export").Cells(X, 15) = Me.TextBox128.Value
Worksheets("Export").Cells(X, 16) = Me.TextBox129.Value
Worksheets("Export").Cells(X, 17) = Me.TextBox130.Value
Worksheets("Export").Cells(X, 18) = Me.TextBox131.Value
Worksheets("Export").Cells(X, 19) = Me.TextBox132.Value
Worksheets("Export").Cells(X, 20) = Me.TextBox133.Value
Worksheets("Export").Cells(X, 21) = Me.TextBox134.Value
Worksheets("Export").Cells(X, 22) = Me.TextBox134.Value
Worksheets("Export").Cells(X, 23) = Me.TextBox136.Value
Worksheets("Export").Cells(X, 24) = Me.TextBox137.Value
Worksheets("Export").Cells(X, 25) = Me.TextBox138.Value
Worksheets("Export").Cells(X, 26) = Me.TextBox139.Value
Worksheets("Export").Cells(X, 27) = Me.TextBox140.Value
Worksheets("Export").Cells(X, 28) = Me.TextBox141.Value
Worksheets("Export").Cells(X, 29) = Me.TextBox142.Value
Worksheets("Export").Cells(X, 30) = Me.TextBox143.Value
Worksheets("Export").Cells(X, 31) = Me.TextBox144.Value
Worksheets("Export").Cells(X, 32) = Me.TextBox145.Value
Worksheets("Export").Cells(X, 33) = Me.TextBox146.Value
Worksheets("Export").Cells(X, 34) = Me.TextBox147.Value
Worksheets("Export").Cells(X, 35) = Me.TextBox148.Value
Worksheets("Export").Cells(X, 36) = Me.TextBox149.Value
Worksheets("Export").Cells(X, 37) = Me.TextBox150.Value
Worksheets("Export").Cells(X, 38) = Me.TextBox151.Value
Worksheets("Export").Cells(X, 39) = Me.TextBox152.Value
Worksheets("Export").Cells(X, 40) = Me.TextBox153.Value

UserForm1.Hide
End Sub

nilem
01-11-2012, 10:43 PM
try this
Private Sub UserForm_Activate()
Dim i As Long
For i = 1 To 153
Me.Controls("TextBox" & i) = ""
Next i
Me.ComboBox1.Clear
End Sub

Private Sub CommandButton1_Click()
Dim i As Long, j As Long

With Worksheets("Stockpile")
i = .Range("A375").End(xlUp).Row + 1
.Cells(i, 1).Resize(, 4).Value = Array(dt, Me.TextBox2.Value, Me.TextBox3.Value, Me.ComboBox1.Value)
For j = 4 To 117
.Cells(i, j + 1) = Me.Controls("TextBox" & j)
Next j
End With

With Worksheets("Export")
i = .Range("A375").End(xlUp).Row + 1
.Cells(i, 1).Resize(, 4).Value = Array(dt, Me.TextBox2.Value, Me.TextBox3.Value, Me.ComboBox1.Value)
For j = 5 To 40
Worksheets("Export").Cells(x, j) = Me.Controls("TextBox" & j + 113)
Next j
End With

Me.Hide
End Sub