Date changing to Text in a VBA User Form

  LeeA 10:08 11 Apr 2008


I have a user form where ppl choose a date from a drop down list.But it populates the cell with a number.
Is there a way to stop this from happening


  VoG II 10:45 11 Apr 2008

Similar issue click here

  LeeA 12:41 11 Apr 2008

heres my code,

i added the code suggested in the other topic, but im not sure im applying right

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the 'close form' button!"
End If
End Sub
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("National Order Tracking")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.txtOrderNo.Value) = "" Then
MsgBox "Please enter an Order No. or if you do not require when then enter N/A"
Exit Sub
End If

'check for a part number
If Trim(Me.txtCustomerID.Value) = "" Then
MsgBox "Please enter a Customer ID"
Exit Sub
End If

Dim mydate As Date
mydate = Me.cmbMonth.Value
Sheets("National Order Tracking").Range("M10:M10000").Value = mydate

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtOrderNo.Value
ws.Cells(iRow, 2).Value = Me.txtCustomerID.Value
ws.Cells(iRow, 3).Value = Me.txtContractID.Value
ws.Cells(iRow, 4).Value = Me.cmbAccountManager.Value
ws.Cells(iRow, 7).Value = Me.txtContactName.Value
ws.Cells(iRow, 8).Value = Me.txtCustomerName.Value
ws.Cells(iRow, 9).Value = Me.txtEmailAddress.Value
ws.Cells(iRow, 10).Value = Me.txtOrderLine.Value
ws.Cells(iRow, 11).Value = Me.cmbClassifiedBusAtoZ.Value
ws.Cells(iRow, 12).Value = Me.cmbDirectory.Value
ws.Cells(iRow, 13).Value = Me.cmbMonth.Value
ws.Cells(iRow, 15).Value = Me.cmbClassification.Value
ws.Cells(iRow, 16).Value = Me.cmbAdType.Value
ws.Cells(iRow, 17).Value = Me.txtGrossPrice.Value
ws.Cells(iRow, 19).Value = Me.txtNetPrice.Value
ws.Cells(iRow, 20).Value = Me.cmbPromo.Value
ws.Cells(iRow, 21).Value = Me.txtRSC.Value

'clear the data
Me.txtOrderLine.Value = ""
Me.cmbClassifiedBusAtoZ.Value = ""
Me.cmbDirectory.Value = ""
Me.cmbMonth.Value = ""
Me.cmbClassification.Value = ""
Me.cmbAdType.Value = ""
Me.txtGrossPrice.Value = ""
Me.txtNetPrice.Value = ""
Me.cmbPromo.Value = ""
Me.txtRSC.Value = ""

End Sub

  VoG II 13:33 11 Apr 2008

Are you really adding the same date to nearly 10,000 rows?

As far as I can see, your code (for the date) should work. What happens? - do you get a date or a number or text?

  LeeA 13:41 11 Apr 2008

when i choose a date in the user form and add it to the spreadsheet its still putting numbers in instead.

Sheets("National Order Tracking").Range("M10:M10000").Value = mydate

what do i need to put in here to target just one cell going down each row

  VoG II 13:53 11 Apr 2008


With Sheets("National Order Tracking").Range("M10:M10000")
.Value = mydate
.NumberFormat = "mm/dd/yyyy"
End With

"what do i need to put in here to target just one cell going down each row" - I'm not sure what you mean.

  LeeA 14:08 11 Apr 2008

awesome tried that and its working

now when i choose a date from the drop down list in the user form. it's still showing up as a number is there a way to get this to display as a date?

thanks for all your help by the way

  VoG II 14:56 11 Apr 2008

I tested this and it seems to work

In the UserForm's code module

Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Value, "dd/mm/yyyy")
End Sub

Private Sub CommandButton1_Click()
End Sub

and in a regular module to test it

Sub zzzzzzz()
Dim mydate As Date
UserForm1.ComboBox1.RowSource = Sheets("Sheet1").Range("A1:A10").Address(external:=True)
mydate = UserForm1.ComboBox1.Value
Unload UserForm1
MsgBox mydate
End Sub

  LeeA 15:15 11 Apr 2008

hi ive tried putting the code into mine but i still cant seem to get it too work

can you give me an idea (maybe highlight) what parts i need to replace with my references

i have tried figuring it out myself but to no avail.

  VoG II 16:35 11 Apr 2008

I think that all you need to do is in the Visual Basic Editor, right click the combo box and select View Code. Then put in this code

Private Sub cmbMonth_Change()
cmbMonth.Value = Format(cmbMonth.Value, "dd/mm/yyyy")
End Sub

  LeeA 09:41 14 Apr 2008


that did the trick

thanks for all your help

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Best of the Grad Shows 2017: University of the West of England (UWE)

Best value Mac: Which is the best £1249 Mac to buy

Les meilleures GoPro 2017