VBA: automatically input non-updating dates?

Hello All,

I need to set up an if-then code so that if I put data into a cell between column E15:E115, the date I entered the data will appear in the corresponding cell number in column "C". I need to do the same for data in M15:M115 and column "K". The date cannot change once input. For example, if I type something in E15 on 30.10.2010, I need 30.10.2010 to appear in C15 and to not update the next time I open the file.

I found a code for this purpose, but it is not working properly. Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("E15:E115")) Is Nothing Then

With Target(1, -1)

.Value = Date

.EntireColumn.AutoFit

End With

End If

End Sub

This code results in one of two problems: 1) If the column between these two columns is empty (column “D”), then I can only type one character into E15, for example, before I am locked from typing anymore. In other words, it locks my keyboard for everything but typing in the formula bar, and this includes hitting 'enter' or 'tab' to leave the formula bar. It is very inconvenient. 2) If, however, the column between these two columns, “D”, has anything in it, the code does absolutely nothing.

I am hoping this is not a 2011 Excel issue for Mac because the only reason I bought it was to use the VBA ability for this. Hopefully someone has a correction to this code or a suggestion.

Please help.

Thank you

Hi Jedouard
I thought we had a few more months for the release of Excel 2011 for the Mac. I guess not by reading
http://www.macworld.com/article/1551...el2011faq.html

Welcome to the world of Excel VBA.

Your code needs to be behind the worksheet, not a module, as I assume it is.
Try putting your code in an event called Worksheet_SelectionChange instead of Worksheet_Change.

That should trigger the event after you move your cursor to another cell instead of just typing a singel character.

I believe the With Target(1,-1) puts the stuff entered one row down and one column back. If you are in E15 and want it to get into C15, try Target(0,-2).

I hope that helps. Now you can start reading a lot of the VBA hints and tricks that work on the PC and they should also work on the MAC.

More on http://www.mvps.org/dmcritchie/excel/event.htm

Hi,
Right before your End Sub put a line like:
Range("E14").select

That should keep the entire column from looking like it is selected.