Mmmm not understandiing this!!!
I have a date format ("d-mmm-yyyy") in a cell. showing 26-Nov-2005
The same cell shows 26/11/05" in the formula bar.
Obviously the cell is formatted as a Date.
Can i programmatically change the cell value to read "26-Nov-2005" but have it formatted as Text. So the date, although it is a date, is text and not a date format (Hope that makes sense).
It is not essential that the format is of a Date but purely that the value shown is looking like a date eg. "26-Nov-2005".
This is the code i am using to populate my worksheet.
Code:
Private Sub CommandButton1_Click()
Dim c As Variant
Dim pt As Variant
CloseDate = Format(DateValue(EndDateTextBox.Value), "d/mmm/yyyy")
RefNo = cbListWorkOrderNumbers.Value ' From TextBox
With Workbooks("BB Closure prep.xls").Worksheets("Schedules").Columns("B:B")
Set c = .Find(RefNo, LookIn:=xlValues, after:=Range("B3"), SearchDirection:=xlPrevious)
If Not c Is Nothing Then
c.Offset(0, -1).Value = NoticeType
c.Offset(0, 2).Value = JobCodeComboBox.Value
c.Offset(0, 4).HorizontalAlignment = xlLeft
c.Offset(0, 4).Value = CloseDate
c.Offset(0, 14).Value = AreaCodeComboBox.Value
Else
pt = MsgBox("NO SUCH REF No EXISTS", vbCritical, "ERROR")
End If
End With
End Sub
NB: Initially the date is entered in format "dd-mm-yy" and converted to "d-mmm-yyyy". But i would like to use this line:
c.Offset(0, 4).Value = CloseDate
to put a text value on the worksheet but the text being whatever the date was eg. "26-Nov-2005".
Please could anybody help.
TIA