Private Sub CommandButton2_Click()
' missing code here
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
' End With
ActiveCell.Value = Now
ActiveCell.Numberformat = "dd.mm.yyyy hh:mm"
End Sub
so use the same method that works each time.
--
Regards,
Tom Ogilvy
--
Regards,
Tom Ogilvy
"zzxxcc" wrote:
Thank you Tom and Luca,
Nice Date/Time page you refered to. Unfortunately I'm not yet an expert on
VBA, but I have ordered John Walkenbach's book. I currently copy and paste
codes I find on this excellent page.
Can you propose a modification of my code?
--
Thanks
Tom Ogilvy skrev:
Yes, you are correct that it does not seem to return the formatted length.
Nonetheless, the OP's problem seems to be that one of his functions is
putting a text string in the cell and the other is putting a value in the
cell.
he does need to understand how dates/times are stored.
http://www.cpearson.com/Excel/datetime.htm#SerialDates
when assigning a date to a cell from vba it is best to assign the date
serial to the cell and then format the cell as you want it to appear.
--
Regards,
Tom Ogilvy
"Luca Brasi" wrote:
I think the LEN() function returns the length of the value without any
formatting. The unformatted value for NOW() is a number like
39366.6878256944
Therefore LEN always returns 16 for a date value. The applied cell
format has no influence.
zzxxcc wrote:
I also get 16 on both types of returned data. (The wrong and the correct).
That's because it is shown as 16 charters and spaces in the cell. Stll if you
go into the formula bar on top of the Excel window you can count 20 charters
and spaces.
Forget about counting. Just lokk at the code. Something is wrong, but I
can't see it. Here is a code that does a different task, but returns a
correct date / time value: (the full code is IRRELEVANT, just a reference):
Option Explicit
Private Sub CommandButton1_Click()
Dim NextCell As Range
With Me
Set NextCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
End With
With NextCell
.Value = Now
.NumberFormat = "dd.mm.yyyy hh:mm"
End With
End Sub