StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POVBA force string variable to uk date format
text
Body
copied!<p>Edit: After some reading, I've come across an answer which I've posted below.</p> <p>Okay, so I've been looking at a piece of VBA that is available via www.contextures.com. The piece of code in question enables users to add multiple entries to a data validation list seperated by a comma. The list allows the user to select from a list of dates, and also a small list of text entries. The way it works is the user can free type a date, or use the pick list to select a date or text entry. Sometimes two dates may need to go into a cell, so the VBA below allows the user to select/type one date, hit enter, then type another date in the same cell. As soon as they hit enter, the previous date appears in the cell, with the new date following seperated by a comma. So far so good.</p> <p>This is the original piece of VBA.</p> <pre><code>Option Explicit ' Developed by Contextures Inc. ' www.contextures.com Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count > 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 3 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub </code></pre> <p>Now, the problem I'm having is that the VBA works, however, as I'm UK based, whenever I enter a UK date into the cells the VBA applies to (such as 01/06/2013 i.e. dd/mm/yyyy), the VBA is intervening and returning the date as 06/01/2013 (mm/dd/yyyy). My control panel settings are all set to uk format, so I figure it's something to do with how the VBA stores and then returns the date. I've tried making amendments to the dims oldval and newval (to dates) but then the VBA doesn't allow me to have multiple entries in one cell as before (I guess vba doesn't like concatenating dates?). </p> <p>I've amended the VBA to a hacky work around (below) which formats the dates to the non standard dd.mm.yyyy (dd/mm/yyyy doesn't work) but this would require me to tell users to input dates in this format which I'd rather not do (I'd rather allow the more standard 01/01/2013 or 01-01-2013 both of which are recognised as dates by excel):</p> <pre><code>Option Explicit ' Developed by Contextures Inc. ' www.contextures.com Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count > 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Format(Target.Value, "dd.mm.yyyy") 'amendment Application.Undo oldVal = Format(Target.Value, "dd.mm.yyyy") 'amendment Target.Value = newVal If Target.Column > 1 Then 'amendment If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub </code></pre> <p>Anyway, any advice/pointers would be much appreciated! I'm using Excel 2007, but the spreadsheet needs to be compatible with 2003.</p> <p>Edit: The way the data looks is in this question here: <a href="https://stackoverflow.com/questions/16299946/excel-2003-how-to-count-dates-in-row-including-multiple-dates-in-cell">Excel 2003 - How to count dates in row, including multiple dates in cell</a></p> <p>Edit: I should also mention that if I type 01/07/2013 into a cell twice, it comes out like this:</p> <p>07/01/2013, 01/07/2013</p> <p>Which is very odd. It's like the first time the cell is populated it switches it to US date format, but anything after is ok. I am going to try and test this on a colleague's machine, and on my home PC to see what the results are.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload