Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove the seconds on a mirror = cell coded in VB?
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()) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove the seconds on a mirror = cell coded in VB?
Format your range to the time format you want or you could use this
code Range("D8") = Format(Time, "h:mm AM/PM") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove the seconds on a mirror = cell coded in VB?
Add this as the next line of your code
[D8].NumberFormat = "h:mm AM/PM" Paul "Joe Wildman" wrote in message ... 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()) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove the seconds on a mirror = cell coded in VB?
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 "Paul" wrote: Add this as the next line of your code [D8].NumberFormat = "h:mm AM/PM" Paul "Joe Wildman" wrote in message ... 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()) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove the seconds on a mirror = cell coded in VB?
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 "Joe Wildman" wrote: 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 "Paul" wrote: Add this as the next line of your code [D8].NumberFormat = "h:mm AM/PM" Paul "Joe Wildman" wrote in message ... 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()) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove the seconds on a mirror = cell coded in VB?
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, "Shane Devenshire" wrote: 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 "Joe Wildman" wrote: 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 "Paul" wrote: Add this as the next line of your code [D8].NumberFormat = "h:mm AM/PM" Paul "Joe Wildman" wrote in message ... 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()) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove the seconds on a mirror = cell coded in VB?
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 on the cell, "Joe Wildman" wrote: 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, "Shane Devenshire" wrote: 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 "Joe Wildman" wrote: 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 "Paul" wrote: Add this as the next line of your code [D8].NumberFormat = "h:mm AM/PM" Paul "Joe Wildman" wrote in message ... 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()) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to remove the seconds on a VB mirror = cell | Excel Worksheet Functions | |||
Color-coded cell references | Excel Discussion (Misc queries) | |||
Convert State abbreviations color coded cell | Excel Worksheet Functions | |||
Convert Hard-coded cell values to constants | Excel Discussion (Misc queries) | |||
Color Coded cell references. | Excel Discussion (Misc queries) |