Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 by NewYears1978; 2015-02-22 at 06:17 PM.

  2. #2
    Woops I meant to use String not Long.

  3. #3
    I also tried it this way

    Code:
        Select Case TextBoxBorderColor.Text
              Call ColorCodes
            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?

  4. #4
    Code:
    MyColor = GetRGB(Val(TextBoxBorderColor.Text))
    
    '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

  5. #5
    Ohhh that makes sense! Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •