All,

Had a look around the forum and I can't quite find the answer I am after (or that will actually take).

So my users put a date in UK format (dd/mm/yy) into a user form. This is then put into US format when it arrives in the Excel Sheet.

The element of interest throughout is DOI

Here is the Sub for the button press

Private Sub AddRowButton_Click()

BlankCatcherShort LA.Value, BisName.Value, BisID.Value, DOI.Value, Scope.Value, FHS.Value, FHSNA.Value, CCP.Value, CCPNA.Value, Structural.Value, StructuralNA.Value, Label.Value, LabelNA.Value, COMPOSITION.Value, CompositionNA.Value, FSMS.Value, CIM.Value, Intervention.Value, OptionGroup1.Value, OptionGroup2.Value, OptionGroup3.Value
If ExitAll = True Then
Exit Sub
End If

AddDataShort LA.Value, BisName.Value, BisID.Value, DOI.Value, Scope.Value, FHS.Value, FHSNA.Value, CCP.Value, CCPNA.Value, Structural.Value, StructuralNA.Value, Label.Value, LabelNA.Value, COMPOSITION.Value, CompositionNA.Value, FSMS.Value, CIM.Value, Intervention.Value, OptionGroup1.Value, OptionGroup2.Value, OptionGroup3.Value, Comment.Value
Reset
End Sub
The call is made to a module which has the following sub:
Sub AddDataShort(LA As String, BisName As String, BisID As String, DOI As Date, Scope As String, FHS As Variant, FHSNA As Variant, CCP As Variant, CCPNA As Variant, Structural As Variant, StructuralNA As Variant, Label As Variant, LabelNA As Variant, COMPOSITION As Variant, CompositionNA As Variant, FSMS As Variant, CIM As Variant, Intervention As Variant, OptionGroup1 As Boolean, OptionGroup2 As Boolean, OptionGroup3 As Boolean, Comment As Variant)
Sheets("Data").Activate

Dim NextColumn As Integer

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

'Text Entry
 NextColumn = 1
 Cells(NextRow, NextColumn) = LA
 NextColumn = NextColumn + 1
 Cells(NextRow, NextColumn) = BisName
 NextColumn = NextColumn + 1
 Cells(NextRow, NextColumn) = BisID
 NextColumn = NextColumn + 1
 Cells(NextRow, NextColumn) = Format(DOI, "dd/mm/yy")
 NextColumn = NextColumn + 1
 Cells(NextRow, NextColumn) = Scope
 NextColumn = NextColumn + 1
 
 .....

End Sub
I have tried different forms of this: Cells(NextRow, NextColumn) = Format(DOI, "dd/mm/yy"), Cells(NextRow, NextColumn) = Format(cdate(DOI), "dd/mm/yy") and nothing is sticking.

I am looking at changing the input box to a date picker box but this is bugging me and I would like to fix it.

Thanks in advance

R