Hi All,

I have a very simple UserForm with 4 textboxes and an OK Button on it.

One of the textboxes is for a date which is inputted to the sheet on
clicking OK with the following code:

Private Sub cmdOK_Click()

ActiveCell.Value = tbxDate.Value
ActiveCell.Offset(0, 1).Value = tbxSupplier.Value
ActiveCell.Offset(0, 2).Value = tbxInvoiceTotal.Value
ActiveCell.Offset(0, 3).Value = tbxExtras.Value

End Sub

My difficulty is that if the date is put into the form in the format
dd/mm/yyyy the above code puts it into the active cell in that format.
But I really want the format to be dd-mmm-yyyy. Even if after the form
has been used I try to apply that format to the cell it remains as
dd/mm/yyyy.

If I input the date on the actual sheet as dd/mm/yyyy the format
changes to dd-mmm-yyyy.

Any advice wold be appreciated

Seamus


--
SOS
------------------------------------------------------------------------
SOS's Profile: http://www.excelforum.com/member.php...fo&userid=5406
View this thread: http://www.excelforum.com/showthread...hreadid=513168

ActiveCell.Value = cDate(tbxDate.Value)
ActiveCell.NumberFormat = "dd-mmm-yyyy"
ActiveCell.Offset(0, 1).Value = tbxSupplier.Value
ActiveCell.Offset(0, 2).Value = tbxInvoiceTotal.Value
ActiveCell.Offset(0, 3).Value = tbxExtras.Value

--
Regards,
Tom Ogilvy


"SOS" wrote in message
...

Hi All,

I have a very simple UserForm with 4 textboxes and an OK Button on it.

One of the textboxes is for a date which is inputted to the sheet on
clicking OK with the following code:

Private Sub cmdOK_Click()

ActiveCell.Value = tbxDate.Value
ActiveCell.Offset(0, 1).Value = tbxSupplier.Value
ActiveCell.Offset(0, 2).Value = tbxInvoiceTotal.Value
ActiveCell.Offset(0, 3).Value = tbxExtras.Value

End Sub

My difficulty is that if the date is put into the form in the format
dd/mm/yyyy the above code puts it into the active cell in that format.
But I really want the format to be dd-mmm-yyyy. Even if after the form
has been used I try to apply that format to the cell it remains as
dd/mm/yyyy.

If I input the date on the actual sheet as dd/mm/yyyy the format
changes to dd-mmm-yyyy.

Any advice wold be appreciated

Seamus


--
SOS
------------------------------------------------------------------------
SOS's Profile:
http://www.excelforum.com/member.php...fo&userid=5406
View this thread: http://www.excelforum.com/showthread...hreadid=513168

Thanks to both Toppers and Tom - both solutions work well.

Thanks Again

Seamus


--
SOS
------------------------------------------------------------------------
SOS's Profile: http://www.excelforum.com/member.php...fo&userid=5406
View this thread: http://www.excelforum.com/showthread...hreadid=513168

Try this ... (dates are real pain!):

ActiveCell.Value = Format(CDate(tbxDate.Text), "dd-mmm-yyyy")

"SOS" wrote:


Hi All,

I have a very simple UserForm with 4 textboxes and an OK Button on it.

One of the textboxes is for a date which is inputted to the sheet on
clicking OK with the following code:

Private Sub cmdOK_Click()

ActiveCell.Value = tbxDate.Value
ActiveCell.Offset(0, 1).Value = tbxSupplier.Value
ActiveCell.Offset(0, 2).Value = tbxInvoiceTotal.Value
ActiveCell.Offset(0, 3).Value = tbxExtras.Value

End Sub

My difficulty is that if the date is put into the form in the format
dd/mm/yyyy the above code puts it into the active cell in that format.
But I really want the format to be dd-mmm-yyyy. Even if after the form
has been used I try to apply that format to the cell it remains as
dd/mm/yyyy.

If I input the date on the actual sheet as dd/mm/yyyy the format
changes to dd-mmm-yyyy.

Any advice wold be appreciated

Seamus


--
SOS
------------------------------------------------------------------------
SOS's Profile: http://www.excelforum.com/member.php...fo&userid=5406
View this thread: http://www.excelforum.com/showthread...hreadid=513168