PDA

View Full Version : Date Problems on VBA Forms



WillieC
10-07-2008, 01:49 AM
Hi there,

I have built a Data Capture Form on Excel 2003 VBA forms. I have a calendar on the form but just need a couple of things to make it work.

1. When a user opens the form, how can I get the calendar to automatically go to today's date?

2. When the user clicks OK I need the date on the calendar to go into the first column on the excel spreadsheet.

Can you help??

Bob Phillips
10-07-2008, 02:08 AM
We cannot second guess what the form looks like or what you mean by ... go into the first column on the excel spreadsheet.

Posting an examle workbook with your working code will help.

WillieC
10-07-2008, 02:13 AM
Here is the code I have used so far:

Private Sub Calendar1_Click()

End Sub
Private Sub cboAddRemove_Change()
End Sub
Private Sub cboDemand_Change()
End Sub
Private Sub cboSALT_Change()
End Sub
Private Sub cboValueCreation_Change()
End Sub
Private Sub cboValueFailure_Change()
End Sub
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DemandData")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a date
If Trim(Me.txtDemandDate.Value) = "" Then
Me.txtDemandDate.SetFocus
MsgBox "Nothing has been added!! Please either fill out the boxes or close the form if not required."
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDemandDate.Value
ws.Cells(iRow, 2).Value = Me.txtRoll.Value
ws.Cells(iRow, 3).Value = Me.cboProduct.Value
ws.Cells(iRow, 4).Value = Me.cboWhereFrom.Value
ws.Cells(iRow, 5).Value = Me.cboDemand.Value
ws.Cells(iRow, 6).Value = Me.cboTerm.Value
ws.Cells(iRow, 7).Value = Me.cboIntFreq.Value
ws.Cells(iRow, 8).Value = Me.txtOtherDemand
ws.Cells(iRow, 9).Value = Me.txtResponse.Value
ws.Cells(iRow, 10).Value = Me.cboAddRemove.Value
ws.Cells(iRow, 11).Value = Me.txtWhereFromTo.Value
ws.Cells(iRow, 12).Value = Me.txtPayInterestTo.Value
ws.Cells(iRow, 13).Value = Me.cboSALT.Value
ws.Cells(iRow, 14).Value = Me.cboWhatMatters.Value
ws.Cells(iRow, 15).Value = Me.txtActDate.Value
ws.Cells(iRow, 16).Value = Me.txtMatDate.Value
ws.Cells(iRow, 17).Value = Me.cboDidWeDoWhatMatters.Value
ws.Cells(iRow, 18).Value = Me.txtIfNotWhyNot.Value
ws.Cells(iRow, 19).Value = Me.cboValueFailure.Value
ws.Cells(iRow, 20).Value = Me.cboValueCreation.Value
ws.Cells(iRow, 21).Value = Me.txtExtraDemand.Value

'clear the data
Me.txtDemandDate.Value = ""
Me.txtRoll.Value = ""
Me.cboProduct.Value = ""
Me.cboWhereFrom.Value = ""
Me.cboDemand.Value = ""
Me.cboTerm.Value = ""
Me.cboIntFreq.Value = ""
Me.txtOtherDemand.Value = ""
Me.cboAddRemove.Value = ""
Me.txtWhereFromTo.Value = ""
Me.txtPayInterestTo.Value = ""
Me.cboSALT.Value = ""
Me.txtResponse.Value = ""
Me.cboWhatMatters.Value = ""
Me.txtMatDate.Value = ""
Me.txtActDate.Value = ""
Me.cboDidWeDoWhatMatters.Value = ""
Me.txtIfNotWhyNot.Value = ""
Me.cboValueFailure.Value = ""
Me.cboValueCreation.Value = ""
Me.txtExtraDemand.Value = ""
Me.txtDemandDate.SetFocus
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub ComboBox1_Change()
End Sub
Private Sub Label15_Click()
End Sub
Private Sub txtDemandDate_Change()

End Sub
Private Sub txtPayInterestTo_Change()
End Sub
Private Sub txtSALT_Change()
End Sub
Private Sub txtWhatMatters_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
txtDemandDate.Value = ""

With cboDemand
.AddItem " "

