VB Date Format

W

Woodi2

I have a userform that has a command button that displays a date within an
active cell and displays this date in textbox 1.
Private Sub CommandButton1_Click()
Me.TextBox1.Value = ActiveCell.Value
End Sub
The date in the cell is in the format dd/mm/yyyy. When it is displayed in
the textbox it displays it as mm/dd/yyyy.
How can I change this around to the format dd/mm/yyyy?
I have tried
Me.TextBox1.Value = Format(Date, "dd/mm/yy")
but that displays the current date, not what is in the cell.
 
B

Bob Phillips

Private Sub CommandButton1_Click()
Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy")
End Sub
 
M

Mike H

Hi,

Try it like this

Private Sub CommandButton1_Click()
If IsDate(ActiveCell.Value) Then
Me.TextBox1.Value = Format(ActiveCell.Value, "dd/mm/yyyy")
Else
MsgBox "The active cell isn't a valid date"
End If
End Sub

Mike
 
W

Woodi2

Thanks Bob, thats great.
How would I tie it in with this
Me.TextBox1.Value = ActiveCell.Offset(0, -1).Value.
Where would the code be added to change the format?

Apologies but since writing it I need it to be offset 1 cell.
Thanks
Ian
 
B

Bob Phillips

Private Sub CommandButton1_Click()
Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd.mm/yy")
End Sub
 
M

Mike H

You would do it like this and I still recommend you check for a valid date

Private Sub CommandButton1_Click()
If IsDate(ActiveCell.Offset(0, -1).Value) Then
Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd/mm/yyyy")
Else
MsgBox "The active cell isn't a valid date"
End If
End Sub

Mike
 
W

Woodi2

Thanks Guys, very helpful.

How do I do this when writing back to the sheet.
i.e. i write back using another commandbutton as follows
ActiveCell.Offset(0, -1) = TextBox1.Value
How do i add into this code to change the data entry to dd/mm/yyyy.
i.e. if a user enters the date in textbox 1 as 5/3/09, how can I convert
that to 05/03/2009.
Thanks again
 
B

Bob Phillips

ActiveCell.Offset(0, -1) = CDate(TextBox1.Value)

and format the cell as required.
 
A

Ankur Jain

HI,

I have no idea about programming. but I want to learn excel VBA coding.

Please help me out on this....



Bob Phillips wrote:

Private Sub CommandButton1_Click()Me.TextBox1.Value = Format(ActiveCell.
11-Mar-09

Private Sub CommandButton1_Click(
Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy"
End Su

-
_________________________________
HT

Bob

Previous Posts In This Thread:

VB Date Format
I have a userform that has a command button that displays a date within an
active cell and displays this date in textbox 1.
Private Sub CommandButton1_Click(
Me.TextBox1.Value = ActiveCell.Valu
End Su
The date in the cell is in the format dd/mm/yyyy. When it is displayed in
the textbox it displays it as mm/dd/yyyy
How can I change this around to the format dd/mm/yyyy
I have tried
Me.TextBox1.Value = Format(Date, "dd/mm/yy"
but that displays the current date, not what is in the cell.

Private Sub CommandButton1_Click()Me.TextBox1.Value = Format(ActiveCell.
Private Sub CommandButton1_Click(
Me.TextBox1.Value = Format(ActiveCell.Value, "dd.mm/yy"
End Su

-
_________________________________
HT

Bob

Hi,Try it like thisPrivate Sub CommandButton1_Click()If IsDate(ActiveCell.
Hi

Try it like thi

Private Sub CommandButton1_Click(
If IsDate(ActiveCell.Value) The
Me.TextBox1.Value = Format(ActiveCell.Value, "dd/mm/yyyy"
Els
MsgBox "The active cell isn't a valid date
End I
End Su

Mik

:

Thanks Bob, thats great.How would I tie it in with thisMe.TextBox1.
Thanks Bob, thats great
How would I tie it in with thi
Me.TextBox1.Value = ActiveCell.Offset(0, -1).Value
Where would the code be added to change the format

Apologies but since writing it I need it to be offset 1 cell
Thank
Ia

:

Private Sub CommandButton1_Click()Me.TextBox1.Value = Format(ActiveCell.
Private Sub CommandButton1_Click(
Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd.mm/yy"
End Su

-
_________________________________
HT

Bob

You would do it like this and I still recommend you check for a valid
You would do it like this and I still recommend you check for a valid dat

Private Sub CommandButton1_Click(
If IsDate(ActiveCell.Offset(0, -1).Value) The
Me.TextBox1.Value = Format(ActiveCell.Offset(0, -1).Value, "dd/mm/yyyy"
Els
MsgBox "The active cell isn't a valid date
End I
End Su

Mik

:

Thanks Guys, very helpful.How do I do this when writing back to the sheet.i.e.
Thanks Guys, very helpful

How do I do this when writing back to the sheet
i.e. i write back using another commandbutton as follow
ActiveCell.Offset(0, -1) = TextBox1.Valu
How do i add into this code to change the data entry to dd/mm/yyyy
i.e. if a user enters the date in textbox 1 as 5/3/09, how can I convert
that to 05/03/2009
Thanks agai

:

ActiveCell.Offset(0, -1) = CDate(TextBox1.
ActiveCell.Offset(0, -1) = CDate(TextBox1.Value

and format the cell as required

-
_________________________________
HT

Bob


Submitted via EggHeadCafe - Software Developer Portal of Choice
Access Denied Errors When compiling .NET Solution
http://www.eggheadcafe.com/tutorial...2-a709466e0c68/access-denied-errors-when.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel is changing date format incorrectly 7
International Date formatting 4
Date on UserForm? 5
Pasting a date to a cell from UserForm 2
Dates converted into text 5
Date formatting 1
Today's Date 1
Different Date formats in text to be recognised as date value 8

Top