i have the code but don't know how to use it

• • Reply

  18 years ago

  by allrounder

  Waqar

  , United States

  Joined 19 years ago

    SUppose i have a sheet in which the list of expenses is given as follows:-
  

  
                  A                                   B                 C            
          DETAILS OF EXPENSES      AMOUNT.        TIME
  

  
  1        For purchasing Soap          100              8:40 A.M.
  2        For purchasing DVD           90                9:30 A.M.
  3        FOr purchasing CD             30                12:01 P.M.
  

  
   Now i want that when ever a entry is made in the DEATILS OF EXPENSES cell TIME cell automatically filled showing time at which that entry is made.
   And once the entry is made it cannot be Over-written.
  

  
  Now here is the code for this:-
  

  
  Option Explicit
  

  
  Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Column = 2 Then
  If Target.Offset(0, 3).Text = "" Then
  Target.Offset(0, 3).Value = Format$(Now,"h:nn AMPM")
  'Target.Offset(0, 3).Value = Time
  End If
  End If
  End Sub
  

  
  but i don't know how to apply on my sheet, please guide me how to use this code so that the time appears in the C1 cell and so when ever a entry is made in A1 and so on.
  Please Steps wise.

  Report abuse
• • Reply

  18 years ago

  by Thorny

  K. R. Thorson

  Florida, USA, United States

  Joined 19 years ago

  allrounder,
  

  
  One approach is as follows:
  Format the entire sheet1 with the "lock" under protection UNCHECKED.
  Format the sheet1 column that the time will appear in with the "lock" under protection CHECKED.Apply protection to sheet1, with a password (i just used me).
  

  
  insert the following code via VBA editor in sheet1's code page.
  

  
  

  Code:

  Option Explicit
  

  
  Private Sub Worksheet_Change(ByVal Target As Range)
     
     If Target.Column = 2 Then
         If Target.Offset(0, 3).Text = "" Then
             Sheet1.Protect Password:="me", contents:=False
             Target.Offset(0, 3).Value = Format$(Now, "h:nn AMPM")
             Sheet1.Protect Password:="me", contents:=True
         End If
     End If
  

  
  End Sub
  

  
  

  
  That should do it.
  

  
  It protects only the time column from being entered, hence cannot be changed.
  ''
  

  
  

  Report abuse
• • Reply

  18 years ago

  by allrounder

  Waqar

  , United States

  Joined 19 years ago

  Thanks a lot for so much interest in my question and solving my problem please guide me about this
  

  
  understanding the code to apply it as i want on any cell,
  

  
  I just want to learn if on the same sheet i want to use this time entry automatically for different sections e.g. like one is the Expenses section that when ever a entry is made in the expense column time automatically appear , similary automatic time appearance is made in sales section on the same sheet.
  That's why i want to know how this code is working, so that i can use it for my requirements.
  

  
  1) what does this mean :-
  Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  

  
  2)If Target.Column = 1 Then
  If Target.Offset(0, 2).Text = "" Then
  

  
  

  
  3)Target.Offset(0, 2).Value = Format$(Now,"h:nn AMPM")
  'Target.Offset(0, 2).Value = Time
  

  
  

  
  

  
  

  
  

  Report abuse
• • Reply

  18 years ago

  by Thorny

  K. R. Thorson

  Florida, USA, United States

  Joined 19 years ago

  allrounder,
  

  
  Worksheetchange is a worksheet event available through the VBA editor.  When the left hand selection window shows worksheet, then all the events available are shown in a dropdown list from the righthand window.  Selection an event such as 'Change'  will drop
  

  Code:

  
  public sub worksheetChange(ByVal Target As Excel.Range)
  

  
  end sub
  

  
  

  
  into the VBA editor.  The 'Target' is an Excel range variable, which in this case is the cell (any cell is also a range consisting of one cell).  It's an object with its attendant properties.  This subroutine will be triggered whenever something is entered into a cell on a worksheet containing the subroutine.  It triggers when you press enter or exit the cell some other way.  It will trigger regardless of whether you left data in the cell or erased it before exiting, but if nothing was done in the cell it's not triggered.
  

  
  Every column (A,B,C,D, ...) is associated with a number, hence column A is column 1, column B is column 2 and so on.  One of the properts of the target (the cell in this case) is it's column number.  Hence whenever the Change procudure is triggered, the column number (and row also using target.row) can be queried to determine the location of the cell.
  

  
  The Target.Offset(0,2).text is saying, look in a cell that is offset from the cell in question by in 0 rows and 2 columns (not column 2 but target.column + 2 and set it's .text property to "" (Nothing?).  You could get at any cell this way, if you know it's position relative to your target cell.
  

  
  Target.Offset(0,2).value is the value property of target, whereas .text = somevalue, would set the format of the data in the cell to text, .Value will except other formats.  In this case you are using VBA format function to set time in the cell in the format you want.  ie. 'Now' is a function returning (I think, date and time of the instant you executed the line of code.  the "h:nn APMM" layout the format of the time you enter into the cell.  The cells format is now set to Date/Time
  

  
  This is rather long winded.  Hope it helps
  

  Report abuse