.AddItem "I want to reinvest total."
.AddItem "I want to reinvest my capital only."
.AddItem "I want to reinvest some money."
.AddItem "I want to close my account."
.AddItem "I want something else."

End With
cboDemand.Value = ""



With cboProduct

.AddItem " "

.AddItem "Guaranteed Reserve"

.AddItem "FRISA"

End With

cboProduct.Value = ""


With cboWhereFrom

.AddItem " "

.AddItem "Branch"

.AddItem "Mat Form"

.AddItem "Telephone"

.AddItem "Letter"

End With

cboWhereFrom.Value = ""

With cboTerm

.AddItem " "

.AddItem "6 months"

.AddItem "1 year"

.AddItem "2 years"

.AddItem "3 years"

.AddItem "4 years"

End With

cboTerm.Value = ""

With cboIntFreq

.AddItem " "

.AddItem "On Maturity"

.AddItem "Monthly"

.AddItem "Annually"

End With

cboIntFreq.Value = ""

With cboAddRemove

.AddItem " "

.AddItem "Add"

.AddItem "Remove"

End With

cboAddRemove.Value = ""

txtDemandDate.SetFocus

With cboSALT

.AddItem " "

.AddItem "Yes"

.AddItem "No"

End With

cboSALT.Value = ""

With cboWhatMatters

.AddItem " "

.AddItem "On Time"

.AddItem "ASAP"

.AddItem "Now"

End With

cboWhatMatters.Value = ""

With cboDidWeDoWhatMatters

.AddItem " "

.AddItem "Yes"

.AddItem "No"

End With

cboDidWeDoWhatMatters.Value = ""

With cboValueCreation

.AddItem " "

.AddItem "1 - Exceeds Expectations"

.AddItem "2 - Meets Expectations"

.AddItem "3 - Below Expectations"

.AddItem "4 - Failed Security"

End With

cboValueCreation.Value = ""

With cboValueFailure

.AddItem " "

.AddItem "V"

.AddItem "F"

.AddItem "W"

.AddItem "WV"

.AddItem "WF"

End With

cboValueFailure.Value = ""

End Sub

Not sure if this helps. I'm pretty much a Newbie with VBA as I haven't used it in about 10 years lol

Bob Phillips
10-07-2008, 02:40 AM
Post a workbook, with a form, I haven't the time to recreate what you have already done.

WillieC
10-07-2008, 03:26 AM
There you go, mate

WillieC
10-07-2008, 03:54 AM
Problem sorted, XLD. Had a bit of a play around and it seems to be working now. Thanks for your help anyway.

Oh just one other thing. At the minute, when the Data Capture Form is opened, Excel stays visible in the background. Is it possible to have it so that when the Form is opened Excel minimizes?

Bob Phillips
10-07-2008, 03:59 AM
That's better!

To get the calendar to start at today, add this to your form



Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub


The second question. Doesn't it already do that?

WillieC
10-07-2008, 04:01 AM
That's better!

To get the calendar to start at today, add this to your form



Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub


The second question. Doesn't it already do that?

I have it now so that the Date goes into the cell I needed. I think there was a conflict as I had a textbox still in for the date.

Bob Phillips
10-07-2008, 04:04 AM
When you select a date in the calendar, where should it go?

WillieC
10-07-2008, 04:06 AM
When yu select a date in the calendar, where should it go?

I have it sorted I think. It should go into Column A in the next available cell.

Bob Phillips
10-07-2008, 04:15 AM
No I meant on the form, there are 3 date fields there.

WillieC
10-07-2008, 04:18 AM
No I meant on the form, there are 3 date fields there.

Originally I wanted it to go into the field that just said Date but I have removed that textbox now and the calendar has replaced it. The user doesn't have to worry about selecting a date now as the calendar does it for them automatically. The other date boxes will only be filled in the odd time but this must be done by the user.

WillieC
10-07-2008, 05:53 AM
Anybody know how to make Excel disappear when a form opens?? At the minute it sits in the background.

Thanks

Bob Phillips
10-07-2008, 06:13 AM
Application.Visible = False

WillieC
10-07-2008, 06:24 AM
Thanks.

WillieC
10-07-2008, 07:39 AM
Just another quick question for XLD.

