UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Convert Time To 12-hr Am/pm Format In VBA, Office 2013    
 
   
bakersburg9
post Nov 24 2017, 01:48 AM
Post#1



Posts: 5,306
Joined: 2-November 04
From: Downey, CA


This procedure puts the date and time 3 cells to the right of the current cell:

CODE
ActiveCell.Offset(0, 3) = Now()


I tried to convert to 12 hour AM/PM format in code - got error message, wrong syntax:
'
CODE
ActiveCell.Offset(0, 3) = Format(TimeValue(Now), 'hh:mm AM/PM')

confused.gif

Also, I want to be able to type a letter or number, and have it put the date/time in the cell 3 cells to the right, but can't get the syntax right - is there a way to have code run on worksheet change for one cell ?

Any help would be greatly appreciated...
Attached File(s)
Attached File  12hourFormatWIP.zip ( 15.43K )Number of downloads: 2
 
Go to the top of the page
 
DanielPineault
post Nov 24 2017, 06:54 AM
Post#2


UtterAccess VIP
Posts: 6,273
Joined: 30-June 11



Try
CODE
ActiveCell.Offset(0, 3) = Format(TimeValue(Now), "hh:mm AM/PM")
Go to the top of the page
 
DanielPineault
post Nov 24 2017, 06:58 AM
Post#3


UtterAccess VIP
Posts: 6,273
Joined: 30-June 11



You can use the Worksheet_Change event to react to a change in a given cell or range. You can do something along the lines of

CODE
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C4")) Is Nothing Then YourProcedureName
End Sub


CODE
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C4")) Is Nothing Then
        'YourVBACode
    End if
End Sub
Go to the top of the page
 
bakersburg9
post Nov 25 2017, 04:57 PM
Post#4



Posts: 5,306
Joined: 2-November 04
From: Downey, CA


Daniel,
That works great, but what if I want to add the time, like, Sat. Nov 25- 1:57 pm

I don't even care if I need a separate line, to have a separate date, like 11/25 in one column, and 1:57 in the next column. I was playing around with it, and got thing like Dec 25, etc - couldn't figure THAT out...

Steve
Go to the top of the page
 
bakersburg9
post Nov 25 2017, 05:03 PM
Post#5



Posts: 5,306
Joined: 2-November 04
From: Downey, CA


Daniel,
As for the worksheet change, I tried this:

CODE
Sub timeStampInput()

'Input date of input
ActiveCell.Offset(0, 3) = Format(TimeValue(Now), "hh:mm AM/PM")

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("f4")) Is Nothing Then timeStampInput

End Sub
... but it didn't work - I cleared the value in cell F4, expecting my procedure to run, and it didn't - perhaps operator error confused.gif
Go to the top of the page
 
bakersburg9
post Nov 26 2017, 05:48 PM
Post#6



Posts: 5,306
Joined: 2-November 04
From: Downey, CA


Daniel,
I tried your code - didn't work - I expected that when I deleted the contents (or cleared the contents) of the target cell, my code would run - but nothing happened (See attached).
Attached File(s)
Attached File  OnChange.zip ( 16.67K )Number of downloads: 2
 
Go to the top of the page
 
DanielPineault
post Nov 26 2017, 06:07 PM
Post#7


UtterAccess VIP
Posts: 6,273
Joined: 30-June 11



You just have the code in the wrong module (just a very minor change). Look over the attached.
Attached File(s)
Attached File  OnChange_01.zip ( 16.02K )Number of downloads: 4
 
Go to the top of the page
 
bakersburg9
post Nov 26 2017, 08:35 PM
Post#8



Posts: 5,306
Joined: 2-November 04
From: Downey, CA


Daniel, THANKS!!!
Steve
Go to the top of the page
 
DanielPineault
post Nov 26 2017, 09:50 PM
Post#9


UtterAccess VIP
Posts: 6,273
Joined: 30-June 11



thumbup.gif
Go to the top of the page
 
bakersburg9
post Nov 26 2017, 11:27 PM
Post#10



Posts: 5,306
Joined: 2-November 04
From: Downey, CA


oops - I spoke too soon - that only works for one cell, with an absolute reference - what if, instead of say, cell C4, I wanted the same thing for the CURRENT cell ? is that possible?

I tried:
CODE
If Not Intersect(Target, Range.Cells(current.Cells(1).Row, 1).Value) Then
I didn't think it would work, but I was grasping at straws - maybe I could do a count formula in a specific cell, and cause the change in the count by changing the value in a cell that's part of the range of cells that's being counted ?
Go to the top of the page
 
larai
post Nov 28 2017, 12:55 PM
Post#11



Posts: 1,113
Joined: 8-February 02
From: California, USA


You can, but it gets tricky because your event will fire (worksheet_Change) when your code changes the value so you'll need to enableevents = false before changing the value, and then enableevents = true after the value is changed.

I'm giving you 2 examples on how to do this. The first way is so that you can check what COLUMN the value is entered in, and then offset that 3 with the timestamp.

The other, I use the 2nd column as where the value is typed, and then replace that value with the timestamp.

CODE
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target.Offset(0, 3) = Format(TimeValue(Now), "hh:mm AM/PM")
    End If
    
    If Target.Column = 2 Then
        Application.EnableEvents = False
        Target = Format(TimeValue(Now), "hh:mm AM/PM")
        Application.EnableEvents = True
    End If
    
End Sub
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2018 - 01:38 PM