Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2008
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date format (excel 2003)

    Hi
    The below code works exactly as i want it. but when i enter the date in the pop up box it automatically converts it to mm/dd/yy as oppose to dd/mm/yy the UK format. can you please help with any suggestions.thanks in advance

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 7 Then
    If Target.Value = "Accepted" Then
    Application.EnableEvents = False
    Target.Offset(0, 7) = InputBox("Enter date", "Quote Accepted", Date)
    Application.EnableEvents = True
    End If
    End If
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date format (excel 2003)

    VB does not understand UK dates only US dates. Try this:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sDate As String, dDate As Date
    If Target.Column = 7 Then
    If Target.Value = "Accepted" Then
    sDate = InputBox("Enter date", "Quote Accepted", Format(Date, "dd/mm/yyyy"))
    dDate = DateValue(Mid(sDate, 4, 2) & "/" & Left(sDate, 2) & "/" & Right(sDate, 4))
    Application.EnableEvents = False
    Target.Offset(0, 7) = dDate
    Application.EnableEvents = True
    End If
    End If
    End Sub

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •