Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Date Stamp & Worksheet Change macros
Currently I use: Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("B2")) Is Nothing Then SaveReferenceName End If On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B2:B2")) Is Nothing Then With Target .Offset(1, 6).Value = Format(Date, "mm/dd/yy") End With End If ws_exit: Application.EnableEvents = True End Sub And in a seperate module: Sub SaveReferenceName() Dim s As String s = ActiveSheet.Range("B2").Text ActiveWorkbook.SaveAs "C:\New Quotes\" & s & ".xls" End Sub Which saves a new file as filename: (value in B2) whenever the cell B is changed. Also, when cell B2 is changed, the date is stamped in H3. But want I want to add is: when cell C9 is changed (ideally when valu is "YES"), the date is stamped in E4. But the newbie I am, I just can' get it to work! Any solutions please!? Thank you Ale -- alex386 ----------------------------------------------------------------------- alex3867's Profile: http://www.excelforum.com/member.php...fo&userid=3722 View this thread: http://www.excelforum.com/showthread.php?threadid=57052 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Date Stamp & Worksheet Change macros
one way:
Public Sub Worksheet_Change(ByVal Target As Range) With Target If .Cells.Count = 1 Then Select Case .Address(False, False) Case "B2" SaveReferenceName On Error GoTo ws_exit Application.EnableEvents = False With Range("H3") .Value = Date .NumberFormat = "mm/dd/yy" End With Case "C9" If UCase(.Value) = "YES" Then On Error GoTo ws_exit Application.EnableEvents = False With Range("E4") .Value = Date .NumberFormat = "mm/dd/yy" End With End If Case Else End Select End If End With ws_exit: Application.EnableEvents = True End Sub In article , alex3867 wrote: Currently I use: Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("B2")) Is Nothing Then SaveReferenceName End If On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("B2:B2")) Is Nothing Then With Target .Offset(1, 6).Value = Format(Date, "mm/dd/yy") End With End If ws_exit: Application.EnableEvents = True End Sub And in a seperate module: Sub SaveReferenceName() Dim s As String s = ActiveSheet.Range("B2").Text ActiveWorkbook.SaveAs "C:\New Quotes\" & s & ".xls" End Sub Which saves a new file as filename: (value in B2) whenever the cell B2 is changed. Also, when cell B2 is changed, the date is stamped in H3. But want I want to add is: when cell C9 is changed (ideally when value is "YES"), the date is stamped in E4. But the newbie I am, I just can't get it to work! Any solutions please!? Thank you Alex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time/Date Stamp Multiple Rows | Excel Discussion (Misc queries) | |||
Date stamp when a value is entered in a cell on another worksheet | Excel Worksheet Functions | |||
MULTIPLE DATE/TIME stamp | Excel Worksheet Functions | |||
I need a date stamp that doesn't change when you enter text | Excel Worksheet Functions | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) |