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!

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?

Hi Jack,

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

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

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

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

NewJack,
Any luck with your code? Let us know if we can help with anything.

Cheers,
James

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!

Cool! We'll mark this one case closed, then.