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: 4,952
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: 1
 
Go to the top of the page
 
DanielPineault
post Nov 24 2017, 06:54 AM
Post#2


UtterAccess VIP
Posts: 5,437
Joined: 30-June 11



Try
CODE
ActiveCell.Offset(0, 3) = Format(TimeValue(Now), "hh:mm AM/PM")

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
DanielPineault
post Nov 24 2017, 06:58 AM
Post#3


UtterAccess VIP
Posts: 5,437
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

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
bakersburg9
post Nov 25 2017, 04:57 PM
Post#4



Posts: 4,952
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: 4,952
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: 4,952
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: 1
 
Go to the top of the page
 
DanielPineault
post Nov 26 2017, 06:07 PM
Post#7


UtterAccess VIP
Posts: 5,437
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: 3
 

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
bakersburg9
post Nov 26 2017, 08:35 PM
Post#8



Posts: 4,952
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: 5,437
Joined: 30-June 11



thumbup.gif

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
bakersburg9
post Nov 26 2017, 11:27 PM
Post#10



Posts: 4,952
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,064
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    10th December 2017 - 11:12 PM