I've noticed that when I open the Data Capture Form it allows the user to type things into the combo boxes instead of just being able to choose the selections that's there. Is there a way of having it so that nothing can be typed into the combo box?

Also, is there a way that I can stop the users from changing the date on the calendar eg freezing the calendar on that day's date?

Thanks again for all your help

Bob Phillips
10-07-2008, 07:46 AM
Change the style property of the combobox to dropdownlist.

WillieC
10-07-2008, 07:49 AM
Thanks.

Any ideas with regards to locking the calendar?

Bob Phillips
10-07-2008, 08:48 AM
Sorry, I missed that, what are you trying to do?

WillieC
10-08-2008, 01:48 AM
Hi XLD,

If you still have the file I sent, you'll see that there are two textboxes that say Mat Date and Act Date. Is is possible that when a user clicks on these that a calendar automatically "pops up" for them to select the date?

georgiboy
10-08-2008, 02:18 AM
I have posted an example sheet with this in action.

Hope this helps.

WillieC
10-08-2008, 06:26 AM
Hi guys,

I've been trying to work out how to use a command button on a form to open another userform. I can't for the life of me work it out.

Can anyone help???

georgiboy
10-08-2008, 12:28 PM
Do you mean like this?

Hope this helps.

Bob Phillips
10-08-2008, 02:11 PM
Hi XLD,

If you still have the file I sent, you'll see that there are two textboxes that say Mat Date and Act Date. Is is possible that when a user clicks on these that a calendar automatically "pops up" for them to select the date?

That's what I was alluding to earlier, and I would just make it non-visible until you enter one of those textboxes, then use ActiveControl to trap it.

WillieC
10-09-2008, 12:45 AM
That's what I was alluding to earlier, and I would just make it non-visible until you enter one of those textboxes, then use ActiveControl to trap it.

Would you have a piece of code that I could insert? I can't get it going.

Cheers again for all your help

Bob Phillips
10-09-2008, 01:41 AM
Can you post your workbook again, I have deleted it?

WillieC
10-09-2008, 01:43 AM
Removed

WillieC
10-09-2008, 08:28 AM
Hi there,

So far the form is working well. I have come across just one problem. When testing the form with more than one user, if they both clicked ok to store the data it come with conflicting interests. How can I program my form so that more than one user can put the data onto the spreadsheet at the same time?

WillieC
10-10-2008, 12:51 AM
Morning, XLD. Any joy with my question?

Bob Phillips
10-10-2008, 01:01 AM
I thought you had overcome the problem, that is how I read the last post post of yours, and you removed the file.

WillieC
10-13-2008, 04:08 AM
I had resolved the previous problem. The one I have come across now is that 2 users can't enter the same data at the same time. If they both click ok at the same time then a conflict error comes up.

WillieC
10-13-2008, 06:19 AM
Hi there,

I've uploaded the file again in the hope that you or somebody else can help, XLD.

Basically what I need done is for two or more users to be able to click OK at the same time and for the data to go onto the worksheet instead of getting conflicting errors.

Apologies for being a nuisance but this is doing my head in lol :banghead: :banghead:

Bob Phillips
10-13-2008, 06:25 AM
Sorry, there is no point in hoping that I will help, I avoid shared workbooks like the plague and have no idea of how to solve the problems that they create.

WillieC
10-13-2008, 06:30 AM
No worries, XLD, hopefully someone else can help.

Thanks for all your help so far. You've been a star

WillieC
10-13-2008, 08:34 AM
Another question for you guys if possible.

How do I have it so that if a certain option is chosen from a combobox then other fields are greyed out and can't be used??

GTO
10-13-2008, 11:40 AM
Presuming you are using the combo boxes as drop-down lists (no editing), you should be able to use the change event of the combo box and trap either the val (if single col box) or better yet, the list index.

WillieC
10-14-2008, 06:16 AM
Presuming you are using the combo boxes as drop-down lists (no editing), you should be able to use the change event of the combo box and trap either the val (if single col box) or better yet, the list index.

Sorry, GTO, what do you mean? I'm quite a newbie when it comes to VBA

GTO
10-14-2008, 08:55 PM
...How do I have it so that if a certain option is chosen from a combobox then other fields are greyed out and can't be used??

