Within a Sub - Calling a large portion of code from a module?

NewYears1978

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

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:
I also tried it this way

Code: 
    Select Case TextBoxBorderColor.Text
[B]          Call ColorCodes[/B]
        Case Else: Exit Sub
    End Select

Code: 
Public Sub ColorCodes()


        Case "0": MyColor = RGB(255, 255, 255)
        Case "56": MyColor = RGB(51, 51, 51)
End Sub

But I get an error when it calls ColorCodes because it expects case. Is there no way to make it simple "paste" in the code from a module instead of reading it as it's own code?
 
Code: 
MyColor = GetRGB(Val([COLOR=#333333]TextBoxBorderColor.Text))[/COLOR]

'and so on

Function GetRGB(ByVal colorvalue As Long) As Long
    Select Case colorvalue
        Case 0: GetRGB = RGB(255, 255, 255)
        Case 1: GetRGB = RGB(0, 0, 0)
        Case 2: GetRGB = RGB(255, 255, 255)
        Case 3: GetRGB = RGB(255, 0, 0)
        Case 4: GetRGB = RGB(0, 255, 0)
        Case 5: GetRGB = RGB(0, 0, 255)
        Case 6: GetRGB = RGB(255, 255, 0)
        Case 7: GetRGB = RGB(255, 0, 255)
        Case 8: GetRGB = RGB(0, 255, 255)
        Case 9: GetRGB = RGB(128, 0, 0)
        Case 10: GetRGB = RGB(0, 128, 0)
        Case 11: GetRGB = RGB(0, 0, 128)
        Case 12: GetRGB = RGB(128, 128, 0)
        Case 13: GetRGB = RGB(128, 0, 128)
        Case 14: GetRGB = RGB(0, 128, 128)
        Case 15: GetRGB = RGB(192, 192, 192)
        Case 16: GetRGB = RGB(128, 128, 128)
        Case 17: GetRGB = RGB(153, 153, 255)
        Case 18: GetRGB = RGB(153, 51, 102)
        Case 19: GetRGB = RGB(255, 255, 204)
        Case 20: GetRGB = RGB(204, 255, 255)
        Case 21: GetRGB = RGB(102, 0, 102)
        Case 22: GetRGB = RGB(255, 128, 128)
        Case 23: GetRGB = RGB(0, 102, 204)
        Case 24: GetRGB = RGB(204, 204, 255)
        Case 25: GetRGB = RGB(0, 0, 128)
        Case 26: GetRGB = RGB(255, 0, 255)
        Case 27: GetRGB = RGB(255, 255, 0)
        Case 28: GetRGB = RGB(0, 255, 255)
        Case 29: GetRGB = RGB(128, 0, 128)
        Case 30: GetRGB = RGB(128, 0, 0)
        Case 31: GetRGB = RGB(0, 128, 128)
        Case 32: GetRGB = RGB(0, 0, 255)
        Case 33: GetRGB = RGB(0, 204, 255)
        Case 34: GetRGB = RGB(204, 255, 255)
        Case 35: GetRGB = RGB(204, 255, 204)
        Case 36: GetRGB = RGB(255, 255, 153)
        Case 37: GetRGB = RGB(153, 204, 255)
        Case 38: GetRGB = RGB(255, 153, 204)
        Case 39: GetRGB = RGB(204, 153, 255)
        Case 40: GetRGB = RGB(255, 204, 153)
        Case 41: GetRGB = RGB(51, 102, 255)
        Case 42: GetRGB = RGB(51, 204, 204)
        Case 43: GetRGB = RGB(153, 204, 0)
        Case 44: GetRGB = RGB(255, 204, 0)
        Case 45: GetRGB = RGB(255, 153, 0)
        Case 46: GetRGB = RGB(255, 102, 0)
        Case 47: GetRGB = RGB(102, 102, 153)
        Case 48: GetRGB = RGB(150, 150, 150)
        Case 49: GetRGB = RGB(0, 51, 102)
        Case 50: GetRGB = RGB(51, 153, 102)
        Case 51: GetRGB = RGB(0, 51, 0)
        Case 52: GetRGB = RGB(51, 51, 0)
        Case 53: GetRGB = RGB(153, 51, 0)
        Case 54: GetRGB = RGB(153, 51, 102)
        Case 55: GetRGB = RGB(51, 51, 153)
        Case 56: GetRGB = RGB(51, 51, 51)
        Case Else:
    End Select
End Function
 
You must log in or register to reply here.
Back
Top