How to remove the seconds on a mirror = cell coded in VB?

[Joe Wildman]

Hello everyone, I created a button, when i click on it, it fills a cell with
the current time, i can edit the time after i click the button if needed, I
did some VB code where as that cell will = to cell C35, well that works but
the problem is it shows the seconds and that does not work. any Ideas?

Button:

Sub ss1()

[D8] = Now

End Sub

Also, I have this code that works fine but I am unable to edit the time when
there is a formula in the cell... =MONTH(NOW()) & "/" & DAY(NOW()) & "/" &
YEAR(NOW()) & " " & HOUR(NOW()) &
":" & MINUTE(NOW())

 

[CurlyDave]

Format your range to the time format you want or you could use this
code

Range("D8") = Format(Time, "h:mm AM/PM")

 

[Paul]

Add this as the next line of your code

[D8].NumberFormat = "h:mm AM/PM"

Paul

 

[Joe Wildman]

I also used with the code below, still shows seconds. I am using Private Sub
Worksheet_Change(ByVal Target As Range) so the cells are coded in VB to input
data in cell a1 and will = in cell a100 so it will show in the correct
format. Yes the seconds don't show up in the input cell but it shows the
seconds in the output cell.

Sub Button58_Click()
[A5].Value = DateSerial(Year(Now), Month(Now) + 1, 0)
ActiveCell.NumberFormat = "MM/DD/YYYY HH:MM"
End Sub

and/or

Not sure yet if i can get this to format right...

Sub Button58_Click()
[A5].Value = TimeSerial(Hour(Now), Minute(Now), Second(Now))
ActiveCell.NumberFormat = "HH:MM:SS"
End Sub

and

Sub Button1_Click()

[D8] = Now()
[D8].NumberFormat = "hh:mm"

End Sub

Add this as the next line of your code

[D8].NumberFormat = "h:mm AM/PM"

Paul

Hello everyone, I created a button, when i click on it, it fills a cell
with
the current time, i can edit the time after i click the button if needed,
I
did some VB code where as that cell will = to cell C35, well that works
but
the problem is it shows the seconds and that does not work. any Ideas?

Button:

Sub ss1()

[D8] = Now

End Sub

Also, I have this code that works fine but I am unable to edit the time
when
there is a formula in the cell... =MONTH(NOW()) & "/" & DAY(NOW()) & "/"
&
YEAR(NOW()) & " " & HOUR(NOW()) &
":" & MINUTE(NOW())

 

[Shane Devenshire]

Hi,

You are working way to hard

Sub Button58_Click()
[A5] = Format(Now,"MM/DD/YYYY HH:MM")
End Sub

which will show the date and time without seconds or

Sub test()
[A5] = Format(Now, "HH:MM")
End Sub

which will show the time without seconds

In both cases the seconds will not be in A5 and will not display if you have
other references to A5.

the problem with
[D8] = Now()
[D8].NumberFormat = "hh:mm"
is you are storing the seconds even though you aren't displaying them in D8.
So if you reference D8 with a formula, that formula will be using the
seconds portion along with the rest. Not a problem in the first two examples
of mine.

FYI - the default property of a cell or range is its Value so it is not
necessary to write [D8].Value or Range("D8").Value

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

I also used with the code below, still shows seconds. I am using Private Sub
Worksheet_Change(ByVal Target As Range) so the cells are coded in VB to input
data in cell a1 and will = in cell a100 so it will show in the correct
format. Yes the seconds don't show up in the input cell but it shows the
seconds in the output cell.

Sub Button58_Click()
[A5].Value = DateSerial(Year(Now), Month(Now) + 1, 0)
ActiveCell.NumberFormat = "MM/DD/YYYY HH:MM"
End Sub

and/or

Not sure yet if i can get this to format right...

Sub Button58_Click()
[A5].Value = TimeSerial(Hour(Now), Minute(Now), Second(Now))
ActiveCell.NumberFormat = "HH:MM:SS"
End Sub

and

Sub Button1_Click()

[D8] = Now()
[D8].NumberFormat = "hh:mm"

End Sub

Add this as the next line of your code

[D8].NumberFormat = "h:mm AM/PM"

Paul

Hello everyone, I created a button, when i click on it, it fills a cell
with
the current time, i can edit the time after i click the button if needed,
I
did some VB code where as that cell will = to cell C35, well that works
but
the problem is it shows the seconds and that does not work. any Ideas?

Button:

Sub ss1()

[D8] = Now

End Sub

Also, I have this code that works fine but I am unable to edit the time
when
there is a formula in the cell... =MONTH(NOW()) & "/" & DAY(NOW()) & "/"
&
YEAR(NOW()) & " " & HOUR(NOW()) &
":" & MINUTE(NOW())

 

[Joe Wildman]

Sorry, still shows the seconds, any time I put time data in the cell it shows
(auto formats) the output cell with seconds (I am using office 2003). I have
an idea, i want to create a text box object and a button object (as it will
be an input line anyway for many users) when I click on the button, it will
paste the current date in the text box object and it will not format the time
because it was not entered into a cell (so it does not auto format the date
with seconds) but also that text box object will = to the output cell so it
will show up right. Now I was able to create this but i never used the text
box object so I am not sure how to add that into the button VB code,

