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

  • 18 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.

  • 18 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.
    ''


  • 18 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





  • 18 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 worksheet
    Change(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

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Some people, when confronted with a problem, think "I know, I’ll use regular expressions." Now they have two problems.” - Jamie Zawinski