Excel 2003 - Date input mask with VBA 6

**kadghar** · Aug 13 '11, 01:56 AM

I apologize myself if im not quite familiar with this code or problem. Correct me if i misuderstood your question:

At a first glance, your problem is that you want to give excel a four digit date (mdyy), and instead of giving you back mm/dd/yyy, its returning something else.

Well, first of all, know this: Excel calendar starts at Jan 1st 1900, and if you give Excel al date in a numeric format, it'll add days to that date, e.g.

day 25.5 will be Jan 25th 1900 at 12hrs.
day 8811 will be Feb 14th 1924 at midnight.

First of all, what you have to do is to force Excel to read your data as TEXT and to show data as TEXT, then your macro would have effect.

The problem here is that your code will work fine for the first time you write some numbers, but after the date is written in 'date style', you'll have to change the format back to TEXT again. To do this, just write this in line (lets say 17 of your code):

Target.NumberFo rmat = "@"

...and that should do the magic.

HTH

**James Grant** · Aug 23 '11, 06:35 AM

Hi Kadghar,

Thank you very much for your response! It was spot on. After I added the code above at Line 17 it worked.

The dilemma has evolved a little.

I've added in a functionality to also convert 3 and 4 digit time codes to be converted into the desired time format (i.e 112 becomes 01:12 [am], 1545 becomes 15:45 [pm]). These time codes are in a separate set of columns. Similar to my original issue whilst the cells are being manipulated the formatting just does not seem to work (e.g. 225 becomes 25/08/1900 12:00 [am]). As you will see in the below code I'm forcing it to treat the input as a string or 'text' value, then setting the format on output to "hh:mm" - but it just does not work.

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim DateStr As String
Dim TimeStr As String

On Error GoTo EndMacro

'========================================================================================

If Application.Intersect(Target, Range("C2:C1000")) Is Nothing Then
    Exit Sub
End If

If Target.Cells.Count > 1 Then
    Exit Sub
End If

If Target.Value = "" Then
    Exit Sub
End If

Application.EnableEvents = False

With Target

.NumberFormat = "@"

If .HasFormula = False Then
    Select Case Len(.Formula)
        Case 4
        
            DateStr = Left(.Formula, 1) & "/" & Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
        
        Case 6
            
            DateStr = Mid(.Formula, 3, 2) & "/" & Left(.Formula, 2) & "/" & Right(.Formula, 2)
            
        Case Else
            
            MsgBox "Please only enter a 4 or 6 digit shortcode for the date", vbOKOnly, "Entry error"
            
    End Select
    
    .NumberFormat = "dd/mm/yyyy"
    .Formula = DateValue(DateStr)

End If

End With

'========================================================================================

If Application.Intersect(Target, Range("D3:K1000")) Is Nothing Then
    Exit Sub
End If

With Target

.NumberFormat = "@"

If .HasFormula = False Then
    Select Case Len(.Value)
        Case 3
            TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2)

        Case 4
            TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2)

        Case Else
            
            MsgBox "Please only enter 3 or 4 digit shortcodes for the time", vbOKOnly, "Entry Error"
    End Select
    
    .NumberFormat = "hh:mm"
    .Value = TimeValue(TimeStr)

End If

End With

'========================================================================================

Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date or time."
Application.EnableEvents = True
End Sub

The second issue arising from this is now that the date conversion is no longer working properly. It will still correctly convert the first entered date on opening the workbook, but any subsequent entries revert to my original issue. It would seem to me that it's a triggering issue - but I'm simply using the generic and inbuilt 'Change' event trigger and the ranges remain static - I cannot figure out what's happening.

Any assistance would be greatly appreciated, and I apologise for my deviation into time input masks in addition to the date input mask thread. If you wish I will re-post this question separately.

Many thanks!

James

**kadghar** · Aug 27 '11, 12:48 AM

Hi, sorry for the delay..

well, the problem is that at the end of your SUB, you ask VBA to make the cell's format a DATETIME again, that should be causing troubles...

when it comes to the time... well it's a little bit harder, since vb understand dates as fractions of a day... so 12 hours will be 0.5...

i'll strongly suggest you to remove the .numberformat = "dd/mm/yy" or "hh:mm", and leave it as a text and write the format yourself, otherwise, excel will always try to fit it its own way, and sometimes do it wrong.

Excel 2003 - Date input mask with VBA 6

Excel 2003 - Date input mask with VBA 6

Comment

Comment

Comment