Userform Date Variables issue

[Nameless_87]

Hello all,

This will probably have a very simple solution but its stumping me atm.

I have a userform which puts dates 2 columns but the date I input keeps coming back as a time.

I want to input the date on the userform as follows : DD/MM/YYYY

This is what I have so far but as I said the date comes back on the sheet as a time

Private Sub CmdBtnOKS1_Click()
Dim TxtBoxInvoiceDate As Date
Dim TxtBoxAmount As Currency
Dim TxtBoxGRNDate As Date

 

[RoryA]

How do you put it into the sheet and what format do the cells have?

 

[Nameless_87]

The format the cells origially have is 'Short Date' but when the userform dumps the data onto the sheet it come back as 'Custom'

Apologies I was ment to attach the entire Userform.

 Private Sub CmdBtnOKS1_Click()
Dim TxtBoxInvoiceDate As Date
Dim TxtBoxAmount As Currency
Dim TxtBoxGRNDate As Date
 
'Set TxtBoxInvoiceDate = ActiveSheet("A:A")
'Set TxtBoxAmount = ActiveSheet("F:F")
'Set TxtBoxGRNDate = ActiveSheet("I:I")

 If TxtBoxSupplier = "" Then
    MsgBox "Please input supplier"
  Else
  ' Go to the last row on the worksheet
   Cells(ActiveSheet.Rows.Count, 1).Select
  ' Go back to the last populated row on the worksheet
  Selection.End(xlUp).Select
  ' Move down a row
  ActiveCell.Offset(1, 0).Select
  ' Enter the value
  ActiveCell.Value = TxtBoxInvoiceDate
  
  
  Cells(ActiveSheet.Rows.Count, 2).Select
  Selection.End(xlUp).Select
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = TxtBoxSupplier.Value
  
  Cells(ActiveSheet.Rows.Count, 3).Select
  Selection.End(xlUp).Select
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = TxtBoxInvoiceNo.Value
    
  Cells(ActiveSheet.Rows.Count, 4).Select
  Selection.End(xlUp).Select
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = TxtBoxMoreDetails.Value
  
  Cells(ActiveSheet.Rows.Count, 5).Select
  Selection.End(xlUp).Select
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = TxtBoxCostCode.Value
  
  Cells(ActiveSheet.Rows.Count, 6).Select
  Selection.End(xlUp).Select
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = TxtBoxAmount
  
  Cells(ActiveSheet.Rows.Count, 7).Select
  Selection.End(xlUp).Select
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = TxtBoxReqNumber.Value
  
  Cells(ActiveSheet.Rows.Count, 8).Select
  Selection.End(xlUp).Select
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = TxtBoxPONo.Value
  
  Cells(ActiveSheet.Rows.Count, 9).Select
  Selection.End(xlUp).Select
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = TxtBoxGRNDate
     
  Cells(ActiveSheet.Rows.Count, 11).Select
  Selection.End(xlUp).Select
  ActiveCell.Offset(1, 0).Select
  ActiveCell.Value = Environ$("UserName")
  
  
If MsgBox("Would you like to enter another invoice?", vbYesNo + vbQuestion, "Close") = vbNo Then
    End
    Else
      'TxtBoxInvoiceDate.Date = ""
      TxtBoxSupplier.Value = ""
      TxtBoxInvoiceNo.Value = ""
      TxtBoxMoreDetails.Value = ""
      TxtBoxCostCode.Value = ""
      'TxtBoxAmount = ""
      TxtBoxReqNumber.Value = ""
      TxtBoxPONo.Value = ""
      'TxtBoxGRNDate = ""
      
     End If
    
 End If
 
End Sub

 

[RoryA]

Try this instead (I've assumed that the three variables you declared are the same names as actual controls on the form)

Private Sub CmdBtnOKS1_Click()
    Dim lRow             As Long


    If TxtBoxSupplier = "" Then
        MsgBox "Please input supplier"
    Else
        ' Find the next blank row in column A
        lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
        ' populate that row on the worksheet
        Cells(lRow, "A").Value = CDate(TxtBoxInvoiceDate)
        Cells(lRow, "B").Value = TxtBoxSupplier.Value
        Cells(lRow, "C").Value = TxtBoxInvoiceNo.Value
        Cells(lRow, "D").Value = TxtBoxMoreDetails.Value
        Cells(lRow, "E").Value = TxtBoxCostCode.Value
        Cells(lRow, "F").Value = CDbl(TxtBoxAmount)
        Cells(lRow, "G").Value = TxtBoxReqNumber.Value
        Cells(lRow, "H").Value.Value = TxtBoxPONo.Value
        Cells(lRow, "I").Value = CDate(TxtBoxGRNDate)
        Cells(lRow, "K").Value = Environ$("UserName")


        If MsgBox("Would you like to enter another invoice?", vbYesNo + vbQuestion, "Close") = vbNo Then
            Unload Me
        Else
            'TxtBoxInvoiceDate.Date = ""
            TxtBoxSupplier.Value = ""
            TxtBoxInvoiceNo.Value = ""
            TxtBoxMoreDetails.Value = ""
            TxtBoxCostCode.Value = ""
            'TxtBoxAmount = ""
            TxtBoxReqNumber.Value = ""
            TxtBoxPONo.Value = ""
            'TxtBoxGRNDate = ""


        End If


    End If


End Sub

 

[Nameless_87]

Rory you are a legend!

It works perfectly, Thanks a load!

It amazes me how quick you managed to do all that hehe.

 

[RoryA]

It amazes me how quick you managed to do all that hehe.

Unfortunately I can type faster than I can think.