Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO or FORMULA
I am trying to make a spread sheet which has a cell with a drop down function
from which a name can be selected - that is no problem. What I would like to do is when a name is selected from the drop down, I would like to have the cell below it self-populate with the time and date. I have talked to many users much more experienced than I, and have not been able to figure out a method to do this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO or FORMULA
Right click on sheet tab, view code. Paste this in:
Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A1") Then 'Range where drop down is Range("A2").Value = Now 'Range where you want time stamp End If End Sub Note that time stamp is changed whenever drop-down is changed. You may need to adjust formatting of time stamp cell to match the exact look you are wanting. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "CHENG28" wrote: I am trying to make a spread sheet which has a cell with a drop down function from which a name can be selected - that is no problem. What I would like to do is when a name is selected from the drop down, I would like to have the cell below it self-populate with the time and date. I have talked to many users much more experienced than I, and have not been able to figure out a method to do this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO or FORMULA
Let's use Cells B9 and B10:
Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set b9 = Range("B9") If Intersect(t, b9) Is Nothing Then Exit Sub Application.EnableEvents = False t.Offset(1, 0).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200840 "CHENG28" wrote: I am trying to make a spread sheet which has a cell with a drop down function from which a name can be selected - that is no problem. What I would like to do is when a name is selected from the drop down, I would like to have the cell below it self-populate with the time and date. I have talked to many users much more experienced than I, and have not been able to figure out a method to do this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MACRO or FORMULA
What would be used if data is entered in F1 then static date is posted in B2?
"Gary''s Student" wrote: Let's use Cells B9 and B10: Put the following event macro in the worksheet code area: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set b9 = Range("B9") If Intersect(t, b9) Is Nothing Then Exit Sub Application.EnableEvents = False t.Offset(1, 0).Value = Now Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200840 "CHENG28" wrote: I am trying to make a spread sheet which has a cell with a drop down function from which a name can be selected - that is no problem. What I would like to do is when a name is selected from the drop down, I would like to have the cell below it self-populate with the time and date. I have talked to many users much more experienced than I, and have not been able to figure out a method to do this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro formula help | Excel Worksheet Functions | |||
Formula or Macro | Excel Discussion (Misc queries) | |||
Formula or Macro | Excel Discussion (Misc queries) | |||
Formula Macro | Excel Discussion (Misc queries) | |||
Formula / Macro Help | Excel Discussion (Misc queries) |