Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate date in cell on changing value in drop down list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate date in cell on changing value in drop down list
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate date in cell on changing value in drop down list
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate date in cell on changing value in drop down list
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generate date in cell on changing value in drop down list
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do generate a drop down list with no blanks | Excel Discussion (Misc queries) | |||
drop-down list changing according to choice made | Excel Programming | |||
drop-down list changing according to choice made | Excel Worksheet Functions | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions | |||
changing the font size on the drop down of a list (autofilter). | Excel Programming |