How to remove the seconds on a VB mirror = cell

  • Thread starter Thread starter Joe Wildman
  • Start date Start date
J

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())
 
Does not work... no matter what I do it always shows the seconds in the = cell

Sub Button58_Click()
ActiveCell.Value = Format(Now(), "mm/dd/yyyy hh:mm")
End Sub


Joel said:
you just need to format the cell

activecell.numberformat = "mm/dd/yy hh:mm"

Joe Wildman said:
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())
Click to expand...
Click to expand...
 
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

Joe Wildman said:
Does not work... no matter what I do it always shows the seconds in the = cell

Sub Button58_Click()
ActiveCell.Value = Format(Now(), "mm/dd/yyyy hh:mm")
End Sub


Joel said:
you just need to format the cell

activecell.numberformat = "mm/dd/yy hh:mm"

Joe Wildman said:
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())
Click to expand...
Click to expand...
Click to expand...
 
Maybe...

Option Explicit
Sub testme()
With ActiveCell 'with activesheet.range("A5")
.NumberFormat = "mm/dd/yyyy hh:mm"
.Value = Now
End With
End Sub




Joe said:
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

Joe Wildman said:
Does not work... no matter what I do it always shows the seconds in the = cell

Sub Button58_Click()
ActiveCell.Value = Format(Now(), "mm/dd/yyyy hh:mm")
End Sub


Joel said:
you just need to format the cell

activecell.numberformat = "mm/dd/yy hh:mm"

:

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())
Click to expand...
Click to expand...
Click to expand...
 
No still does not work, still get the seconds, I can only see 2 options
create a text box in the control toolbar and then have it paste the date in
there so it does not auto populate the cells with the seconds or if there is
a formula that can be used to round down the seconds so the cell will not
show the seconds, but I need the option to show the current date and military
time and edit it in the text box or cell, and will not display the seconds,
as the VBA code will pick up all the data despite in the input still it will
not show the seconds. You can tell if it going to show the seconds if you
double click on the input cell and see it populate eg. 12:00:00, the VBA
coded cell will show it all. I guess i give up, thanks for your help everyone

Dave Peterson said:
Maybe...

Option Explicit
Sub testme()
With ActiveCell 'with activesheet.range("A5")
.NumberFormat = "mm/dd/yyyy hh:mm"
.Value = Now
End With
End Sub




Joe said:
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

Joe Wildman said:
Does not work... no matter what I do it always shows the seconds in the = cell

Sub Button58_Click()
ActiveCell.Value = Format(Now(), "mm/dd/yyyy hh:mm")
End Sub


:

you just need to format the cell

activecell.numberformat = "mm/dd/yy hh:mm"

:

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())
Click to expand...
Click to expand...
Click to expand...
 
Back
Top