date format - user form text box

[Tony Miall]

Hello everyone,

I have a user form that requires dates to be entered and found the following code which I applied to the text box to enforce the correct date format:

Private Sub InvoiceDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Mid(InvoiceDate.Value, 4, 2) > 12 Then
        MsgBox "Invalid date, please re-enter using the correct format dd/mm/yyyy", vbCritical
        InvoiceDate.Value = vbNullString
        InvoiceDate.SetFocus
        Exit Sub
    End If
     
    dDate = DateSerial(Year(Date), Month(Date), Day(Date))
    InvoiceDate.Value = Format(InvoiceDate.Value, "dd/mm/yyyy")
    dDate = InvoiceDate.Value
End Sub

This works great and enters the date into to worksheet correctly.

Then when I call the user form if the cell already has a date value in it it repopulates the text box "InvoiceDate" with the date in the cell and it all looks fine. But when I run the macro again to enter the detail into the sheet the value entered into the cell shows as 0/01/1900 and I don't know why. I've tried various formats for the cell on the sheet itself but it doesn't work.

The relevant line of code I'm running off the form is:

ActiveCell.Offset(0, 45).Range("A1").Select
    ActiveCell.Value = dDate

I hope that's clear, any help appreciated as usual. Thanks

 

[James006]

Hi Tony,

The code you have posted is the "before-update" for your textbox ...
What is the code you are using to populate your worksheet ?

Provided your dDate is declared as public, the code activecell.value = dDate must work without any problem ...

Cheers

 

[Tony Miall]

Thanks for the reply, here's the whole macro. Just to reiterate it works fine when the date is typed in to the text box on the user formand the macro is run. The problem arises when the user form is called then if a user has already entered some information it is repopulated from the worksheet back into the text box or boxes on the user form. So the date is now in the text box and has not been re entered manually. The user then makes other changes and runs the macro again to put the updated information into the sheet. The date then shows up in the cell as 0/01/1900 ie it's saying there is nothing in the textbox. hope that explains it properly

Private Sub CommandButton1_Click()




     
If InvoiceNumber = "" Then
        MsgBox "Please enter an invoice number"
    Exit Sub
End If


If InvoiceAmount = "" Then
        MsgBox "Please enter an invoice amount"
    Exit Sub
End If


If InvoiceDate = "" Then
        MsgBox "Please enter an invoice date"
    Exit Sub
End If


Answer = MsgBox("You are about to execute an irreversable action on this job or enquiry. Do you want to proceed?", _
    vbYesNo, "Order Processor")
      
        If Answer = vbNo Then


    Exit Sub
       
        End If


OPENMASTERSALES
  
    If Workbooks("BFA MASTER SALES.xlsm").ReadOnly Then
        
        MsgBox "Update in progress, please wait a few seconds and try again", vbCritical, "Update in progress"
                          
            Workbooks("BFA MASTER SALES.xlsm").Close savechanges:=False
     
                Exit Sub
    End If
    
    FOOLPROOFMASTERSALES


ActiveCell.Rows("1:1").EntireRow.Select
    ActiveCell.Offset(0, 74).Range("A1").Select
    ActiveCell.Value = InvoiceDetail.InvoiceNumber.Value
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Value = InvoiceDetail.InvoiceAmount.Value
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/1.1-SUM(RC[-67]:RC[-65])"
    
    ActiveCell.Offset(0, 45).Range("A1").Select
    ActiveCell.Value = dDate
    
    ActiveCell.Offset(0, -45).Range("A1").Select
    ActiveSheet.Calculate
    InvoiceDetail.InvoiceDifference.Value = ActiveCell.Text


    If ActiveCell.Value <> 0 Then
    
    MsgBox ("The invoice amount is not the same as the sale amount, please advise the relevant salesman.")


    End If
      
    ActiveCell.Rows("1:1").EntireRow.Select
    ActiveCell.Offset(0, 4).Range("A1").Select
    
    If ActiveCell.Value = 27 Then
   
   ActiveCell.Rows("1:1").EntireRow.Select
    ActiveCell.Range("A1:IP1").Select
    Selection.Interior.ColorIndex = 6
    ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveCell = 29
    ActiveCell.Offset(0, 1).Range("A1").Select
    
    Else
    
    ActiveCell.Rows("1:1").EntireRow.Select
    'ActiveCell.Range("A1:IP1").Select
    'Selection.Interior.ColorIndex = 6
    'ActiveCell.Offset(0, 4).Range("A1").Select
    'ActiveCell = 29
    ActiveCell.Offset(0, 1).Range("A1").Select
   
   End If
   
      Unload InvoiceDetail
    UPDATEMASTERSALES
    
End Sub
[CODE]

 

[James006]

Hi Tony,

Your new explanation makes it a bit clearer ...

It would appear you have not declared dDate as Public ...

So, you should try to insert, just before the line :
Activecell.Value =dDate
the following lines (in your CommandButton1 which populates the worksheet) :

dDate = DateSerial(Year(Date), Month(Date), Day(Date)) 
Me.InvoiceDate.Value = Format(Me.InvoiceDate.Value, "dd/mm/yyyy") 
dDate = Me.InvoiceDate.Value

HTH

 

[Tony Miall]

Thanks mate, as simple as that. appreciate it.