DateAdd Swaps Format during Increment

Have you tried OnCallTec.TextBox1.Value = Format(DateAdjust, " dd/mm/yyyy")?

Quote from fifijazz

Have you tried OnCallTec.TextBox1.Value = Format(DateAdjust, " dd/mm/yyyy")?

I took the above and tested it and it appears to solve the problem with TextBox1. I could go from 03/10/2004 up to 03/14/2004. However, if I set the date to 05/10/2004, the date in J2 would be 10/05/2004 which is actually May 10, 2004 (changing the date format makes this obvious). By again manipulating the TextBox1 value before assigning it to J2, I was able to get the date entered in J2 correctly. I also added the code to hide OnCallTec when the OK button was clicked.

So my code on OnCallTec form is

Private Sub OK_Click()
OnCallTec.Hide
Call AdvFilterTec
Worksheets("Data").Range("J2") = DateSerial(Right(OnCallTec.TextBox1.Value, 4), Mid(OnCallTec.TextBox1.Value, 4, 2), Left(OnCallTec.TextBox1.Value, 2))
End Sub
Private Sub SpinButton1_Change()
Dim DateAdjust As Date


adjust = OnCallTec.SpinButton1.Value
DateAdjust = DateAdd("d", adjust, OnCallTec.TextBox1.Value)
OnCallTec.TextBox1.Value = Format(DateAdjust, "dd/mm/yyyy")
SpinButton1.Value = 0
End Sub
Private Sub TextBox1_Change()


End Sub
Private Sub UserForm_Initialize()


Call AutoFilter
TextBox1.ControlSource = Worksheets("Data").Range("J2")
TextBox1.Text = Worksheets("Data").Range("I2")
ZoneSelectCombo.SetFocus


Set ZoneRange = Range("Zones")
Nzones = ZoneRange.Rows.Count
    For r = 1 To Nzones
        ZoneSelectCombo.AddItem ZoneRange.Cells(r, 1)
    Next r


ZoneSelectCombo.ListIndex = 0
End Sub

Couple of things I noticed before this code change and after.

If J2 is not blank, you get a Run-time error #380: Could not set the ControlSource property. Invalid property value. Since the value for J2 is already being assigned in Sub OK-Click, and since the ControlSource must contain no data (see VBA Help: Keyword "ControlSource Property" and click on the example line), you should deleteTextBox1.ControlSource = Worksheets("Data").Range("J2") in Sub UserForm_Initialize. Everything works fine without it and the Run-time error #380 no longer occurs.

Also, since you didn't have a procedure to show the OnCallTec form, I assumed you did it the same way I did by running the UserForm_Initialize procedure. Since the form is re-initialized each time because you start with the initialization procedure, there is no problem. But if run the following procedure

Sub test()
Load OnCallTec
OnCallTec.Show
End Sub

and then run it again, OnCallTec is still loaded and is not re-initialized so the date in TextBox1 is the date when the form was hidden. You will have to unload the form and when it is next loaded, the initialization code will be excuted before the form is show.

Sub test()
Load OnCallTec
OnCallTec.Show
Unload OnCallTec
End Sub

Alternatively, rename Private Sub UserForm_Initialize() to Private Sub UserForm_Activate() and the code will be executed whenever the form is activated (shown).

Hope this helps!

Quote from Jasper

Barbara

You aredefinately 'The Cat's Whiskers'

Thanks using the formating worked, I hadn't learnt yet how to get the form displayed, but will read your comments in depth and take it in.

Big Thanks

Display More

Thanks to fifijazz who supplied the first link OnCallTec.TextBox1.Value = Format(DateAdjust, " dd/mm/yyyy").

What I gave you was the procedure but you will still need a non-VBE method to display the form:

• Add a shortcut key to the macro: easiest but your user has to know that the shortcut keys exists, how to use it, and what it is (plus remember what it is)
• Add a button from the Forms toolbar to the worksheet and assign a macro: usually specific to the worksheet on which the button is placed and generally applies only to the workbook in which the button is contained but it is visual - you may still have to inform the user about the function behind the button
• Add an item -- through Excel, customize the Worksheet Menu Bar or an existing toolbar, or create your own customized toolbar: can apply to any active workbook or active worksheet but you may only want it to apply to a particular workbook or worksheet
• Add an item -- through VBA, customize the Worksheet Menu Bar or an existing toolbar, or create your own customized toolbar: can apply to any active workbook or active worksheet but you may only want it to apply to a particular workbook or worksheet

Just come back and start a new thread with your specific question.

Just a suggestion: It's a good habit to give your objects descriptive names. Makes it easier for you to read your code and certainly makes it easier for others. For example, UserForm1 is not very user friendly and so you named it OnCallTec. TextBox1 is also not user friendly.

Many VB programmers follow the VB Object Naming Conventions set out by Microsoft http://msdn.microsoft.com/libr…jectnamingconventions.asp. You can create your own but again the advantage is readability of code. Another benefit is grouping objects of the same class. For example, if all your textboxes have the "txt" prefix, then when entering your code after typing your top level object (such as frmOnCallTec) and the period (.), a list of the objects contained in frmOnCallTec is displayed. All the textboxes will be grouped together and all you have to do is start typing "txt" to jump to the textboxes in the list.