Hello all,
I have a worksheet and for selected cells I have set up a vba code so that when you double click in these cells it enters the current time. The vba code is set up to minus 1 hour (zulu time). I have also formatted the cell to "custom - hh:mmz". This works fantastically with the double click entering the time only in the format 13:25z for example. With zulu time ending soon I am preparing the sheet for this. The minus time part is easy (just deleting part of the vba code.)
The issue I am having is in formatting the cell time format. Whenever I select "hh:mm" from the "Custom" list in the cell format and then double click I get ###### showing. If I extend the width of the column it is inputting the date AND time where in the previous setup (zulu hh:mmz) it only inputs the time on double click.....
If I go to "Custom" on the format cell list and enter hh:mm and put a full stop after the hh:mm then it only enters the time in to the cell. Am I missing something with the formatting ?? I thought it would just be select hh:mm. I'm after the cell showing the existing time in the cell as 13:25 for example.
The "double click" vba code is.... with me just deleting the " - (1/24), giving me the normal time in the cell.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Date stamp with zulu
If Not Intersect(Target, Range("B4:B249")) Is Nothing Then
Cancel = True
Target.Value = Now() - (1 / 24)
End If
End Sub
The screenshot below shows the ###### B5 cell which is the format cell set as hh:mm. The B6 cell format is hh:mmz. The second table is the the column extended showing how its entering the cells differently on double click.
Thanks all
T
I have a worksheet and for selected cells I have set up a vba code so that when you double click in these cells it enters the current time. The vba code is set up to minus 1 hour (zulu time). I have also formatted the cell to "custom - hh:mmz". This works fantastically with the double click entering the time only in the format 13:25z for example. With zulu time ending soon I am preparing the sheet for this. The minus time part is easy (just deleting part of the vba code.)
The issue I am having is in formatting the cell time format. Whenever I select "hh:mm" from the "Custom" list in the cell format and then double click I get ###### showing. If I extend the width of the column it is inputting the date AND time where in the previous setup (zulu hh:mmz) it only inputs the time on double click.....
If I go to "Custom" on the format cell list and enter hh:mm and put a full stop after the hh:mm then it only enters the time in to the cell. Am I missing something with the formatting ?? I thought it would just be select hh:mm. I'm after the cell showing the existing time in the cell as 13:25 for example.
The "double click" vba code is.... with me just deleting the " - (1/24), giving me the normal time in the cell.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Date stamp with zulu
If Not Intersect(Target, Range("B4:B249")) Is Nothing Then
Cancel = True
Target.Value = Now() - (1 / 24)
End If
End Sub
The screenshot below shows the ###### B5 cell which is the format cell set as hh:mm. The B6 cell format is hh:mmz. The second table is the the column extended showing how its entering the cells differently on double click.
Thanks all
T