potain


Hi

I would greatly appreciate your help please.

I use an Excel spreadsheet to keep a record of conversations for telemarketing.

I have a formula : =TEXT(NOW(),"ddd dd mmm yy h:mm ") in B1 which reflects the current date & time.

I run a simple macro which fills the date value into the active cell : ActiveCell.Value = Range("B1").Value & " - " to record the date and time before entering relevant remarks after it.

I presently have to double click on the cell to be able to add my remarks and I was wandering if there is a way of adjusting the macro so that it enters "Edit Mode" and places the cursor at the end of the " - " so that I don't have to double click on the cell and can start entering text straightaway into the cell.

Thank you in advance for your assistance.

Jean



Re: Move cursor to the end of cell for data entry

Peter Mo.


Hi

I don't know how to do what you asked, but how about another approach How would it be if the Macro put the date/time at the front of anything you entered after you had entered it

Enter VBA and put the following code in the sheet where you will be entering data

Code Snippet

Const myColumn As Long = 1 ' or what ever column you are using

Dim blnChanging As Boolean

Private Sub Worksheet_Change(ByVal Target As Range

If blnChanging Then Exit Sub ' are we changing things

If Target.Column = myColumn Then
blnChanging = True ' say we are changing things
Target = Range("B1").Value & " - " & Target
blnChanging = False ' say we have finished
End If

End Sub

This code only adds the date/time when you change something in a particular column and leaves any other changes untouched. It also has the advantage that you never have to invoke the Macro explicitly. On the other hand it will prefix a date/time if you go back and change a cell again.

Regards

Peter Mo






Re: Move cursor to the end of cell for data entry

potain

Hello Peter

Thank you very much for your suggestion.

I right clicked on the sheet tab and chose View Code and added the snippet to the code window and it is showing under Worksheet Change but nothing happens when I add an entry to any of the cells in column 1 (Column B). Any idea why

Thanks again,

Jean







Re: Move cursor to the end of cell for data entry

Peter Mo.

Hi Jean

Try changing to ...

Const myColumn As Long = 2

Column A is 1, B is 2, etc.

If this still doesn't work then remove the If and End If statements and see if it work in any columns.

Regards

Peter Mo





Re: Move cursor to the end of cell for data entry

potain

Thank you Peter, It works great.

Jean