Generate date in cell on changing value in drop down list

[Hannes]

Hi there,

I would need some support please on how to program in VBA the following task:
In column M of any row the user chooses in a drop down list (values 1 to 6)
one value, at which simultaneously the current date (of selection) is copied
into column N to S of the same row.
E.g.: User selects value 1 of drop down list in M3 so the date of today e.g.
08/20/08 is copied into N3. At the 08/25/08 the user selects value 2 in M3 so
the date is copied into O3. At the 08/29/08 the user selects value 3 in M3 so
the date is copied into P3. And so on. Maximum of the date history would be
S3 as there are 6 values to select. This should work for all rows whereas the
columns stay the same.

Many thanks for any support.
Hannes

 

[Joel]

You need a worksheet change function.

Private Sub worksheet_change(ByVal target As Range)

If target.Column = Columns("M").Column Then
target.Offset(0, target.Value) = Date
target.Offset(0, target.Value).NumberFormat = "MM/DD/YY"
End If
End Sub

 

[Hannes]

Could you please explain a bit more detailed how I have to adapt your code
because I am beginner in VBA programming. Thanks!

 

[Don Guillett]

Simply right click the sheet tab desired>copy/paste the macro.

 

[Hannes]

Sorry but it doesn't work. I copied the macro in the worksheet but nothing
happens.

Perhaps there is a misunderstanding of the functionality. Here an example
for clarification:

Selected value in Column Date Generate date in
cell

1 M3 08/20/08 N3
3 M3 08/23/08 P3
5 M7 08/22/08 R7
6 M7 08/23/08 S7
....
....
I hope this helps. The macro should work in real-time in the background and
generate dates depending on the value in the according column of the row.

Many thanks for not giving up on me ;-)

 

[Hannes]

I get a run-time error '13': Types incompatible.

Thanks for any support!

 

[Don Guillett]

If Joel doesn't come back send your workbook to my address below along with
snippets from these msgs.

 

[Don Guillett]

OP sent workbook and this is my response

In your post you NEVER mentioned that your number was NOT the only thing in
the cell. We would get the impression that you had 1,2,3,4,5,6 instead of
1-xxxd. This will fix it. There was also a problem with the offset going
down a row (fixed) and you probably do NOT need to format the date so I
commented out the last line. Un comment if necessary.

Private Sub worksheet_change(ByVal target As Range)
If target.Column = Columns("M").Column Then
target.Offset(0, Left(target, 1)) = Date
'target.Offset(0, left(target,1)).NumberFormat = "MM/DD/YY"
End If
End Sub