Hey there WillieC,

Presuming you were asking as to disabling another combo/text/similar control based on a certain value (option) being chosen, I was just answering that this is possibly a couple of different ways.

Here's an example that disables the 'AddRemove' box anytime that "Mat Form" is chosen in the 'WhereFrom' box.

Mark


Private Sub cboWhereFrom_Change()
If cboWhereFrom.Value = "Mat Form" Then
cboAddRemove.Enabled = False
Else
cboAddRemove.Enabled = True
End If
End Sub

WillieC
10-15-2008, 01:16 AM
Hey there WillieC,

Presuming you were asking as to disabling another combo/text/similar control based on a certain value (option) being chosen, I was just answering that this is possibly a couple of different ways.

Here's an example that disables the 'AddRemove' box anytime that "Mat Form" is chosen in the 'WhereFrom' box.

Mark


Private Sub cboWhereFrom_Change()
If cboWhereFrom.Value = "Mat Form" Then
cboAddRemove.Enabled = False
Else
cboAddRemove.Enabled = True
End If
End Sub


Hi Mark,

That's worked perfectly. Thanks very much mate.

Just two other things that I could do with some help with.

1. When more than one user is using the form, if they click on OK and Save at the same time, a conflict error comes up as the form tries to enter both sets of data into the same row. Any ideas how to solve this?

2. When a user opens the Form, I'd like Excel to minimize so that the form will appear on screen and can be used without having to have Excel in the background.

Thanks again for all your help

WillieC
10-15-2008, 01:16 AM
Hey there WillieC,

Presuming you were asking as to disabling another combo/text/similar control based on a certain value (option) being chosen, I was just answering that this is possibly a couple of different ways.

Here's an example that disables the 'AddRemove' box anytime that "Mat Form" is chosen in the 'WhereFrom' box.

Mark


Private Sub cboWhereFrom_Change()
If cboWhereFrom.Value = "Mat Form" Then
cboAddRemove.Enabled = False
Else
cboAddRemove.Enabled = True
End If
End Sub


Hi Mark,

That's worked perfectly. Thanks very much mate. :beerchug:

Just two other things that I could do with some help with.

1. When more than one user is using the form, if they click on OK and Save at the same time, a conflict error comes up as the form tries to enter both sets of data into the same row. Any ideas how to solve this?

2. When a user opens the Form, I'd like Excel to minimize so that the form will appear on screen and can be used without having to have Excel in the background.

Thanks again for all your help

WillieC
10-15-2008, 01:16 AM
Hey there WillieC,

Presuming you were asking as to disabling another combo/text/similar control based on a certain value (option) being chosen, I was just answering that this is possibly a couple of different ways.

Here's an example that disables the 'AddRemove' box anytime that "Mat Form" is chosen in the 'WhereFrom' box.

Mark


Private Sub cboWhereFrom_Change()
If cboWhereFrom.Value = "Mat Form" Then
cboAddRemove.Enabled = False
Else
cboAddRemove.Enabled = True
End If
End Sub


Hi Mark,

That's worked perfectly. Thanks very much mate. :beerchug:

Just two other things that I could do with some help with.

1. When more than one user is using the form, if they click on OK and Save at the same time, a conflict error comes up as the form tries to enter both sets of data into the same row. Any ideas how to solve this?

2. When a user opens the Form, I'd like Excel to minimize so that the form will appear on screen and can be used without having to have Excel in the background.

Thanks again for all your help

GTO
10-15-2008, 07:31 AM
Greetings and happy to help,

re 1. Nope.

I did mention this to a friend and co-worker, and he may get to take a look-see.

re 2. Bob (xld) already answered this around post 14.

Application.Visible = False

...should be either right before frmWhatever.Show, or you could put it in the form's activate event. Naturally you'll want to ensure that the app's visibility is returned when the userform(s) is/are dismissed. I'd think the query close event a good place where you couldn't forget it under some button or other.

WillieC
10-16-2008, 02:30 AM
Cheers GTO. I had forgotten about XLD's code from earlier but that work's a treat. Cheers to XLD also.

Another question, sorry for being a pain, is there a way of restricting the number of characters that a user can input into a textbox?

