MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dave Hawley


Posted by Robert on April 25, 2001 12:33 PM

Thanks for your help in regards to message message 15606. One more question. The first formula didnt work, but the VB code did. Can you modify that code to make it work for the each cell in the whole column?, so that if something is input in B3, A3 with display the time and date of which B3 was modified and so on. I'm obviously not familiar with VB (maybe I should start). I would again appreciate any help,
Thanks,
Robert


Posted by Dave Hawley on April 25, 2001 2:12 PM


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''
'Places the current date and time in cell A1 _
If B1 <> ""
'''''''''''''''''''''''''''''''''''''

If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
If Target <> "" Then
Range("A1") = Now
Range("A1").EntireColumn.AutoFit
End If
End If
End Sub

Hi Robert

Try this:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''
'Places the current date and time in Column B
'''''''''''''''''''''''''''''''''''''

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target <> "" Then
Target.Offset(0, -1) = Now
Columns(1).AutoFit
End If
End If
End Sub

Dave

OzGrid Business Applications

Posted by Robert on April 25, 2001 2:41 PM


Dave,
Thanks SO much! heres the code I ended up using:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

''''''''''''''''''''''''''''''''''''
'Places the current date and time in Column B
'''''''''''''''''''''''''''''''''''''

If Target.Cells.Count > 3 Then Exit Sub
If Target.Column = 4 Then
If Target <> "" Then
Target.Offset(0, -1) = Now
Else
Target.Offset(0, -1) = ""
End If
End If
End Sub

I added the Else all by myself :) You've been of great help to me, and it seems everyone on this board. Just curious... do you only know VB, or do you know other languages also. And what do you suggest doing to become as efficient as you?

Thanks again,

Robert

Posted by Dave Hawley on April 25, 2001 5:07 PM


Hi Robert

>I added the Else all by myself :)

That's how it starts you know :o)

I only Know VBA for Excel. I decided to go down the path of knowing this language as best I could and the focus on it. My company OzGrid Business Applications is slowly but surely moving more and more into Excel.


>And what do you suggest doing to become as efficient as you

At the risk of sounding uppity, I would be happy to give you VBA for Excel tutoring if you wanted.

DaveOzGrid Business Applications

Posted by Robert on April 25, 2001 5:52 PM


Dave,

I'm sure you would never sound uppity :) I don't have much time on my hands now as I'm attending school full time (C programming is one of the classes) and also working. But out of curiosity, how much do you charge. I'm constantly making little excel documents for my office, and I feel, eventhough my creations are simple, this ability has increased my value to the company. I may be interested in sometime in the future taking up your offer :)

Thanks,
Robert

Posted by Dave Hawley on April 25, 2001 6:58 PM


Rob, not a problem, studying one langauge is more than enough!

As my lessons are Tailor-made to the individual it really depends on how much they wish to learn. But to give you and idea, the average is about 250-300 Australian dollars. That would get you ten email lessons and five working Workbook examples. This would usually also mean another ten smaller emails responding to questions. The pace of the course it also set by the student.

Dave

OzGrid Business Applications