Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Unanswered: Excel Date Format

    Afternoon All (Depending On Where You Are Of Course)

    I have a small issue with a date format in my Excel 2002 forms. When i select a date from a drop down list (formatted as date in excel sheet) the form shows the number value for that date not the date itself. I am aware how to format the date in normal worksheets but unaware of how to do this for a form field. Please help as its been bugging me all morning.

    Below is the code when the field changes.

    Private Sub CBPrposedDate_Change()
    Call dater
    End Sub

    Sub dater()
    Dim banana As Date
    If FRMAgent_Details.CBAreaOfBus = "Sales" Then
    banana = FRMAgent_Details.CBPrposedDate.Text
    Sheets("InputForm").Select
    Range("u2").Select
    Do Until ActiveCell.Value = banana
    ActiveCell.Offset(1, 0).Select
    Loop
    FRMAgent_Details.TBRequestDate.Text = ActiveCell.Offset(0, 3).Value
    banana1 = FRMAgent_Details.TBRequestDate.Text
    Sheets("Main").Select
    Else
    banana = FRMAgent_Details.CBPrposedDate.Text
    Sheets("InputForm").Select
    Range("u2").Select
    Do Until ActiveCell.Value = banana
    ActiveCell.Offset(1, 0).Select
    Loop
    FRMAgent_Details.TBRequestDate.Text = ActiveCell.Offset(0, 2).Value
    banana1 = FRMAgent_Details.TBRequestDate.Text
    End If
    End Sub



    Many thanks in advance for any help you may be able to offer.

    Eddiesvoicebox

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Eddiesvoicebox

    I think what your looking for is CDate which changes doubles to dates

    but ive done a bit more than that
    try this code out and see if its ok for you

    Code:
    Option Explicit
    
    Private Sub CBPrposedDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        'run the sub for date checking
        If dater = 1 Then Cancel = True
    End Sub
    
    Function dater() As Integer
    'Updated Dater function by DavidCoutts (dbforums) 25/01/2004
    
    Dim banana As Date
    Dim LastCell As Range
        
        
        'set inital value
        dater = 0
        
        On Error GoTo InvalidDate
        'convert value ot a date,  If not exists go to the error handler
        banana = CDate(FRMAgent_Details.CBPrposedDate.Text)
        
        On Error GoTo 0
        'Try to find the correct date
        Set LastCell = Worksheets("Inputform").Range("u2").EntireColumn.Find(banana)
        If Not LastCell Is Nothing Then
            'if the date exists update TBRequestDate appropriatly
            With Worksheets("Inputform").Range(LastCell.Address)
                If UCase$(FRMAgent_Details.CBAreaOfBus) = "SALES" Then
                    FRMAgent_Details.TBRequestDate.Text = CDate(.Offset(0, 3).Value)
                Else
                    FRMAgent_Details.TBRequestDate.Text = CDate(.Offset(0, 2).Value)
                End If
            End With
        Else
            'Case when no valid date entered i.e. not on worksheet
            MsgBox "Date outwith valid boundaries", vbOKOnly
        End If
        
        Exit Function
        
    InvalidDate:
        'Case when a date value not entered
        Dim msgAnswer As VbMsgBoxResult
        
        'check if user wants to input a proper date
        msgAnswer = MsgBox("Date Value not Entered, Update?", vbYesNo, "Invalid Date")
        Select Case msgAnswer
            Case vbYes
                'if yes reset focus
                CBPrposedDate.Text = ""
                dater = 1
            Case vbNo
                'stop execution of the macro
                Exit Function
        End Select
            
    End Function
    see what you think
    Dave

Posting Permissions

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