Date changing to Text in a VBA User Form

  LeeA 10:08 11 Apr 08


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 08

Similar issue click here

  LeeA 12:41 11 Apr 08

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 08

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 08

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 08


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 08

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 08

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 08

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 08

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 08


that did the trick

thanks for all your help

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

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Framestore’s haunting post-WWII title sequence for new BBC series SS-GB

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…