How can I cut this script down?

F

Fyr

Active Member
Joined
Jan 20, 2009
Messages
375
First, my apologies if this is long.
How can make the script below shorter?
It crashes some computers that this will be used on.
I'm guessing it's because it's to long?

Thanks for taking a look!

Code: 
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
TextBox68.Value = TextBox1.Value
TextBox68.Enabled = False
TextBox69.Value = TextBox2.Value
TextBox69.Enabled = False
TextBox70.Value = TextBox3.Value
TextBox70.Enabled = False
TextBox71.Value = TextBox4.Value
TextBox71.Enabled = False
TextBox72.Value = TextBox5.Value
TextBox72.Enabled = False
TextBox73.Value = TextBox6.Value
TextBox73.Enabled = False
TextBox74.Value = TextBox7.Value
TextBox74.Enabled = False
TextBox75.Value = TextBox8.Value
TextBox75.Enabled = False
TextBox76.Value = TextBox9.Value
TextBox76.Enabled = False
TextBox77.Value = TextBox10.Value
TextBox77.Enabled = False
TextBox78.Value = TextBox11.Value
TextBox78.Enabled = False
TextBox80.Value = TextBox13.Value
TextBox80.Enabled = False
TextBox81.Value = TextBox14.Value
TextBox81.Enabled = False
TextBox82.Value = TextBox15.Value
TextBox82.Enabled = False
TextBox83.Value = TextBox16.Value
TextBox83.Enabled = False
TextBox84.Value = TextBox17.Value
TextBox84.Enabled = False
TextBox86.Value = TextBox19.Value
TextBox86.Enabled = False
TextBox87.Value = TextBox20.Value
TextBox87.Enabled = False
TextBox88.Value = TextBox21.Value
TextBox88.Enabled = False
TextBox89.Value = TextBox22.Value
TextBox89.Enabled = False
TextBox90.Value = TextBox23.Value
TextBox90.Enabled = False
TextBox92.Value = TextBox25.Value
TextBox92.Enabled = False
TextBox93.Value = TextBox26.Value
TextBox93.Enabled = False
TextBox94.Value = TextBox27.Value
TextBox94.Enabled = False
TextBox95.Value = TextBox28.Value
TextBox95.Enabled = False
TextBox96.Value = TextBox29.Value
TextBox96.Enabled = False
TextBox98.Value = TextBox31.Value
TextBox98.Enabled = False
TextBox99.Value = TextBox32.Value
TextBox99.Enabled = False
TextBox100.Value = TextBox33.Value
TextBox100.Enabled = False
TextBox101.Value = TextBox34.Value
TextBox101.Enabled = False
TextBox102.Value = TextBox35.Value
TextBox102.Enabled = False
TextBox103.Value = TextBox36.Value
TextBox103.Enabled = False
TextBox104.Value = TextBox37.Value
TextBox104.Enabled = False
TextBox105.Value = TextBox38.Value
TextBox105.Enabled = False
TextBox106.Value = TextBox39.Value
TextBox106.Enabled = False
TextBox107.Value = TextBox40.Value
TextBox107.Enabled = False
TextBox108.Value = TextBox41.Value
TextBox108.Enabled = False
TextBox109.Value = TextBox42.Value
TextBox109.Enabled = False
TextBox110.Value = TextBox43.Value
TextBox110.Enabled = False
TextBox111.Value = TextBox44.Value
TextBox111.Enabled = False
TextBox112.Value = TextBox45.Value
TextBox112.Enabled = False
TextBox113.Value = TextBox46.Value
TextBox113.Enabled = False
TextBox114.Value = TextBox47.Value
TextBox114.Enabled = False
TextBox115.Value = TextBox48.Value
TextBox115.Enabled = False
TextBox116.Value = TextBox49.Value
TextBox116.Enabled = False
TextBox117.Value = TextBox50.Value
TextBox117.Enabled = False
TextBox118.Value = TextBox51.Value
TextBox118.Enabled = False
TextBox119.Value = TextBox52.Value
TextBox119.Enabled = False
TextBox120.Value = TextBox53.Value
TextBox120.Enabled = False
TextBox121.Value = TextBox54.Value
TextBox121.Enabled = False
TextBox122.Value = TextBox55.Value
TextBox122.Enabled = False
TextBox123.Value = TextBox56.Value
TextBox123.Enabled = False
TextBox124.Value = TextBox57.Value
TextBox124.Enabled = False
TextBox125.Value = TextBox58.Value
TextBox125.Enabled = False
TextBox126.Value = TextBox59.Value
TextBox126.Enabled = False
TextBox127.Value = TextBox60.Value
TextBox127.Enabled = False
TextBox128.Value = TextBox61.Value
TextBox128.Enabled = False
TextBox129.Value = TextBox62.Value
TextBox129.Enabled = False
TextBox130.Value = TextBox63.Value
TextBox130.Enabled = False
TextBox131.Value = TextBox64.Value
TextBox131.Enabled = False
TextBox132.Value = TextBox65.Value
TextBox132.Enabled = False
TextBox133.Value = TextBox66.Value
TextBox133.Enabled = False
TextBox134.Value = TextBox67.Value
TextBox134.Enabled = False
TextBox263.Value = TextBox262.Value
TextBox263.Enabled = False
CheckBox2 = True
CheckBox2.Enabled = False
CheckBox3 = True
CheckBox3.Enabled = False
CheckBox4 = True
CheckBox4.Enabled = False
CheckBox5 = True
CheckBox5.Enabled = False
CheckBox6 = True
CheckBox6.Enabled = False
CheckBox7 = True
CheckBox7.Enabled = False
CheckBox8 = True
CheckBox8.Enabled = False
CheckBox9 = True
CheckBox9.Enabled = False
CheckBox10 = True
CheckBox10.Enabled = False
CheckBox11 = True
CheckBox11.Enabled = False
CheckBox12 = True
CheckBox12.Enabled = False
CheckBox14 = True
CheckBox14.Enabled = False
CheckBox15 = True
CheckBox15.Enabled = False
CheckBox16 = True
CheckBox16.Enabled = False
CheckBox17 = True
CheckBox17.Enabled = False
CheckBox18 = True
CheckBox18.Enabled = False
CheckBox20 = True
CheckBox20.Enabled = False
CheckBox21 = True
CheckBox21.Enabled = False
CheckBox22 = True
CheckBox22.Enabled = False
CheckBox23 = True
CheckBox23.Enabled = False
CheckBox24 = True
CheckBox24.Enabled = False
CheckBox26 = True
CheckBox26.Enabled = False
CheckBox27 = True
CheckBox27.Enabled = False
CheckBox28 = True
CheckBox28.Enabled = False
CheckBox29 = True
CheckBox29.Enabled = False
CheckBox30 = True
CheckBox30.Enabled = False
CheckBox32 = True
CheckBox32.Enabled = False
CheckBox33 = True
CheckBox33.Enabled = False
CheckBox34 = True
CheckBox34.Enabled = False
CheckBox35 = True
CheckBox35.Enabled = False
CheckBox36 = True
CheckBox36.Enabled = False
CheckBox37 = True
CheckBox37.Enabled = False
CheckBox38 = True
CheckBox38.Enabled = False
CheckBox39 = True
CheckBox39.Enabled = False
CheckBox40 = True
CheckBox40.Enabled = False
CheckBox41 = True
CheckBox41.Enabled = False
CheckBox42 = True
CheckBox42.Enabled = False
CheckBox43 = True
CheckBox43.Enabled = False
CheckBox44 = True
CheckBox44.Enabled = False
CheckBox45 = True
CheckBox45.Enabled = False
CheckBox46 = True
CheckBox46.Enabled = False
CheckBox47 = True
CheckBox47.Enabled = False
CheckBox48 = True
CheckBox48.Enabled = False
CheckBox49 = True
CheckBox49.Enabled = False
CheckBox50 = True
CheckBox50.Enabled = False
CheckBox51 = True
CheckBox51.Enabled = False
CheckBox52 = True
CheckBox52.Enabled = False
CheckBox53 = True
CheckBox53.Enabled = False
CheckBox54 = True
CheckBox54.Enabled = False
CheckBox55 = True
CheckBox55.Enabled = False
CheckBox56 = True
CheckBox56.Enabled = False
CheckBox57 = True
CheckBox57.Enabled = False
CheckBox58 = True
CheckBox58.Enabled = False
CheckBox59 = True
CheckBox59.Enabled = False
CheckBox60 = True
CheckBox60.Enabled = False
CheckBox61 = True
CheckBox61.Enabled = False
CheckBox62 = True
CheckBox62.Enabled = False
CheckBox63 = True
CheckBox63.Enabled = False
CheckBox64 = True
CheckBox64.Enabled = False
CheckBox65 = True
CheckBox65.Enabled = False
CheckBox66 = True
CheckBox66.Enabled = False
CheckBox67 = True
CheckBox67.Enabled = False
CheckBox68 = True
CheckBox68.Enabled = False
CheckBox69 = True
CheckBox69.Enabled = False
Else:
TextBox68.Enabled = True
TextBox68.Value = "0"
TextBox69.Enabled = True
TextBox69.Value = "0"
TextBox70.Enabled = True
TextBox70.Value = "0"
TextBox71.Enabled = True
TextBox71.Value = "0"
TextBox72.Enabled = True
TextBox72.Value = "0"
TextBox73.Enabled = True
TextBox73.Value = "0"
TextBox74.Enabled = True
TextBox74.Value = "0"
TextBox75.Enabled = True
TextBox76.Value = "0"
TextBox76.Enabled = True
TextBox77.Value = "0"
TextBox77.Enabled = True
TextBox78.Value = "0"
TextBox78.Enabled = True
TextBox80.Value = "0"
TextBox80.Enabled = True
TextBox81.Value = "0"
TextBox81.Enabled = True
TextBox82.Value = "0"
TextBox82.Enabled = True
TextBox83.Value = "0"
TextBox83.Enabled = True
TextBox84.Value = "0"
TextBox84.Enabled = True
TextBox86.Value = "0"
TextBox86.Enabled = True
TextBox87.Value = "0"
TextBox87.Enabled = True
TextBox88.Value = "0"
TextBox88.Enabled = True
TextBox89.Value = "0"
TextBox89.Enabled = True
TextBox90.Value = "0"
TextBox90.Enabled = True
TextBox92.Value = "0"
TextBox92.Enabled = True
TextBox93.Value = "0"
TextBox93.Enabled = True
TextBox94.Value = "0"
TextBox94.Enabled = True
TextBox95.Value = "0"
TextBox95.Enabled = True
TextBox96.Value = "0"
TextBox98.Enabled = True
TextBox98.Value = "0"
TextBox99.Enabled = True
TextBox99.Value = "0"
TextBox100.Enabled = True
TextBox100.Value = "0"
TextBox101.Enabled = True
TextBox101.Value = "0"
TextBox102.Enabled = True
TextBox102.Value = "0"
TextBox103.Enabled = True
TextBox103.Value = "0"
TextBox104.Enabled = True
TextBox104.Value = "0"
TextBox105.Enabled = True
TextBox105.Value = "0"
TextBox106.Enabled = True
TextBox106.Value = "0"
TextBox107.Enabled = True
TextBox107.Value = "0"
TextBox108.Enabled = True
TextBox108.Value = "0"
TextBox109.Enabled = True
TextBox109.Value = "0"
TextBox110.Enabled = True
TextBox111.Value = "0"
TextBox111.Enabled = True
TextBox112.Value = "0"
TextBox112.Enabled = True
TextBox113.Value = "0"
TextBox113.Enabled = True
TextBox114.Value = "0"
TextBox114.Enabled = True
TextBox115.Value = "0"
TextBox115.Enabled = True
TextBox116.Value = "0"
TextBox116.Enabled = True
TextBox117.Value = "0"
TextBox117.Enabled = True
TextBox118.Value = "0"
TextBox118.Enabled = True
TextBox119.Value = "0"
TextBox119.Enabled = True
TextBox120.Value = "0"
TextBox120.Enabled = True
TextBox121.Value = "0"
TextBox121.Enabled = True
TextBox122.Value = "0"
TextBox122.Enabled = True
TextBox123.Value = "0"
TextBox124.Enabled = True
TextBox124.Value = "0"
TextBox125.Enabled = True
TextBox125.Value = "0"
TextBox126.Enabled = True
TextBox126.Value = "0"
TextBox127.Enabled = True
TextBox127.Value = "0"
TextBox128.Enabled = True
TextBox128.Value = "0"
TextBox129.Enabled = True
TextBox129.Value = "0"
TextBox130.Enabled = True
TextBox130.Value = "0"
TextBox131.Enabled = True
TextBox131.Value = "0"
TextBox132.Enabled = True
TextBox132.Value = "0"
TextBox133.Enabled = True
TextBox133.Value = "0"
TextBox134.Enabled = True
TextBox134.Value = "0"
TextBox263.Enabled = True
TextBox263.Value = "0"
CheckBox2 = False
CheckBox2.Enabled = True
CheckBox3 = False
CheckBox3.Enabled = True
CheckBox4 = False
CheckBox4.Enabled = True
CheckBox5 = False
CheckBox5.Enabled = True
CheckBox6 = False
CheckBox6.Enabled = True
CheckBox7 = False
CheckBox7.Enabled = True
CheckBox8 = False
CheckBox8.Enabled = True
CheckBox9 = False
CheckBox9.Enabled = True
CheckBox10 = False
CheckBox10.Enabled = True
CheckBox11 = False
CheckBox11.Enabled = True
CheckBox12 = False
CheckBox12.Enabled = True
CheckBox14 = False
CheckBox14.Enabled = True
CheckBox15 = False
CheckBox15.Enabled = True
CheckBox16 = False
CheckBox16.Enabled = True
CheckBox17 = False
CheckBox17.Enabled = True
CheckBox18 = False
CheckBox18.Enabled = True
CheckBox20 = False
CheckBox20.Enabled = True
CheckBox21 = False
CheckBox21.Enabled = True
CheckBox22 = False
CheckBox22.Enabled = True
CheckBox23 = False
CheckBox23.Enabled = True
CheckBox24 = False
CheckBox24.Enabled = True
CheckBox26 = False
CheckBox26.Enabled = True
CheckBox27 = False
CheckBox27.Enabled = True
CheckBox28 = False
CheckBox28.Enabled = True
CheckBox29 = False
CheckBox29.Enabled = True
CheckBox30 = False
CheckBox30.Enabled = True
CheckBox32 = False
CheckBox32.Enabled = True
CheckBox33 = False
CheckBox33.Enabled = True
CheckBox34 = False
CheckBox34.Enabled = True
CheckBox35 = False
CheckBox35.Enabled = True
CheckBox36 = False
CheckBox36.Enabled = True
CheckBox37 = False
CheckBox37.Enabled = True
CheckBox38 = False
CheckBox38.Enabled = True
CheckBox39 = False
CheckBox39.Enabled = True
CheckBox40 = False
CheckBox40.Enabled = True
CheckBox41 = False
CheckBox41.Enabled = True
CheckBox42 = False
CheckBox42.Enabled = True
CheckBox43 = False
CheckBox43.Enabled = True
CheckBox44 = False
CheckBox44.Enabled = True
CheckBox45 = False
CheckBox45.Enabled = True
CheckBox46 = False
CheckBox46.Enabled = True
CheckBox47 = False
CheckBox47.Enabled = True
CheckBox48 = False
CheckBox48.Enabled = True
CheckBox49 = False
CheckBox49.Enabled = True
CheckBox50 = False
CheckBox50.Enabled = True
CheckBox51 = False
CheckBox51.Enabled = True
CheckBox52 = False
CheckBox52.Enabled = True
CheckBox53 = False
CheckBox53.Enabled = True
CheckBox54 = False
CheckBox54.Enabled = True
CheckBox55 = False
CheckBox55.Enabled = True
CheckBox56 = False
CheckBox56.Enabled = True
CheckBox57 = False
CheckBox57.Enabled = True
CheckBox58 = False
CheckBox58.Enabled = True
CheckBox59 = False
CheckBox59.Enabled = True
CheckBox60 = False
CheckBox60.Enabled = True
CheckBox61 = False
CheckBox61.Enabled = True
CheckBox62 = False
CheckBox62.Enabled = True
CheckBox63 = False
CheckBox63.Enabled = True
CheckBox64 = False
CheckBox64.Enabled = True
CheckBox65 = False
CheckBox65.Enabled = True
CheckBox66 = False
CheckBox66.Enabled = True
CheckBox67 = False
CheckBox67.Enabled = True
CheckBox68 = False
CheckBox68.Enabled = True
CheckBox69 = False
CheckBox69.Enabled = True
End If
End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
In your case, you could try something like this (I assumed UserForm1 is where these checkboxes and textboxes are):

