How to shorten this VBA code

[Dark0Prince]

I have more boxes to come and need to know how I can shorten this to one code include all textboxes instead of a new sub for every text box that is added. below is the 177th time I had to write it

 Private Sub Textbox177_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   If TextBox177.BackColor = -2147483643 Then
      TextBox177.BackColor = vbRed
   ElseIf TextBox177.BackColor = vbRed Then
      TextBox177.BackColor = vbYellow
   ElseIf TextBox177.BackColor = vbYellow Then
      TextBox177.BackColor = vbGreen
   ElseIf TextBox177.BackColor = vbGreen Then
      TextBox177.BackColor = -2147483643
   End If
End Sub

 

[MickG]

Perhaps something like this, you still need a separate sub, but a bit shorter.
NB:- You could also use a Class Module to achieve the same in one go, when you activate the sheet !!

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
color TextBox1
End Sub

Private Sub TextBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
color TextBox2
End Sub

Private Sub TextBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
color TextBox3
End Sub

Sub color(Ob As Object)
If Ob.BackColor = -2147483643 Then
      Ob.BackColor = vbRed
   ElseIf Ob.BackColor = vbRed Then
      Ob.BackColor = vbYellow
   ElseIf Ob.BackColor = vbYellow Then
      Ob.BackColor = vbGreen
   ElseIf Ob.BackColor = vbGreen Then
      Ob.BackColor = -2147483643
End If
End Sub

 

[Dark0Prince]

I stumbled upon this in google, but don't know how to apply it. I still feel like there should be a way to do this with one sub.

<code> If TypeName(c) = "TextBox"

</code>

 

[MickG]

As I mentioned you can use a Class or you could just loop through all "TextBox"s , but this would change all textboxes , not just the one you select !!!

Your bit of code is just to determine that the object you are selecting is a "TextBox"

 

[Dark0Prince]

I was able to use your code to drag down an autofill in excel
Private Sub TextBox1
color TextBox1
End Sub

Then CONCAT the rest and paste values in another column. Then easily put in VBA editor thanks!
_DblClick(ByVal Cancel As MSForms.ReturnBoolean)