Bob Phillips
10-16-2008, 02:50 AM
This stops more than 5 being entered



Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If Len(Me.TextBox1.Text) > 4 Then

MsgBox "Too many chars"
KeyAscii = 0
End If
End Sub

Bob Phillips
10-16-2008, 02:52 AM
I had forgotten about XLD's code from earlier ...

A lot of water since then :-)

Aussiebear
10-16-2008, 02:54 AM
WillieC, I can appreciate the fact that Bob has been helping you in this matter, as I sure you do as well, but this is a public forum. Either post your workbook to the forum or take it offline with Bob.

You were asked to post a workbook to the forum, but sent to Bob privately. How can anybody assist you if this is being dealt with off site?

GTO
10-16-2008, 03:52 AM
@Aussiebear: Greetings and Howdy from Arizona. I believe WillieC's latest ver of wb in question is at/about post #32.

Mark

GTO
10-16-2008, 04:22 AM
@xld:

Greetings Bob and best to your and yours. For my education, is there an advantage to keypress vs properties?

Thank you so much,

Mark

Bob Phillips
10-16-2008, 04:35 AM
What properties are you referring to?

GTO
10-16-2008, 04:48 AM
Well... i should probably start feeling abashed now, but I took the Q. as to the/a userform textbox, ie @ maxlength. 'Yeeks!' on me?

WillieC
10-16-2008, 05:21 AM
I posted the file to the forum but here it is again, in case it has been removed.

WillieC
10-16-2008, 05:30 AM
This stops more than 5 being entered



Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If Len(Me.TextBox1.Text) > 4 Then

MsgBox "Too many chars"
KeyAscii = 0
End If
End Sub


Thanks XLD. I just realised that I could change the character length in the textbox's properties. D'oh!

WillieC
10-16-2008, 08:18 AM
Updated version guys. The problem I'm having now is that when Today's Date is selected, anything entered after that automatically drops to the next row. Is there any way that I can use the pop up calendar to insert today's date but will also allow me to fill in the rest of the same row??

Bob Phillips
10-16-2008, 09:09 AM
It doesn't do anything when I click a date. BTW, why ask them to select Today's date?

Demosthine
10-17-2008, 04:08 PM
Good Afternoon there.

GTO suggested I take a look at this thread because I've done a fair amount of work with Shared Workbooks. As XLD said, it can cause quite the mess out of your project and it took me a good deal of time ironing out of the problems I had.

So the other information aside (since the others seem to be helping well with that), if you would be so kind, run through the method you are using to allow multiple users to open the workbook at once. That'll help me pin down the particular errors you are having...

Scott

WillieC
10-20-2008, 02:45 AM
Good Afternoon there.

GTO suggested I take a look at this thread because I've done a fair amount of work with Shared Workbooks. As XLD said, it can cause quite the mess out of your project and it took me a good deal of time ironing out of the problems I had.

So the other information aside (since the others seem to be helping well with that), if you would be so kind, run through the method you are using to allow multiple users to open the workbook at once. That'll help me pin down the particular errors you are having...

Scott

At the minute I'm just using the shared workbook method which has a macro for the users to open the form.

WillieC
10-20-2008, 04:08 AM
It doesn't do anything when I click a date. BTW, why ask them to select Today's date?

I have that in there as I wasn't sure how to automatically select today's date on the form.

Demosthine
10-20-2008, 09:19 AM
Good Morning Everyone.

To insert Today's Date automatically, place a command in your UserForm_Initialize or UserForm_Activate Event. The code would look something like:


lblTodayDate.Caption = Format(Now(), "mm/dd/yyyy")



A few other notes while I'm posting...

When I load the project, I am missing "ffOcx" and I'm not sure what that is. Is it used in the project or is it a miscellaneous extra?

After I load the UserForm frmDataCapture and click on the "Click to select Date of Demand" button, it automatically resets to today's date. I would assume that's not what you want, so you'll want to remove delete the code from Calendar7_OnClick.

Or better yet, write some code in that Event Procedure to validate the date. For example, you can not have a date that is prior to today for your Date of Demand, right?


Private Sub Calendar7_OnClick
Dim datToday as Date

