Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Conditional Formatting (Office 97 SR2)

    Hi

    Is there any way to conditional format a field to produce red text when a certain word appears.

    It is calculated field which will either produce Delivered or Overdue I would like Overdue to turn red.


    I know this is simple in later versions but for now I have only 97


    Many Thanks
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Office 97 SR2)

    The following will work in a single form, not in a continuous form - there it would change the color of the text in all records simultaneously.

    In the module behind the form, create the following function:

    Private Function HandleFormat()
    If txtStatus = "Overdue" Then
    txtStatus.ForeColor = vbRed
    Else
    txtStatus.ForeColor = vbBlack
    End If
    End Function

    where txtStatus should be replaced by the name of the text box displaying either Delivered or Overdue.

    Call this function from the OnCurrent event of the form and from the AfterUpdate event of each control bound to a field that influences the Delivered/Overdue status. You can do this in one of the following two ways:

    1. Call the function from the event procedure, for instance

    Private Sub Form_Current()
    HandleFormat
    End Sub

    This way, you'll have to create separate event procedures for the OnCurrent event of the form and for the AfterUpdate event of each of the controls influencing the status.

    2. Type =HandleFormat() in the Properties window in the OnCurrent event of the form and in the AfterUpdate event of the appropriate controls. This way, you write less code, but thereby makes your code less self-documenting.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi Hans thanks for the response

    Being a novice I am not sure I fully Understand it. But I will give it a try and let you know how I get on.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi Hans

    No succes I'm afraid I think I am too much of a novice to know where to put details you have sent me.

    But thanks anyway

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Office 97 SR2)

    Brad.

    (Not to step on any-ones toes)

    Heres a little demp based on Hans's earlier post.
    The text box is called txtStatus
    Check the properties of the after update of the combo and the forms current event, I think you will see how it's done.
    The form Module resides with the form and each time you want to implement it, you can call it from an event procedure within the form.

    Post back if you still have a prob or even post a short demo of yours.(Less than 100k - zipped)

    Hope this helps.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (Office 97 SR2)

    Hi Braddy,

    I hope you haven't given up. Dave Willett's demo shows exactly what I meant - if you open the form and its module, you'll see where and how the ForeColor (i.e. text color) of the text box is modified. It shouldn't be difficult to apply the same ideas in your form - you can even copy bits of code from Dave's demo to your database.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi Dave

    I Have attached my Database because I could not relate your demo to my database.

    Have a look and see what you think. Please bear in mind I am a novice trying to improve.

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Office 97 SR2)

    Brad.
    I'll try to do this step by step.

    1.First of all, open the mainform in design view.
    2. In the menu at the top, click the multi coloured square ring.This will bring up the VB editor.(This is the module behind the form.)
    3. Directly underneath "Option Compare Database" put the following:

    Private Function HandleFormat()
    If Status = "Overdue" Then
    Status.ForeColor = vbRed
    Else
    Status.ForeColor = vbBlack
    End If
    End Function

    4.File|Save and close the editor.
    5.Click on your [Date Sent] text box and open its properties (Right Click - Properties)
    6.Where it says "After Update", select [Event Procedure] and press the ... (Three Dots)
    7.You should now be back in the VB editor and the cursor should read:
    Private Sub Date_Sent_AfterUpdate()

    Directly under this, put

    HandleFormat

    End sub should already follow this.

    8.Again, File|Save and close the editor.
    9.At the top left of your form should be a Square , above left of the detail bar.Click to put a dot in it.
    Again right click (In the square)and properties. Go to the event tab, you should see On Current.
    Again choose [Event Procedure] and click the three dots.
    You're back in the VB Editor.
    Under the line which says : Private Sub Form_Current() put

    HandleFormat

    Save and exit once again.

    This should be all you need to do.

    Try it and see what happens.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Office 97 SR2)

    Brad

    I've noticed your field names have spaces in them.
    This can be dangerous and cause complications later on.
    Read up on Naming Conventions, It'll be too late to remedy some problems later on.

    Order Number would be better as OrderNumber
    Date Sent would be better as DateSent

    etc etc etc

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi Hans

    I followed Dave step by step and it worked fine. Once again I am overwhelmed by the patience and help given by Loungers

    Grateful Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi Dave

    Thanks the step by step worked fine. I can see I have a lot to learn but with this printed out now I can study it. I take your point about spaces in names. I shall endeavour to avoid it in the future. I take it would not be advisable to change them now.

    Once again my thanks are inadequate to you for your help.

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (Office 97 SR2)

    Brad, Hang on in there mate.

    It's really up to you if you want to change your field names at this point.
    It depends how far you have got with you db and whether you have got the time to do so.

    We continue to learn more and more each day, and without the help of some of the guy's and gal's here on the site, it would be almost impossible.

    I continue to write my db going 2 steps forward and 3 back.

    It's the same old saying:

    If I knew then, what I know now <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    It's a pleasure to help.
    Goodluck with the rest of your project.

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting (Office 97 SR2)

    Hi Dave

    Thanks for the encouragement!!
    If you are a fool at forty, you will always be a fool

Posting Permissions

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