I want to reduce time at the coal face.

[MACDOB]

Hi folks. I am trying to be a little bit more efficient and I am working on an Excel sheet for booking in transport and suppliers.. I have started the sheet and I want to be able to just click on a cell and the date appears in col A in col. B I want to be able to click on the cell and the actual time appears for the time in, a running clock which stops whne I click on the cell or perhaps I will have to have a button. I will be able to appy this in another col. for time out. Another trick I would like to use is the one which happens when you start to type an e-mail addres and after a few letters a number of contacts appear for you select from. How do they do that?
This is my first post so apologies if I am asking too much. I have never done VB or C++ threfore I will need instruction if it is a macro in how to get it into the worksheet. Thanks in anticipation and have a good day out there

 

[Jameo]

Hi MacDob - Welcome to the board.

Open up your spreadsheet and hit alt+F11 to open up VBA.

Then on the VBA project explorer on the left find the sheet you want to run this macro on - double click on it.

Then in the window that opens up paste the following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim IntRow As Long, IntCol As Long
    Dim CuTime As Date, NewTime As Date
    
    
        If Target.Column = 1 Then
        
            IntRow = Target.Row
            IntCol = Target.Column
        
        Cells(IntRow, IntCol).Value = Now()
        End If
    
    
        If Target.Column = 2 Then
            If Not IsEmpty(Cells(Target.Row, Target.Column - 1)) Then
                IntRow = Target.Row
                IntCol = Target.Column
                CuTime = CDate(Cells(IntRow, 1).Value)
                Cells(IntRow, IntCol).Value = Now()
                NewTime = Cells(IntRow, IntCol).Value
                Cells(IntRow, IntCol + 1).Value = Format(NewTime - CuTime, "hh:mm:ss")
            End If
        End If
  
End Sub

As the above is written at the moment, if you double click in any cell in column "A", todays date and time will be added to the cell.

Then if you double click in column "B" the time of the click will be added to that cell, and the difference added int he same row of column "C".

Without seeing your spreadsheet I am not sure where you wish the actual cells to lie. So get back to me if you have any issues

 

[MACDOB]

Thanks for the very quick response. I did the copy and paste following your instructions but I may have missed something else. If you want to see the sheet how do I send it to you. Macdob

 

[Jameo]

Just upload it somewhere and then paste me the download link

 

[MACDOB]

Hi Jameo. Thanks for that but I don't know where to upload it to. I don't have a web site. I can send it be email or you can email me and I will replyor. my e-mail is. I am 73 years of age and quite computer literate but this new to me. The columns are headed
a b c d e f
Date. Registration number, Time in Company/Supplier. Pass no. Time out

 

[VoG]

I've removed your e-mail address to protect you from spammers.

you could upload to a site like www.box.net and provide a link in a reply to this thread.

 

[MACDOB]

Hi Thanks for that it was just a tad niave. Is upload save as web page or should it be send to. I said that this is new to me. You can probably tell that I don't have a clue about uploading but I am learning

 

[VoG]

If you go to www.box.net and Sign Up (Personal, for free) you should find instructions on how to upload a file.

 

[MACDOB]

Thank you.

 

[Jameo]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    
    Dim IntRow As Long, IntCol As Long
    Dim CuTime As Date, NewTime As Date
 
        If Target.Column = 1 Then
        
            IntRow = Target.Row
            IntCol = Target.Column
        
        Cells(IntRow, IntCol).Value = Format(Now(), "dd/mm/yyyy")
        End If
    
    
        If Target.Column = 3 Then
        
            IntRow = Target.Row
            IntCol = Target.Column
        
        Cells(IntRow, IntCol).Value = Now()
        End If
    
    
        If Target.Column = 5 Then
            If Not IsEmpty(Cells(Target.Row, Target.Column - 2)) Then
                IntRow = Target.Row
                IntCol = Target.Column
                CuTime = CDate(Cells(IntRow, 3).Value)
                Cells(IntRow, IntCol).Value = Now()
                NewTime = Cells(IntRow, IntCol).Value
                Cells(IntRow, IntCol + 1).Value = Format(NewTime - CuTime, "hh:mm:ss")
            End If
        End If
  
End Sub

Here is my understanding of your spreadsheet.
Column: A - Date
Column: B - Registration number
Column: C - Time in
Column: D - Company/Supplier
Column: E - Pass no.
Column: F - Time out
With the above procedure, if you double click in the Date Column, todays date will be added in the format dd/mm/yyyy. If you double click in the Time In COlumn, the date and time will be added. If you double click in the Time out column, the date and time will again be added, sutracted from the data in the Time In column, and the difference placed in column G.
I hope this is what you need. Again, to add this procedure, open up VBA by pressing Alt+F11, then in the Project Explorer window on the left (if this is not visible, View -> Project Explorer) you will see a list of all of the sheets in your workbook. Double click on the sheet you wish this proecedure to be active on. A blank window will open up. Copy and paste the code, and then close VBA. Your procedure will be active