datToday = Format(Now(), "mm/dd/yyyy")

If Calendar7.Value <= datToday Then
MsgBox "You must select a future date."
End If
End Sub



For practical programming, you have eight forms that are identical in design. Since your two Data Capture Forms each use four of these Forms, I've written a small snippet in the attached Workbook that should simplify your project. By just deleting these eight UserForms, I took the Workbook size from 345KB to 233KB. Those eight forms took up 1/3rd of your Workbook size!

In the example workbook, when you move your Mouse over the Labels with the dates, the Pointer will turn to a hand. Click on the Label and the Calendar will appear at the correct position. When you click the Date on the Calendar, it will disappear.


I am still looking into the Shared Workbook issue for you, but don't have anything substantial yet.

Scott

WillieC
10-21-2008, 03:21 AM
Thanks for the help, Demosthine. Excellent work with the Calendar. Just on thing. That calendar example, I'm having some problems with it. Most likely just me being stupid but I can't get the Calendar working. Where do I put it into the form that I have?

Sorry for being a nuisance

Demosthine
10-21-2008, 04:05 PM
Good Afternoon again.

You can put the actual Calendar Control anywhere (out of the way) on frmDataCapture and frmDataCapture2. You'll want to replace your four command buttons on each form that say "Click here for date." or whatever it says. Make them labels and format them how you want them to look.

Double click each of the four new labels and substitute the code inside of the Label_Click Events from my sample workbook and the Calendar_Click Event from my sample. You'll have to change the name in each of the events where I had Set ctlActiveControl = lbl*Val.

If you still can't figure it out, repost the workbook witht the changes you've made reference the above and I'll work with you there.

Scott

P.S. Please work on your Control Naming. It is never good practice, especially with a project that seems to be growing ever-larger, to use controls named Calendar1, Calendar8, etc. Use names that mean a little more, like I did with the sample Workbook. lblDateOfDemand and lblTodaysDate, for instance. This greatly simplifies debugging and other people trying to figure out what you're doing. It's hard to go hunt down what Text1 is supposed to be every time.

WillieC
10-22-2008, 01:49 AM
Thanks Demosthine, I'll give that a go. Much appreciated

WillieC
10-22-2008, 06:22 AM
Hi Demosthine.

I've tried to get the calendar control to work but I keep getting an error. Attached is the file with the changes I've made. Thanks for everything so far.

Demosthine
10-22-2008, 04:49 PM
Ok Willie, I'm thoroughly confused. You attached what is supposed to be your file with the changes made, but the only sign I see that you've made any changes is that you've imported the MousePointer Module, which is the least important of the information I provided in the working example.

On frmDataCapture and frmDataCapture2, you are still using CommandButtons instead of Labels. You still have eight different UserForms for your Calendars, and there is no sign of implementing the example's code. Everything is missing, right down to the Module-Level variable for the ActiveControl.


If you'll read any of my posts, you'll find that I don't "do the work for you," but instead provide suggestions in assisting you in completing it. With a project of this size, I'm sure you are not expecting anyone to do it for you. That said, I literally provided code that you can copy and paste into your project. With the proper naming of your four Labels and your Calendar Control, the example is fully functional.

I don't wish to sound harsh, but would ask that you make a better attempt at implementing the code or suggestions that we provided to you. After that, should you still experience an error, feel free to repost with the new error and I will be happy to take a look at it from there.

Scott

WillieC
10-23-2008, 01:21 AM
Hi Scott,

Sorry, I posted the wrong file. Apologies for that. I'm trying something else for the dates as I've just found out yesterday that this is only going to be used for about 3 months or so and then our IT dept are going to have an all singing, all dancing form.

Thanks so much for all your help. At least I know how helpful you guys are if I need any more assistance.

PS I never expected any of you guys in here to do the work for me. I've been asked to do this and all I wanted was a few hints and tips here and there.

Thanks again

WillieC
10-23-2008, 05:49 AM
Afternoon Guys,

Everything seems to be fine so far but I've just noticed that when I click on Demand Date and it is entered into the spreadsheet it auto formats to US style dates from the 2nd column onwards. I have formatted the Date column so that it's the UK format but it won't stick. Can you help?