Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

How to insert Time in a range of excel sheet which should not update automatically using com object?


  • Please log in to reply
8 replies to this topic
manoj aggarwal
  • Members
  • 72 posts
  • Last active: Nov 13 2015 10:03 AM
  • Joined: 12 Oct 2014

Friends I want to insert time in a range which should not get updated automatically. For example if there is any value in cell no k5 then the current time should automatically be inserted in cell no N5. Similarly if there is any value in k6 then the current time should be automatically be inserted in N6, so on.. Moreover it should not be updated automatically. I tried to use this formula-

 

 

=IF(K5<0,"",IF(K5=0,"",NOW()))

 

This formula is working but it is auto updating the time with the system's clock. I want it should not update the time. Is there any way to do this by using com object in autohotkey. You can see the snapshot also-

3JIwORg.png

 

 PLEASE HELP ME...THANKS A LOT...

 

 



Jackie Sztuk _Blackholyman
  • Spam Officer
  • 3757 posts
  • Last active: Apr 03 2016 08:47 PM
  • Joined: 28 Feb 2012
Example:
 
oExcel := ComObjActive("Excel.Application")
Sheet := oExcel.ActiveSheet
ComObjConnect(Sheet, Worksheet_Events)
return

class Worksheet_Events 
{
	Change(cell) 
	{
		if (cell.address ~= "\$K") ; if cell is in col with a k
		{
			if (cell.value != "") ; if there is a value in the cell after change
			{
				if (cell.offset(0,3).value = "") ; if the time cell is blank
				{
					FormatTime, TimeString, %A_Now%, hh:mm:ss
					cell.offset(0,3).value := TimeString
				}
			}
		}
	}
}

Helping%20you%20learn%20autohotkey.jpg?d

[AHK] Version. 1.1+ [CLOUD] DropBox ; Copy [WEBSITE] Blog ; About

Alpha Bravo
  • Members
  • 1687 posts
  • Last active: Nov 07 2015 03:06 PM
  • Joined: 01 Sep 2011

Open VBA (Alt+F11).
Double click on your worksheet name in the Project explorer window.

Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Column = 11 And Target.Row > 4 Then
        If Cells(Target.Row, 14).Value = "" Then
            Cells(Target.Row, 14).Value = Time()
        End If
     End If
End Sub


manoj aggarwal
  • Members
  • 72 posts
  • Last active: Nov 13 2015 10:03 AM
  • Joined: 12 Oct 2014

 

Open VBA (Alt+F11).
Double click on your worksheet name in the Project explorer window.

Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Column = 11 And Target.Row > 4 Then
        If Cells(Target.Row, 14).Value = "" Then
            Cells(Target.Row, 14).Value = Time()
        End If
     End If
End Sub

DEAR @ALPHA BRAVO...thanks for your answer...the codes are working but one thing i forget to tell you that there is also a formula in entire range column k. so If I type any value in any cell left to column K for example- if there is any data in A5 or B5 it will show the calculated value in k5. similarly if there any value in any cell left to k6 then its calculated value will be shown in k6 and so on. your formula is working if i directly type any value in column k.  but when i type any value left to column k for example in a5 or b5 or c5 then its calculated values are shown cell k5 and then the codes do not work.  please tell me how to deal with this problem...



Alpha Bravo
  • Members
  • 1687 posts
  • Last active: Nov 07 2015 03:06 PM
  • Joined: 01 Sep 2011

modify it to 

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Column <= 11 And Target.Row > 4 Then
        If Cells(Target.Row, 14).Value = "" Then
            Cells(Target.Row, 14).Value = Time()
        End If
     End If
End Sub


manoj aggarwal
  • Members
  • 72 posts
  • Last active: Nov 13 2015 10:03 AM
  • Joined: 12 Oct 2014
&nbsp;


modify it to&nbsp;

Private Sub Worksheet_Change(ByVal Target As Range)     If Target.Column &lt;= 11 And Target.Row &gt; 4 Then        If Cells(Target.Row, 14).Value = "" Then            Cells(Target.Row, 14).Value = Time()        End If     End IfEnd Sub
&nbsp;

Thanks @AlphaBravo..now the codes are working fine..one think i wish to add in the codes more..is that..when i delete any value in column k it should also delete the time inserted in column N. currently it is not deleting the time in column N when i delete any value in column K. I have to manually delete the time in column N. Please help... thanks..

bruno
  • Members
  • 635 posts
  • Last active: Nov 04 2015 02:26 PM
  • Joined: 07 Mar 2011

i am not at Alpha Bravo's level. the closest i got was to do a search for something close: ;)

 

http://www.mrexcel.c...ll-deleted.html



Jackie Sztuk _Blackholyman
  • Spam Officer
  • 3757 posts
  • Last active: Apr 03 2016 08:47 PM
  • Joined: 28 Feb 2012
try it like this

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Column <= 11 And Target.Row > 4 Then
        If Cells(Target.Row, 14).Value = "" Then
            Cells(Target.Row, 14).Value = Time()
        ElseIf Target.Value = "" Then
            Cells(Target.Row, 14).Value = ""
        End If
     End If
End Sub

Helping%20you%20learn%20autohotkey.jpg?d

[AHK] Version. 1.1+ [CLOUD] DropBox ; Copy [WEBSITE] Blog ; About

bruno
  • Members
  • 635 posts
  • Last active: Nov 04 2015 02:26 PM
  • Joined: 07 Mar 2011

thanks, BHM, it works and what can i say? ;)