New Related Question - WAS: Wierd Formula Request

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
 
D

Don Guillett

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
 
M

magmike

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
 
D

Don Guillett

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
 
D

Don Guillett

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
 
M

magmike

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.
 
I

isabelle

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 :
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine Two Different Worksheet_Change codes 9
Wierd Formula Request 2
VBA Worksheet Event Change or Selection Question, please assist me 2
Macro to email via MAC (Code for Outlook working) 1
email from Excel Question for Ron de Bruin (or other really smart people) 0
Text Field Validation Rule for Date 1
Adding date and time to cell programatically? 3
VBA: Copy a date and paste as text 3

Top