How to shorten this code.

[HYKE]

Hi,

How to shorten this code. I have 35 ABT's to copy this same function.I am still at ABT 4, but it seems to me that the code is already long and huge. Is there any way the code can be shorten?

Private Sub CommandButton1_Click()
If ABT1.BackColor = vbGreen Then
   Worksheets("Plan").Range("C3").Value = Label7.Caption
   Worksheets("Plan").Range("D3").Value = Label9.Caption
   Worksheets("Plan").Range("E3").Value = TextBox3.Value
Else
   Worksheets("Plan").Range("E3").Value = "No Ullage"
   
End If
If ABT2.BackColor = vbGreen Then
   Worksheets("Plan").Range("C4").Value = Label10.Caption
   Worksheets("Plan").Range("D4").Value = Label12.Caption
   Worksheets("Plan").Range("E4").Value = TextBox4.Value
Else
   Worksheets("Plan").Range("E4").Value = "No Ullage"
   
End If
If ABT3.BackColor = vbGreen Then
   Worksheets("Plan").Range("C5").Value = Label13.Caption
   Worksheets("Plan").Range("D5").Value = Label15.Caption
   Worksheets("Plan").Range("E5").Value = TextBox7.Value
Else
   Worksheets("Plan").Range("E5").Value = "No Ullage"
   
End If
If ABT4.BackColor = vbGreen Then
   Worksheets("Plan").Range("C6").Value = Label16.Caption
   Worksheets("Plan").Range("D6").Value = Label18.Caption
   Worksheets("Plan").Range("E6").Value = TextBox10.Value
Else
   Worksheets("Plan").Range("E6").Value = "No Ullage"
   
End If
If ABT4.BackColor = vbGreen Then
   Worksheets("Plan").Range("C7").Value = Label19.Caption
   Worksheets("Plan").Range("D7").Value = Label21.Caption
   Worksheets("Plan").Range("E7").Value = TextBox13.Value
Else
   Worksheets("Plan").Range("E7").Value = "No Ullage"
   
End If
End Sub

 

[LufiaMaxim]

It would look something like this:

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("Plan")
    For i = 1 To 35
        If ABT & i & .BackColor = vbGreen Then
            ws.Range("C" & i + 2).value = Label & 4 + (i * 3) & .Caption
            ws.Range("D" & i + 2).value = Label & 6 + (i * 3).Caption
            ws.Range("E" & i + 2).value = TextBoxN.value
        Else
            ws.Range("E" & i + 2).value = "No Ullage"
        End If
    Next i
End Sub

I couldn't find a consistency with your Text Labels, since for ABT1 it's TextBox3, then next one it's TextBox4, THEN it starts going up by 3. If you change the first textbox3 to textbox1, you can apply the same formula to the textboxes.

Hopefully this code will get you started in the right direction, though.

 

[HYKE]

Hi Lufia,

Thanks for the reply. However your code is giving me an unqualified error and it highlights .backcolor.

With regards to label and textboxes, I have not change the name as it is too many to change. However I will try your suggestion to name it after the ABT's.

 

[LufiaMaxim]

Hm, the error is appearing at this line?

        If ABT & i & .BackColor = vbGreen Then

To me it seems like that should work, but I'm no excel Guru like some people on this board.


Looking at my last post I noticed I left out a concatenation on the Label.Caption line, but that doesn't seem to be your issue anyway.

However, your code can be made significantly shorter since you (for the most part) have a consistent numbering pattern, so it should easily be possible to accomplish with a loop. I thought my code would've worked, but apparently not, so maybe someone else can fix up my code and get you a working sample.

 

[Norie]

Where are all the controls located?

Wherver they are I'm afraid you can't refer to them like this:

ABT & I

If they are on a userform you can use something similar:

Me.Controls("ABT" & I).BackColor = vbGreen