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

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" wrote:

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

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

"Joel" wrote:

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" wrote:

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

Simply right click the sheet tab desiredcopy/paste the macro.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

"Joel" wrote:

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" wrote:

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

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 ;-)

"Don Guillett" wrote:

Simply right click the sheet tab desiredcopy/paste the macro.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

"Joel" wrote:

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" wrote:

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

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

Thanks for any support!


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 ;-)

"Don Guillett" wrote:

Simply right click the sheet tab desiredcopy/paste the macro.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

"Joel" wrote:

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" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hannes" wrote in message
...
I get a run-time error '13': Types incompatible.

Thanks for any support!


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 ;-)

"Don Guillett" wrote:

Simply right click the sheet tab desiredcopy/paste the macro.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

"Joel" wrote:

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" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
If Joel doesn't come back send your workbook to my address below along
with snippets from these msgs.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hannes" wrote in message
...
I get a run-time error '13': Types incompatible.

Thanks for any support!


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 ;-)

"Don Guillett" wrote:

Simply right click the sheet tab desiredcopy/paste the macro.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

"Joel" wrote:

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" wrote:

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