NewYears1978
New member
- Joined
- Feb 19, 2015
- Messages
- 60
- Reaction score
- 0
- Points
- 0
Not sure how to explain this properly.
I have a large Sub of code, that I reuse over and over a portion of it
The below part gets repeated many times
Is there a way to condense this? I tried moving the duplicated part to a module like this
Then I just removed that block and used Call ColorCase
I think this works I just need to figure out how to get the Active Textbox's name and it's text (which is probably something simple..I am striking out)
I trieD:
But that didn't work.. (Dim's Strings, Variables all that stuff I have a bad understanding of..I need to take some classes on it)
I have a large Sub of code, that I reuse over and over a portion of it
Code:
Private Sub TextBoxBorderColor_Afterupdate()
Dim MyColor As Long
Dim oCtrl As Control
Select Case TextBoxBorderColor.Text
Case "0": MyColor = RGB(255, 255, 255)
Case "1": MyColor = RGB(0, 0, 0)
Case "2": MyColor = RGB(255, 255, 255)
Case "3": MyColor = RGB(255, 0, 0)
Case "4": MyColor = RGB(0, 255, 0)
Case "5": MyColor = RGB(0, 0, 255)
Case "6": MyColor = RGB(255, 255, 0)
Case "7": MyColor = RGB(255, 0, 255)
Case "8": MyColor = RGB(0, 255, 255)
Case "9": MyColor = RGB(128, 0, 0)
Case "10": MyColor = RGB(0, 128, 0)
Case "11": MyColor = RGB(0, 0, 128)
Case "12": MyColor = RGB(128, 128, 0)
Case "13": MyColor = RGB(128, 0, 128)
Case "14": MyColor = RGB(0, 128, 128)
Case "15": MyColor = RGB(192, 192, 192)
Case "16": MyColor = RGB(128, 128, 128)
Case "17": MyColor = RGB(153, 153, 255)
Case "18": MyColor = RGB(153, 51, 102)
Case "19": MyColor = RGB(255, 255, 204)
Case "20": MyColor = RGB(204, 255, 255)
Case "21": MyColor = RGB(102, 0, 102)
Case "22": MyColor = RGB(255, 128, 128)
Case "23": MyColor = RGB(0, 102, 204)
Case "24": MyColor = RGB(204, 204, 255)
Case "25": MyColor = RGB(0, 0, 128)
Case "26": MyColor = RGB(255, 0, 255)
Case "27": MyColor = RGB(255, 255, 0)
Case "28": MyColor = RGB(0, 255, 255)
Case "29": MyColor = RGB(128, 0, 128)
Case "30": MyColor = RGB(128, 0, 0)
Case "31": MyColor = RGB(0, 128, 128)
Case "32": MyColor = RGB(0, 0, 255)
Case "33": MyColor = RGB(0, 204, 255)
Case "34": MyColor = RGB(204, 255, 255)
Case "35": MyColor = RGB(204, 255, 204)
Case "36": MyColor = RGB(255, 255, 153)
Case "37": MyColor = RGB(153, 204, 255)
Case "38": MyColor = RGB(255, 153, 204)
Case "39": MyColor = RGB(204, 153, 255)
Case "40": MyColor = RGB(255, 204, 153)
Case "41": MyColor = RGB(51, 102, 255)
Case "42": MyColor = RGB(51, 204, 204)
Case "43": MyColor = RGB(153, 204, 0)
Case "44": MyColor = RGB(255, 204, 0)
Case "45": MyColor = RGB(255, 153, 0)
Case "46": MyColor = RGB(255, 102, 0)
Case "47": MyColor = RGB(102, 102, 153)
Case "48": MyColor = RGB(150, 150, 150)
Case "49": MyColor = RGB(0, 51, 102)
Case "50": MyColor = RGB(51, 153, 102)
Case "51": MyColor = RGB(0, 51, 0)
Case "52": MyColor = RGB(51, 51, 0)
Case "53": MyColor = RGB(153, 51, 0)
Case "54": MyColor = RGB(153, 51, 102)
Case "55": MyColor = RGB(51, 51, 153)
Case "56": MyColor = RGB(51, 51, 51)
Case Else: Exit Sub
End Select
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "TextBox" Or TypeName(oCtrl) = "Label" Or TypeName(oCtrl) = "Image" Then
If oCtrl.Tag <> "RouteButtons" Then
oCtrl.BorderColor = MyColor
End If
End If
Next oCtrl
Worksheets("Options").Range("A4").Value = TextBoxBorderColor.Value
End Sub
The below part gets repeated many times
Code:
Select Case TextBoxBorderColor.Text
Case "0": MyColor = RGB(255, 255, 255)
Case "1": MyColor = RGB(0, 0, 0)
Case "2": MyColor = RGB(255, 255, 255)
Case "3": MyColor = RGB(255, 0, 0)
Case "4": MyColor = RGB(0, 255, 0)
Case "5": MyColor = RGB(0, 0, 255)
Case "6": MyColor = RGB(255, 255, 0)
Case "7": MyColor = RGB(255, 0, 255)
Case "8": MyColor = RGB(0, 255, 255)
Case "9": MyColor = RGB(128, 0, 0)
Case "10": MyColor = RGB(0, 128, 0)
Case "11": MyColor = RGB(0, 0, 128)
Case "12": MyColor = RGB(128, 128, 0)
Case "13": MyColor = RGB(128, 0, 128)
Case "14": MyColor = RGB(0, 128, 128)
Case "15": MyColor = RGB(192, 192, 192)
Case "16": MyColor = RGB(128, 128, 128)
Case "17": MyColor = RGB(153, 153, 255)
Case "18": MyColor = RGB(153, 51, 102)
Case "19": MyColor = RGB(255, 255, 204)
Case "20": MyColor = RGB(204, 255, 255)
Case "21": MyColor = RGB(102, 0, 102)
Case "22": MyColor = RGB(255, 128, 128)
Case "23": MyColor = RGB(0, 102, 204)
Case "24": MyColor = RGB(204, 204, 255)
Case "25": MyColor = RGB(0, 0, 128)
Case "26": MyColor = RGB(255, 0, 255)
Case "27": MyColor = RGB(255, 255, 0)
Case "28": MyColor = RGB(0, 255, 255)
Case "29": MyColor = RGB(128, 0, 128)
Case "30": MyColor = RGB(128, 0, 0)
Case "31": MyColor = RGB(0, 128, 128)
Case "32": MyColor = RGB(0, 0, 255)
Case "33": MyColor = RGB(0, 204, 255)
Case "34": MyColor = RGB(204, 255, 255)
Case "35": MyColor = RGB(204, 255, 204)
Case "36": MyColor = RGB(255, 255, 153)
Case "37": MyColor = RGB(153, 204, 255)
Case "38": MyColor = RGB(255, 153, 204)
Case "39": MyColor = RGB(204, 153, 255)
Case "40": MyColor = RGB(255, 204, 153)
Case "41": MyColor = RGB(51, 102, 255)
Case "42": MyColor = RGB(51, 204, 204)
Case "43": MyColor = RGB(153, 204, 0)
Case "44": MyColor = RGB(255, 204, 0)
Case "45": MyColor = RGB(255, 153, 0)
Case "46": MyColor = RGB(255, 102, 0)
Case "47": MyColor = RGB(102, 102, 153)
Case "48": MyColor = RGB(150, 150, 150)
Case "49": MyColor = RGB(0, 51, 102)
Case "50": MyColor = RGB(51, 153, 102)
Case "51": MyColor = RGB(0, 51, 0)
Case "52": MyColor = RGB(51, 51, 0)
Case "53": MyColor = RGB(153, 51, 0)
Case "54": MyColor = RGB(153, 51, 102)
Case "55": MyColor = RGB(51, 51, 153)
Case "56": MyColor = RGB(51, 51, 51)
Case Else: Exit Sub
End Select
Is there a way to condense this? I tried moving the duplicated part to a module like this
Code:
Select Case TextBoxBorderColor.Text <---- Not sure what to put here to get the Current active textbox's text
Case "0": MyColor = RGB(255, 255, 255)
Case "1": MyColor = RGB(0, 0, 0)
Case "2": MyColor = RGB(255, 255, 255)
Case "3": MyColor = RGB(255, 0, 0)
Case "4": MyColor = RGB(0, 255, 0)
Case "5": MyColor = RGB(0, 0, 255)
Case "6": MyColor = RGB(255, 255, 0)
Case "7": MyColor = RGB(255, 0, 255)
Case "8": MyColor = RGB(0, 255, 255)
Case "9": MyColor = RGB(128, 0, 0)
Case "10": MyColor = RGB(0, 128, 0)
Case "11": MyColor = RGB(0, 0, 128)
Case "12": MyColor = RGB(128, 128, 0)
Case "13": MyColor = RGB(128, 0, 128)
Case "14": MyColor = RGB(0, 128, 128)
Case "15": MyColor = RGB(192, 192, 192)
Case "16": MyColor = RGB(128, 128, 128)
Case "17": MyColor = RGB(153, 153, 255)
Case "18": MyColor = RGB(153, 51, 102)
Case "19": MyColor = RGB(255, 255, 204)
Case "20": MyColor = RGB(204, 255, 255)
Case "21": MyColor = RGB(102, 0, 102)
Case "22": MyColor = RGB(255, 128, 128)
Case "23": MyColor = RGB(0, 102, 204)
Case "24": MyColor = RGB(204, 204, 255)
Case "25": MyColor = RGB(0, 0, 128)
Case "26": MyColor = RGB(255, 0, 255)
Case "27": MyColor = RGB(255, 255, 0)
Case "28": MyColor = RGB(0, 255, 255)
Case "29": MyColor = RGB(128, 0, 128)
Case "30": MyColor = RGB(128, 0, 0)
Case "31": MyColor = RGB(0, 128, 128)
Case "32": MyColor = RGB(0, 0, 255)
Case "33": MyColor = RGB(0, 204, 255)
Case "34": MyColor = RGB(204, 255, 255)
Case "35": MyColor = RGB(204, 255, 204)
Case "36": MyColor = RGB(255, 255, 153)
Case "37": MyColor = RGB(153, 204, 255)
Case "38": MyColor = RGB(255, 153, 204)
Case "39": MyColor = RGB(204, 153, 255)
Case "40": MyColor = RGB(255, 204, 153)
Case "41": MyColor = RGB(51, 102, 255)
Case "42": MyColor = RGB(51, 204, 204)
Case "43": MyColor = RGB(153, 204, 0)
Case "44": MyColor = RGB(255, 204, 0)
Case "45": MyColor = RGB(255, 153, 0)
Case "46": MyColor = RGB(255, 102, 0)
Case "47": MyColor = RGB(102, 102, 153)
Case "48": MyColor = RGB(150, 150, 150)
Case "49": MyColor = RGB(0, 51, 102)
Case "50": MyColor = RGB(51, 153, 102)
Case "51": MyColor = RGB(0, 51, 0)
Case "52": MyColor = RGB(51, 51, 0)
Case "53": MyColor = RGB(153, 51, 0)
Case "54": MyColor = RGB(153, 51, 102)
Case "55": MyColor = RGB(51, 51, 153)
Case "56": MyColor = RGB(51, 51, 51)
Case Else: Exit Sub
End Select
Then I just removed that block and used Call ColorCase
I think this works I just need to figure out how to get the Active Textbox's name and it's text (which is probably something simple..I am striking out)
I trieD:
Code:
Dim WhatsMyName As Long
ActiveControl.Name = WhatsMyName
Select Case WhatsMyName.Text
But that didn't work.. (Dim's Strings, Variables all that stuff I have a bad understanding of..I need to take some classes on it)
Last edited: