Applying Mask For Date Entries

dejayajay · Jan 24th 2007

I'd like to enter dates without having to use the forward slash symbol between the day, month and year values. I've read a post by Dave Hawley explaing how to do it, but I can't get it to work for me. There must be some step that I am not aware of. Help please.

Wigi · Jan 24th 2007

Re: Applying Mask For Date Entries

Quote from dejayajay

I've read a post by Dave Hawley explaing how to do it, but I can't get it to work for me. There must be some step that I am not aware of. Help please.

Please provide the link to it, will you?

hgus393 · Jan 24th 2007

Re: Applying Mask For Date Entries

http://www.ozgrid.com/VBA/mask-date-entry.htm

hgus393 · Jan 24th 2007

Re: Applying Mask For Date Entries

Quote from dejayajay

I'd like to enter dates without having to use the forward slash symbol between the day, month and year values. I've read a post by Dave Hawley explaing how to do it, but I can't get it to work for me. There must be some step that I am not aware of. Help please.

Pls explain what you do, Have you put this in the worksheet?

**royUK** · Jan 24th 2007

Re: Applying Mask For Date Entries

It works fine, where have you put the code

[wsc]*[/wsc]

dejayajay · Jan 24th 2007

Re: Applying Mask For Date Entries

Thanks for your reply. To check, I went tools/macros/VBE/view Project Explorer, then double clicked the Sheet for the workbook. The code is there as I placed it. I haven't modified the code at all. The cells that I am entering the date in are all in Column A. Should the macro now work automatically when I type in the dates as say 010107 for Jan 1 2007? I have the cells formatted to dd/mmm/yy. Please excuse my lack of knowledge.

**royUK** · Jan 24th 2007

Re: Applying Mask For Date Entries

You don't need to format the cells.

Have you placed it in the WorkSheet_hange as below?

Code

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim StrVal As String
Dim dDate As Date


    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
     
     With Target
         StrVal = Format(.Text, "000000")
          If IsNumeric(StrVal) And Len(StrVal) = 6 Then
            Application.EnableEvents = False
            
            If Application.International(xlDateOrder) = 1 Then 'dd/mm/yy
                dDate = DateValue(Left(StrVal, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Right(StrVal, 2))
            Else 'mm/dd/yy
                dDate = DateValue(Mid(StrVal, 3, 2) & "/" & Left(StrVal, 2) & "/" & Right(StrVal, 2))
            End If
            
            .NumberFormat = "dd/mm/yyyy"
            .Value = CDate(DateSerial(Year(dDate), Month(dDate), Day(dDate)))
           
          End If
    End With
           
     Application.EnableEvents = True


End Sub

Display More

Have you got Macros Enabled?

dejayajay · Jan 24th 2007

Re: Applying Mask For Date Entries

hgus393 has kindly posted the link in his reply. I have replied to another response. Now waiting for more assistance. Will check again tomorrow. Thanks, and good night for now.

dejayajay · Jan 24th 2007

Re: Applying Mask For Date Entries

Macros were enabled. Now when I try to use one of my regular macros eg 'Merge NextSixCells', I get the message "Complile Error: Invalid Outside Proceedure". Re your other questions, I have 'de-formatted te cells in question, back to General Format. And I amended the code to match your copy above - added Option Explicit at the top, and End With / Application.Enable Events = True / End Sub at the end (without the '/'s).

But that's it for me for tonight. I'll check back tomorrow. Thanks.

dejayajay · Jan 25th 2007

Re: Applying Mask For Date Entries

OK. I've got it to work, but only in a fresh worksheet, with none on my other macros running or present. My limited knowledge is a definite handicap here. If one of you 'helpers' pick up this thread again, I'd still like to get it functioning in all my worksheets. Thanks.

**royUK** · Jan 25th 2007

Re: Applying Mask For Date Entries

Quote from dejayajay

OK. I've got it to work, but only in a fresh worksheet, with none on my other macros running or present. My limited knowledge is a definite handicap here. If one of you 'helpers' pick up this thread again, I'd still like to get it functioning in all my worksheets. Thanks.

You need to add the code to each worksheet that you want it to work on

Applying Mask For Date Entries

Participate now!

Share