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!? :confused:
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

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!? :confused:
Thank you
Alex