?
Solved

Setting Back Colors

Posted on 2007-10-10
13
Medium Priority
?
311 Views
Last Modified: 2012-05-05
I have a tab control on a main form with one of the tabs as "Levels". On this tab are five combo boxes, one for each level and five text boxes where the user can type in a level description. The combo boxes contain colors as text (Red,Green,Yellow,Orange,White,None)

When the user selects a color, the backcolor of the combobox changes to the color selected. All works well. On the main form I have a matrix. (Grid of "cells" that is automatically populated) When the user clicks on one of the fields, it will start looping thru the colors (on each click). This is how they select the level colors for each part of the matrix.

The new requirement is that on click it only loops thru the colors they have defined on the Levels tab. For instance, if they only select "Red,Green,Yellow,None,None", then they want the click to only loop thru Red, green, yellow.

I'm trying to figure out the cleanest way to do this. I'm thinking of using a concatenated string as a variable with all of the colors, then checking that with Instr, but I don't know if I want to have to deal with removing the color if they change. Or I could loop thru the comboboxes and check the backcolors. Anyone have any ideas?

Here is my existing code. First the combobox backcolor change and then the click event for the cells.

-a

Function LevelColor()
' Changes back color of combobox based on user color selection

Dim i As Integer

    i = Screen.ActiveControl.Tag
   
    Select Case Screen.ActiveControl.Value
        Case Is = "Green": Screen.ActiveControl.BackColor = vbGreen
        Case Is = "Orange": Screen.ActiveControl.BackColor = RGB(255, 140, 0)
        Case Is = "Yellow": Screen.ActiveControl.BackColor = vbYellow
        Case Is = "Red": Screen.ActiveControl.BackColor = vbRed
        Case Is = "White": Screen.ActiveControl.BackColor = vbWhite
        Case Is = "None": Screen.ActiveControl.BackColor = vbWhite
    End Select
   
    Me.Controls("txtLevel" & i).SetFocus
   
End Function

Function ColorMe()
' If a user clicks on a cell in the matrix that is not null
' this function will change the backcolor
' it loops thru the colors, changing to the next color
' each time they click
   
    If IsNull(Screen.ActiveControl) Then
        Screen.ActiveControl.BackColor = vbWhite
        Exit Function
    End If
   
    Select Case Screen.ActiveControl.BackColor
        Case Is = vbWhite: Screen.ActiveControl.BackColor = vbRed
        Case Is = vbRed: Screen.ActiveControl.BackColor = vbGreen
        Case Is = vbGreen: Screen.ActiveControl.BackColor = RGB(255, 140, 0)
        Case Is = RGB(255, 140, 0): Screen.ActiveControl.BackColor = vbYellow
        Case Is = vbYellow: Screen.ActiveControl.BackColor = vbWhite
    End Select
   
    Me.Controls("txt" & Screen.ActiveControl.Tag).BackColor = _
        Screen.ActiveControl.BackColor
   
   
End Function
0
Comment
Question by:adraughn
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20050048
can you zip and upload the db? www.ee-stuff.com
0
 
LVL 13

Author Comment

by:adraughn
ID: 20050116
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20050220
have you tried using array?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Author Comment

by:adraughn
ID: 20050244
no, not yet. i haven't exactly figured out how i am going to code the loop.
0
 
LVL 10

Expert Comment

by:aesmike
ID: 20050450
A million ways to skin this cat...
how about creating a private array (at the module level).  When the user select's the possible choices, you regenerate this array with the available colors.

