New Related Question - WAS: Wierd Formula Request

  • Thread starter Thread starter magmike
  • Start date Start date
M

magmike

In regards to the former discussion (http://tinyurl.com/762vq62) which
worked great by the way (Thanks to all who helped!), how could I
change the Case statement to populate a cell based any change in
value, not just a specific text entry?

A current example based on specific text:

Case "SENT", Range("Q" & n) = ""
Range("Q" & n) = Format(Date, "mm-dd-yyyy")

Thanks in advance for your help!

magmike
 
In regards to the former discussion (http://tinyurl.com/762vq62) which
worked great by the way (Thanks to all who helped!), how could I
change the Case statement to populate a cell based any change in
value, not just a specific text entry?

A current example based on specific text:

      Case "SENT", Range("Q" & n) = ""
       Range("Q" & n) = Format(Date, "mm-dd-yyyy")

Thanks in advance for your help!

magmike
Click to expand...

You need to clarify what you want.with more explanation.
Send your file with a complete explanation and before/after examples
to dguillett1 @gmail.com
 
You need to clarify what you want.with more explanation.
Send your file with a complete explanation and before/after examples
to dguillett1        @gmail.com
Click to expand...

Don,

The complete code and explanation was at the link given related to
that post chain. However, I will post the result code of that post
here:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim n As Long, s As String
On Error GoTo enditall
If Target.Column = 5 Then '1 is column A
Application.EnableEvents = False
n = Target.Row: s = UCase$(Target)

With Range("N" & n)
If IsEmpty(.Value) Then
.Value = Format(Date, "mm-dd-yyyy")
End If
End With

Select Case s
Case "IN", Range("O" & n) = ""
Range("O" & n) = Format(Date, "mm-dd-yyyy")


Case "QUOTE", Range("P" & n) = ""
Range("P" & n) = Format(Date, "mm-dd-yyyy")

Case "EMAIL", Range("P" & n) = ""
Range("P" & n) = Format(Date, "mm-dd-yyyy")

Case "SENT", Range("Q" & n) = ""
Range("Q" & n) = Format(Date, "mm-dd-yyyy")


Case "REQ", Range("R" & n) = ""
Range("R" & n) = Format(Date, "mm-dd-yyyy")


Case "DONE", Range("S" & n) = ""
Range("S" & n) = Format(Date, "mm-dd-yyyy")
End Select


enditall:
Application.EnableEvents = True
End If
End Sub

One additional feature I am seaking here, is that when the cell in
question $E(?) (i.e. E5 or E435 - for this example we will use E5) has
any change whatsoever, that T5 would then populate with today's date
marking the last date a change was made to cell E5.

In the above code, specific columns populate the date based on a
specific word. For example, when the word "DONE" is typed into E5,
then S5 populates with the date on which that value was entered into
E5 and then always remains the same. The first time any value is
entered into E5, N5 is populated with that day's date, showing the
first date a value was entered into E5. Now I am designating T5 as a
field that will show the last date a change was made to the value of
E5.

Hope that helps,

magmike
 
Don,

The complete code and explanation was at the link given related to
that post chain. However, I will post the result code of that post
here:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim n As Long, s As String
  On Error GoTo enditall
  If Target.Column = 5 Then  '1 is column A
    Application.EnableEvents = False
    n = Target.Row: s = UCase$(Target)

    With Range("N" & n)
      If IsEmpty(.Value) Then
         .Value = Format(Date, "mm-dd-yyyy")
      End If
    End With

    Select Case s
      Case "IN", Range("O" & n) = ""
       Range("O" & n) = Format(Date, "mm-dd-yyyy")

      Case "QUOTE", Range("P" & n) = ""
       Range("P" & n) = Format(Date, "mm-dd-yyyy")

      Case "EMAIL", Range("P" & n) = ""
       Range("P" & n) = Format(Date, "mm-dd-yyyy")

      Case "SENT", Range("Q" & n) = ""
       Range("Q" & n) = Format(Date, "mm-dd-yyyy")

      Case "REQ", Range("R" & n) = ""
       Range("R" & n) = Format(Date, "mm-dd-yyyy")

      Case "DONE", Range("S" & n) = ""
       Range("S" & n) = Format(Date, "mm-dd-yyyy")
    End Select

enditall:
    Application.EnableEvents = True
  End If
End Sub

One additional feature I am seaking here, is that when the cell in
question $E(?) (i.e. E5 or E435 - for this example we will use E5) has
any change whatsoever, that T5 would then populate with today's date
marking the last date a change was made to cell E5.

In the above code, specific columns populate the date based on a
specific word. For example, when the word "DONE" is typed into E5,
then S5 populates with the date on which that value was entered into
E5 and then always remains the same. The first time any value is
entered into E5, N5 is populated with that day's date, showing the
first date a value was entered into E5. Now I am designating T5 as a
field that will show the last date a change was made to the value of
E5.

Hope that helps,

magmike
Click to expand...

I would write it like this and put in the SHEET module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Or Target.Column <> 5 Then Exit Sub
Cells(Target.Row, "M") = Format(Date, "mm-dd-yyyy")
On Error GoTo mymsg
Select Case UCase(Target)
Case "IN": x = "O"
Case "QUOTE", "EMAIL": x = "P"
Case "SENT": x = "Q"
Case "REQ": x = "R"
Case "DONE": x = "S"
End Select
Cells(Target.Row, x) = Format(Date, "mm-dd-yyyy")
Cells(Target.Row, "T") = _
Format(Application.Max(Cells(Target.Row, "M").Resize(, 7)), "mm-dd-
yyyy")
Exit Sub
mymsg: MsgBox ("Not there, How about using data validation")
End Sub
 
Don,

The complete code and explanation was at the link given related to
that post chain. However, I will post the result code of that post
here:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim n As Long, s As String
  On Error GoTo enditall
  If Target.Column = 5 Then  '1 is column A
    Application.EnableEvents = False
    n = Target.Row: s = UCase$(Target)

    With Range("N" & n)
      If IsEmpty(.Value) Then
         .Value = Format(Date, "mm-dd-yyyy")
      End If
    End With

    Select Case s
      Case "IN", Range("O" & n) = ""
       Range("O" & n) = Format(Date, "mm-dd-yyyy")

      Case "QUOTE", Range("P" & n) = ""
       Range("P" & n) = Format(Date, "mm-dd-yyyy")

      Case "EMAIL", Range("P" & n) = ""
       Range("P" & n) = Format(Date, "mm-dd-yyyy")

      Case "SENT", Range("Q" & n) = ""
       Range("Q" & n) = Format(Date, "mm-dd-yyyy")

      Case "REQ", Range("R" & n) = ""
       Range("R" & n) = Format(Date, "mm-dd-yyyy")

      Case "DONE", Range("S" & n) = ""
       Range("S" & n) = Format(Date, "mm-dd-yyyy")
    End Select

enditall:
    Application.EnableEvents = True
  End If
End Sub

One additional feature I am seaking here, is that when the cell in
question $E(?) (i.e. E5 or E435 - for this example we will use E5) has
any change whatsoever, that T5 would then populate with today's date
marking the last date a change was made to cell E5.

In the above code, specific columns populate the date based on a
specific word. For example, when the word "DONE" is typed into E5,
then S5 populates with the date on which that value was entered into
E5 and then always remains the same. The first time any value is
entered into E5, N5 is populated with that day's date, showing the
first date a value was entered into E5. Now I am designating T5 as a
field that will show the last date a change was made to the value of
E5.

Hope that helps,

magmike
Click to expand...

Guess my ans didn't go thru so repeat\

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Or Target.Column <> 5 Then Exit Sub
Cells(Target.Row, "M") = Format(Date, "mm-dd-yyyy")
On Error GoTo mymsg
Select Case UCase(Target)
Case "IN": x = "O"
Case "QUOTE", "EMAIL": x = "P"
Case "SENT": x = "Q"
Case "REQ": x = "R"
Case "DONE": x = "S"
End Select
Cells(Target.Row, x) = Format(Date, "mm-dd-yyyy")
Cells(Target.Row, "T") = _
Format(Application.Max(Cells(Target.Row, "M").Resize(, 7)), "mm-dd-
yyyy")
Exit Sub
mymsg: MsgBox ("Not there, How about using data validation")
End Sub
 
I  would write it like this and put in the SHEET module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Or Target.Column <> 5 Then Exit Sub
    Cells(Target.Row, "M") = Format(Date, "mm-dd-yyyy")
    On Error GoTo mymsg
     Select Case UCase(Target)
        Case "IN": x = "O"
        Case "QUOTE", "EMAIL": x = "P"
        Case "SENT": x = "Q"
        Case "REQ": x = "R"
        Case "DONE": x = "S"
      End Select
    Cells(Target.Row, x) = Format(Date, "mm-dd-yyyy")
    Cells(Target.Row, "T") = _
   Format(Application.Max(Cells(Target.Row, "M").Resize(, 7)), "mm-dd-
yyyy")
    Exit Sub
mymsg: MsgBox ("Not there, How about using data validation")
End Sub- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks. Curious though, what does this line do different from the
rest:

Format(Application.Max(Cells(Target.Row, "M").Resize(, 7)), "mm-dd-
yyyy")

Also - How do I turn off the validation rule? I get your error
whenever I do not use one of the code words in the script, which is
not necessary. I use other words in column 5 that I do not require a
date stamp for. The purpose of the new column is a catch all for when
the last action happened. I don't want to have a column for every
option.
 
hi,

i think you look for "Case Else"

Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else
[elsestatements]]
End Select



--
isabelle


Le 2011-12-11 22:40, magmike a écrit :
 
Back
Top