Code: 
Private Sub CheckBox1_Click()
  Dim i As Long
  
  If CheckBox1 = True Then
    For i = 1 To 67
      UserForm1.Controls("Textbox" & i + 67).Value = UserForm1.Controls("Textbox" & i).Value
      UserForm1.Controls("Textbox" & i + 67).Enabled = False
      
      If i = 11 Or i = 17 Or i = 23 Or i = 29 Then i = i + 1
    Next i
    
    TextBox263.Value = TextBox262.Value
    TextBox263.Enabled = False
    
    For i = 2 To 69
      UserForm1.Controls("CheckBox" & i).Value = True
      UserForm1.Controls("CheckBox" & i).Enabled = False
      
      If i = 12 Or i = 14 Or i = 18 Or i = 24 Or i = 30 Then i = i + 1
    Next i
  Else
    For i = 68 To 134
      UserForm1.Controls("TextBox" & i).Value = "0"
      UserForm1.Controls("TextBox" & i).Enabled = True
      
      If i = 78 Or i = 84 Or i = 90 Then i = i + 1
    Next i

    TextBox263.Enabled = True
    TextBox263.Value = "0"
    
    For i = 2 To 69
      UserForm1.Controls("CheckBox" & i).Value = False
      UserForm1.Controls("CheckBox" & i).Enabled = True
      
      If i = 12 Or i = 18 Or i = 24 Or i = 30 Then i = i + 1
    Next i
  End If
End Sub
 
Upvote 0
Why so many controls?

Perhaps as well as looking into cutting down the code you should look into that.:)
 
Upvote 0
Make sure you test it thoroughly to make sure all the correct fields are being enabled/disabled. I did my best to account for the instances where you skip numbers, but may have missed some.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Excel userform Run-time error '21474178 (80010108)': Automation error Object invoked has disconnected from its clients
Replies
1
Views
4K
Hermanito
Hermanito

Forum statistics

Threads
1,216,077
Messages
6,128,679
Members
449,463
Latest member
Jojomen56

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top