Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    5

    Question Unanswered: Conditional Formatting Extension

    I’d like to code the “Office” field on my report a different color by “Office” for 6 offices. I’ve used the conditional formatting option for the first three. Does anyone know how to setup code for the 6 potential conditions? Also, could you provide the VBA coding for the colors with bolding? I’d like to use black, red, blue, green, orange, and brown. Any assistance that you all could provide would be greatly appreciated. Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    I'm not quite sure what you atually mean by "colors with bolding" I'm assuming that what you mean is that you what the font to be Bold.

    Place the following code into the OnFormat event for the section that contains the "Office" name field. Change the name in red italic to the actual control name that will contain the office name and change the items in italic blue to the 6 different possible office names:

    Code:
    	Const myOrange = 33023
    	Const myBlack = vbBlack
    	Const myRed = vbRed
    	Const myBlue = vbBlue
    	Const myGreen = vbGreen
    	Const myBrown = 27090
    	Dim Colr As Long
     
    	Me.myOfficeFiledName.FontBold = True
    	Select Case Me.myOfficeFiledName
    	   Case "OfficeName1"
    		  Colr = myBlack
    	   Case "OfficeName2"
    		  Colr = myRed
    	   Case "OfficeName3"
    		  Colr = myOrange
    	   Case "OfficeName4"
    		  Colr = myBlue
    	   Case "OfficeName5"
    		  Colr = myGreen
    	   Case "OfficeName6"
    		  Colr = myBrown
    	   Case Else
    		  Me.myOfficeFiledName.FontBold = False
    		  Colr = myBlack
    	End Select
    	Me.myOfficeFiledName.ForeColor = Colr
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Oct 2005
    Posts
    5

    Smile One More Question

    I appreciated the rapid response! Your suggestion worked.

    Unfortunately, I didn’t ask the correct question.

    Ideally, I’d like to change the color of a second field (e.g. event) based on the office that is coordinating the event. Is there anyway to format the text color and bolding proterties of the “event” field based on the six offices?

  4. #4
    Join Date
    Oct 2005
    Posts
    5

    Talking Answer?

    I think I figured it out..

    Do you see any problems with the following code:


    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Const myOrange = 33023
    Const myBlack = vbBlack
    Const myRed = vbRed
    Const myBlue = vbBlue
    Const myGreen = vbGreen
    Const myBrown = 27090

    Me.[Event Text].FontBold = True
    If Office.Value = OOSA Then
    Colr = myBlack
    ElseIf Office.Value = OSBA Then
    Colr = myBlue
    ElseIf Office.Value = OOC Then
    Colr = myGreen
    ElseIf Office.Value = "OOSA/OSBA" Then
    Colr = myOrange
    ElseIf Office.Value = "OPR" Then
    Colr = myRed
    ElseIf Office.Value = "OOSA/OOC" Then
    Colr = myBrown
    End If

    Me.[Event Text].ForeColor = Colr
    End Sub



    It works, but I'm not sure if I'm going to run into problems later on down the road.

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    The same priciple can apply to any control in your report. The Select Case method provided to you earlier should work fine. I find it somewhat a cleaner method of doing things. the following is the same as your code:

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
       Const myOrange = 33023
       Const myBlack = vbBlack
       Const myRed = vbRed
       Const myBlue = vbBlue
       Const myGreen = vbGreen
       Const myBrown = 27090
    
       Me.[Event Text].FontBold = True
       Select Case Me.Office.Value
    	  Case "OOSA"
    		 Colr = myBlack
    	  Case "OSBA"
    		 Colr = myBlue
    	  Case "OOC"
    		 Colr = myGreen
    	  Case "OOSA/OSBA"
    		 Colr = myOrange
    	  Case "OPR"
    		 Colr = myRed
    	  Case "OOSA/OOC"
    		 Colr = myBrown
       End Select
       Me.[Event Text].ForeColor = Colr
    End Sub
    It works, but I'm not sure if I'm going to run into problems later on down the road.
    What sort of troubles would you anticipate?

    One perhaps would be the fact that Office names can change or be added and even perhaps be removed from the database. If this is the case, then the code would need to be modified so as to enumerate through all the possible available office names that may be located within a table and play them against your color scheme.

    The code above is only good for definate office names per say.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    Oct 2005
    Posts
    5

    Smile

    Your second suggestion works and is a lot cleaner. Thanks for the help!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Minor addition to Cyberynxs Case statement
    there is no case else statement, so if the office is not in that list then it will default to an unspecified colour. I'd suggest you extend the case statement to put in a catchall warning colour

Posting Permissions

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