Lock Cell Date Format NOT Content

**mlmaclean** · 09-18-2009, 08:59 AM

I have a workbook that my employees are required to update. HOWEVER, there seems to be some trouble keeping the same date format. Rather than including instructions on how to input the date, is there a way to have my employees enter the date in whatever form they choose and it transform into my preferred format?

I want it always to appear as dd-mmm-yy

Many thanks!

**Palmetto** · 09-18-2009, 09:57 AM

How about using a Calendar Control to let them pick the date so that you always get a valid date in the desired format?

See attached.

Calendar Code:

Option Explicit

Private Sub Calendar1_Click()

    ActiveCell.Value = Format(Me.Calendar1.Value, "dd-mm-yy")

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Not Intersect(Target, Range("A10:A100")) Is Nothing Then
        Me.Calendar1.Visible = True
    Else
        Me.Calendar1.Visible = False
    End If

End Sub

**mlmaclean** · 09-18-2009, 10:22 AM

Yes... That's what I was thinking too...

Thanks for the code... I'll try it out...

**mlmaclean** · 09-18-2009, 12:06 PM

If I want to add it to a new worksheet - I understand I have to create my own user form, but... am I missing something obvious for the ability to click on the cell and make it happen?

**alepsh** · 12-30-2010, 08:00 AM

Hi, i tried to use your example xls file, but i have an error message:

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

Method or data member not found
---------------------------
OK Help
---------------------------

What i need to do ?

thanks