none
VBA access text color

    Question

  • Hi everybody I'm not very good at VBA and I'm trying to edit a code to change text color in a ACCESS report depending on the letter I type in, for example when I write G the letter becomes Green and when I write R the letter becomes Red and so on, Thanks in advance if anybody can help me

    Louis-A

    Tuesday, June 15, 2010 2:46 PM

Answers

  • You can use Conditional formating, if you have a very limited number of color. As example, printing in green if the value (text) is less than "H"  (ie. Giorgio < H , so Giorgio will be in green), in red if less than "S",  black otherwise.

    Otherwise, change the ForeColor property in the print event:

     

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
        Me.Text0.ForeColor = 0 ' black
        If Left(Me.Text0, 1) = "G" Then Me.Text0.ForeColor = RGB(0, 255, 0)
        ...
    End Sub

     

    Vanderghast, Access MVP

     

    • Marked as answer by Tim Li Tuesday, June 22, 2010 7:32 AM
    Tuesday, June 15, 2010 5:27 PM
    Moderator

All replies

  • You can use Conditional formating, if you have a very limited number of color. As example, printing in green if the value (text) is less than "H"  (ie. Giorgio < H , so Giorgio will be in green), in red if less than "S",  black otherwise.

    Otherwise, change the ForeColor property in the print event:

     

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
        Me.Text0.ForeColor = 0 ' black
        If Left(Me.Text0, 1) = "G" Then Me.Text0.ForeColor = RGB(0, 255, 0)
        ...
    End Sub

     

    Vanderghast, Access MVP

     

    • Marked as answer by Tim Li Tuesday, June 22, 2010 7:32 AM
    Tuesday, June 15, 2010 5:27 PM
    Moderator
  • Thank you very much Vanderghast

    Is it possible to activate the macro when I open the Report and should this code refer to the table and the specific column where the data is held

    Thanks a lot for your help 

    Louis-A

     

    Tuesday, June 15, 2010 8:03 PM
  • If you proceed with VBA code, that code is behind the report itself and so, you don't have to activate it in any way short of viewing the report in preview or for printing.

     

    Text0  in the supplied example represents the text box control added in the report which is, I assume, itself bound to the data in the table, but sure, you will have to use the appropriate control name, in your case.

     

     

    Vanderghast, Access MVP

    Wednesday, June 16, 2010 1:41 PM
    Moderator
  • Here is what I have yet but It doesn't work if somebody can help me it would be very appreciated

    Private Sub report_Open(Cancel As Integer)

    Dim db As DAO.Database      'Current database.
        Dim tdf As DAO.TableDef     'Table nominated in argument.
        Dim fld As DAO.Field        'Each field.
        Dim strCaption As String    'Field caption.
        Dim strErrMsg As String     'Responses and error messages.
        Dim SQL As String
        Dim couleur As String
        'Initalize.
        Set db = CurrentDb()
        Set tdf = db.TableDefs(équipement)
        SQL = "select*from équipement"
        Set rs = db.OpenRecordset(SQL)
        rs.MoveFirst
        couleur = rs.Fields!Codedecouleurflowsheet
           

        Me.Codedecouleurflowsheet.ForeColor = 0 ' black
        If Left(Me.Codedecouleurflowsheet, 1) = "V" Then Me.Codedecouleurflowsheet.ForeColor = RGB(0, 255, 0)
        If Left(Me.Codedecouleurflowsheet, 1) = "R" Then Me.Codedecouleurflowsheet.ForeColor = RGB(255, 0, 0)
        If Left(Me.Codedecouleurflowsheet, 1) = "M" Then Me.Codedecouleurflowsheet.ForeColor = RGB(255, 0, 255)
        End If
       
       
    End Sub

    Thanks

     

     

    Wednesday, June 16, 2010 1:46 PM
  • You should use the Print Detail event, NOT the report Open event.

    Indeed, EACH detail, when it has to be printed, has to go trough the logic of getting the right color, so each time a detail is printed, not only once, when the report starts.

    In this print detail event, you have already access to the values to be printed, so you don't need to open a recordset to get them. Just use  Me.Codedecouleurflowsheet for your tests, assuming your text controls, on your report, have the same name than the field.

    All your if-s fit in one line, there should be no need for a final End If. So, you have much more code than required, but mostly, in the wrong event :-)

    Wednesday, June 23, 2010 9:16 PM
    Moderator