Hi,

You are working way to hard

Sub Button58_Click()
[A5] = Format(Now,"MM/DD/YYYY HH:MM")
End Sub

which will show the date and time without seconds or

Sub test()
[A5] = Format(Now, "HH:MM")
End Sub

which will show the time without seconds

In both cases the seconds will not be in A5 and will not display if you have
other references to A5.

the problem with
[D8] = Now()
[D8].NumberFormat = "hh:mm"
is you are storing the seconds even though you aren't displaying them in D8.
So if you reference D8 with a formula, that formula will be using the
seconds portion along with the rest. Not a problem in the first two examples
of mine.

FYI - the default property of a cell or range is its Value so it is not
necessary to write [D8].Value or Range("D8").Value

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

I also used with the code below, still shows seconds. I am using Private Sub
Worksheet_Change(ByVal Target As Range) so the cells are coded in VB to input
data in cell a1 and will = in cell a100 so it will show in the correct
format. Yes the seconds don't show up in the input cell but it shows the
seconds in the output cell.

Sub Button58_Click()
[A5].Value = DateSerial(Year(Now), Month(Now) + 1, 0)
ActiveCell.NumberFormat = "MM/DD/YYYY HH:MM"
End Sub

and/or

Not sure yet if i can get this to format right...

Sub Button58_Click()
[A5].Value = TimeSerial(Hour(Now), Minute(Now), Second(Now))
ActiveCell.NumberFormat = "HH:MM:SS"
End Sub

and

Sub Button1_Click()

[D8] = Now()
[D8].NumberFormat = "hh:mm"

End Sub

Add this as the next line of your code

[D8].NumberFormat = "h:mm AM/PM"

Paul


Hello everyone, I created a button, when i click on it, it fills a cell
with
the current time, i can edit the time after i click the button if needed,
I
did some VB code where as that cell will = to cell C35, well that works
but
the problem is it shows the seconds and that does not work. any Ideas?

Button:

Sub ss1()

[D8] = Now

End Sub

Also, I have this code that works fine but I am unable to edit the time
when
there is a formula in the cell... =MONTH(NOW()) & "/" & DAY(NOW()) & "/"
&
YEAR(NOW()) & " " & HOUR(NOW()) &
":" & MINUTE(NOW())

 

[Joe Wildman]

Also want to add that when I link the text box to the cell in the object
propertys the output cell does not populate with the data till i double click

Sorry, still shows the seconds, any time I put time data in the cell it shows
(auto formats) the output cell with seconds (I am using office 2003). I have
an idea, i want to create a text box object and a button object (as it will
be an input line anyway for many users) when I click on the button, it will
paste the current date in the text box object and it will not format the time
because it was not entered into a cell (so it does not auto format the date
with seconds) but also that text box object will = to the output cell so it
will show up right. Now I was able to create this but i never used the text
box object so I am not sure how to add that into the button VB code,

Hi,

You are working way to hard

Sub Button58_Click()
[A5] = Format(Now,"MM/DD/YYYY HH:MM")
End Sub

which will show the date and time without seconds or

Sub test()
[A5] = Format(Now, "HH:MM")
End Sub

which will show the time without seconds

In both cases the seconds will not be in A5 and will not display if you have
other references to A5.

the problem with
[D8] = Now()
[D8].NumberFormat = "hh:mm"
is you are storing the seconds even though you aren't displaying them in D8.
So if you reference D8 with a formula, that formula will be using the
seconds portion along with the rest. Not a problem in the first two examples
of mine.

FYI - the default property of a cell or range is its Value so it is not
necessary to write [D8].Value or Range("D8").Value

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

I also used with the code below, still shows seconds. I am using Private Sub
Worksheet_Change(ByVal Target As Range) so the cells are coded in VB to input
data in cell a1 and will = in cell a100 so it will show in the correct
format. Yes the seconds don't show up in the input cell but it shows the
seconds in the output cell.

Sub Button58_Click()
[A5].Value = DateSerial(Year(Now), Month(Now) + 1, 0)
ActiveCell.NumberFormat = "MM/DD/YYYY HH:MM"
End Sub

and/or

Not sure yet if i can get this to format right...

Sub Button58_Click()
[A5].Value = TimeSerial(Hour(Now), Minute(Now), Second(Now))
ActiveCell.NumberFormat = "HH:MM:SS"
End Sub

and

Sub Button1_Click()

[D8] = Now()
[D8].NumberFormat = "hh:mm"

End Sub



:

Add this as the next line of your code

[D8].NumberFormat = "h:mm AM/PM"

Paul


Hello everyone, I created a button, when i click on it, it fills a cell
with
the current time, i can edit the time after i click the button if needed,
I
did some VB code where as that cell will = to cell C35, well that works
but
the problem is it shows the seconds and that does not work. any Ideas?

Button:

Sub ss1()

[D8] = Now

End Sub

Also, I have this code that works fine but I am unable to edit the time
when
there is a formula in the cell... =MONTH(NOW()) & "/" & DAY(NOW()) & "/"
&
YEAR(NOW()) & " " & HOUR(NOW()) &
":" & MINUTE(NOW())