Then in your colorme code, first you find the current color in the array.  Then you skip to the next one (or back to the first if you're at the end of your list).
0
 
LVL 13

Author Comment

by:adraughn
ID: 20050481
hey baby. nice of you to stop by.

<how about creating a private array (at the module level). >

that's what i am working on now. i am struggling with using the colors from the array (white) as a value to set the color (vbWhite).

-a
0
 
LVL 75
ID: 20050588
Hey Mike ... don't be talkin' bout my cats like that !

mx
0
 
LVL 13

Author Comment

by:adraughn
ID: 20050606
hi joe
0
 
LVL 13

Author Comment

by:adraughn
ID: 20050625
this is making my head hurt. i am now working on using a collection. someone help - this is what i have right now:

Function ColorMe()
' If a user clicks on a cell in the matrix that is not null
' this function will change the backcolor
' it loops thru the colors, changing to the next color
' each time they click
   
Dim clr As String
Dim colColors As New Collection, varColor As Variant


    If IsNull(Screen.ActiveControl) Then
        Screen.ActiveControl.BackColor = vbWhite
        Exit Function
    End If
   
    'Populate color string
    For j = 1 To 5
        If Me.Controls("cmbLevel" & j) <> "None" Then
            clr = Me.Controls("cmbLevel" & j)
            colColors.Add clr
        End If
    Next
   
   
   
    Select Case Screen.ActiveControl.BackColor
        Case Is = vbWhite: Screen.ActiveControl.BackColor = vbRed
        Case Is = vbRed: Screen.ActiveControl.BackColor = vbGreen
        Case Is = vbGreen: Screen.ActiveControl.BackColor = RGB(255, 140, 0)
        Case Is = RGB(255, 140, 0): Screen.ActiveControl.BackColor = vbYellow
        Case Is = vbYellow: Screen.ActiveControl.BackColor = vbWhite
    End Select
   
    Me.Controls("txt" & Screen.ActiveControl.Tag).BackColor = _
        Screen.ActiveControl.BackColor
   
End Function

0
 
LVL 10

Accepted Solution

by:
aesmike earned 2000 total points
ID: 20050879
friends don't let other friends use collections...

Try these as your substitute event handlers:

Function ColorMe()
' If a user clicks on a cell in the matrix that is not null
' this function will change the backcolor
' it loops thru the colors, changing to the next color
' each time they click
   
    If IsNull(Screen.ActiveControl) Then
        Screen.ActiveControl.BackColor = vbWhite
        Exit Function
    End If
   
    'new code>>
    Screen.ActiveControl.BackColor = GetNextColor(Screen.ActiveControl)
   
    Me.Controls("txt" & Screen.ActiveControl.Tag).BackColor = _
        Screen.ActiveControl.BackColor
   
   
End Function


Function GetNextColor(ctl As Control)
    Dim x As Integer
    Dim FirstColorLevel
   
    Const MaxLevels = 5
   
    'Determines the next available color defined in the levels
   
    'first, find the first level that has a color defined
   
    For x = 1 To MaxLevels
        If Me("cmblevel" & x).Value <> "None" Then
            FirstColorLevel = x
            Exit For
        End If
    Next
    If x > MaxLevels Then
        MsgBox "No Color Levels Defined."
        GetNextColor = ctl.BackColor
        Exit Function
       
    End If
   
   
   
    'second, find where we are in the color levels
    For x = 1 To MaxLevels
        If ctl.BackColor = Me("cmblevel" & x).BackColor And Me("cmblevel" & x).Value <> "None" Then
            Exit For
        End If
    Next
    If x <= MaxLevels Then
        'go to the next higher level
        x = x + 1
       
        Do While x <= MaxLevels
            If Me("cmblevel" & x).Value <> "None" Then
                Exit Do
            End If
            x = x + 1
        Loop
        If x > MaxLevels Then
            'no more available levels, start back at first available color
            x = FirstColorLevel
        End If
       
    Else
        'current color is not among those defined, go to the first level
        x = FirstColorLevel
    End If
   
   
    GetNextColor = Me("cmblevel" & x).BackColor
   
End Function
0
 
LVL 13

Author Comment

by:adraughn
ID: 20050997
if you were in cincinnati i would give you a big kiss...

thanks mike... works perfect.

-a
0
 
LVL 10

Expert Comment

by:aesmike
ID: 20051056
Since I'm not in Cinci, I guess you'll have to blow me one.
Glad it worked.
0
 
LVL 13

Author Comment

by:adraughn
ID: 20051072
done. :-))
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question