Consulting

Results 1 to 9 of 9

Thread: Solved: Tasks Formula Field

  1. #1

    Solved: Tasks Formula Field

    Hello everyone, I was recommended to check out this site by someone that thought you guys might be able to help me with my problem. I am trying to write some VBA Code that will help me out with a couple of things. The problem is that this is my first time ever making macros. The extent of my knowledge has to do with how to make buttons; I am still working on learning the VBA language. Anyways, here is what I am trying to do:

    1.) I have 3 fields that I customly made in the Tasks portion of Outlook: Bus Days, Cal Days, and End Date. Both Bus Days and Cal Days are fields where the user just enters a number, and End Date is a field which adds the Start Date Field (which is one of the default fields supplied by outlooks), the Bus Days field, and the Cal Days field together to resolve a final date (a due date for something). The thing I noticed though is that if the user leaves either the Bus Days or Cal Days cell blank, then the End Date produces an Error and can't calculate a due date. So, I would like to create a macro that when it sees an empty space in the Bus Days or Cal Days fields, it will put a 0 in that cell.

    2.) If I double-click on one of the tasks, I notice the Start Date and Due Date are both set to whatever is in the Start Date field; it is not reading the formula I am using for my end date field. Is there a way (or macro) to make it so that when you double click on a task to see its properties, it automatically sets the Due Date to my End Date field?

    Any help is greatly appreciated. Thanks in advance for the help!

  2. #2
    Well, I just figured out #1 in terms of Excel but, I dont know how to make it work in Outlook. Here is an example of what I came up with in Excel (it checks everything in A and B and fills the blanks in with 0's):

    [vba]Sub Replace_Blanks()
    For Each i In Worksheets("Sheet1").Range("A2:B" & Range("C65536").End(xlUp).Row)
    If i.Value = "" Then i.Value = 0
    Next

    End Sub[/vba]

    Any ideas?

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi Jack,

    Is this for Outlook or Excel? #1 Excel, #2 Outlook?

  4. #4
    The VBA code was for Excel, but I would like to convert it to Outlook so that it does this in my Tasks section.

  5. #5
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    How about this suggestion? Since you want to default Bus Days or Cal Days to 0 if the user doesn't enter anything, how about just setting the field's default value to 0 in the first place?

    Then you can place the following code in the textbox's KeyPress event to prevent anything but numbers from being entered.

    [vba]
    Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii < vbKey0 Or KeyAscii > vbKey9 Then
    MsgBox "You can only enter numbers in this field."
    End If

    End Sub
    [/vba]

    To set your Due Date to the End Date, place this (generic) code in the DoubleClick event of your task:

    [vba]
    DueDate.Value = EndDate.Value
    [/vba]

    If you want to post the code you're currently using, or a copy of the form, I can give you more specific code for your controls.

    Cheers,
    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  6. #6
    Thanks for the help! I will give that a shot.

  7. #7
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    NewJack,
    Any luck with your code? Let us know if we can help with anything.

    Cheers,
    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  8. #8
    Hey james. Sorry about that, I was getting pretty involved with the stuff I was working on. Anyways, the advice you gave me worked out. Thanks again for the help!

  9. #9
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Cool! We'll mark this one case closed, then.
    "All that's necessary for evil to triumph is for good men to do nothing."

Posting Permissions

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