Weird formula request


M

magmike

I would like to have a column that records the date that another
column ("Status") was populated with a value.

The column titled "Status," when populated with a value, marks the
row
a certain color depending on the text entered in the field. For
example, if I enter "cb" (meaning 'call back') the row turns a light
yellow. There are about 10 different possibilities that could be
entered and colors that correspond with the entered text.


Regardless of the text entered into the "Status" column, I would like
another column's field in the same row to be automatically populated
with that day's date when the "Status" column is first populated
(regardless of the entered text) AND I do not want that date to ever
change, even if the text of the "Status" column is changed or
deleted.


Is this possible?


Thanks in advance for your help!


magmike
 
Ad

Advertisements

G

Gord

You cannot do that with a formula Mike.

You must use VBA.............specifically event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then '1 is column A
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Format(Date, "mm-dd-yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View
Code"

Copy/paste the code into that sheet module.

Edit the columns A and B to adjust for your needs.


Gord Dibben Microsoft Excel MVP
 
M

magmike

You cannot do that with a formula Mike.

You must use VBA.............specifically event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo enditall
    Application.EnableEvents = False
    If Target.Cells.Column = 1 Then  '1 is column A
        n = Target.Row
        If Excel.Range("A" & n).Value <> "" _
              And Excel.Range("B" & n).Value = "" Then
        Excel.Range("B" & n).Value = Format(Date, "mm-dd-yyyy")
        End If
    End If
enditall:
    Application.EnableEvents = True
End Sub

This is sheet event code.  Right-click on the sheet tab and "View
Code"

Copy/paste the code into that sheet module.

Edit the columns A and B to adjust for your needs.

Gord Dibben    Microsoft Excel MVP








- Show quoted text -

Thanks. That works great!
 
M

magmike

You cannot do that with a formula Mike.

You must use VBA.............specifically event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo enditall
    Application.EnableEvents = False
    If Target.Cells.Column = 1 Then  '1 is column A
        n = Target.Row
        If Excel.Range("A" & n).Value <> "" _
              And Excel.Range("B" & n).Value = "" Then
        Excel.Range("B" & n).Value = Format(Date, "mm-dd-yyyy")
        End If
    End If
enditall:
    Application.EnableEvents = True
End Sub

This is sheet event code.  Right-click on the sheet tab and "View
Code"

Copy/paste the code into that sheet module.

Edit the columns A and B to adjust for your needs.

Gord Dibben    Microsoft Excel MVP








- Show quoted text -

PS: Can I have multiple versions of this code on the same sheet? I
would also like to have another date column to record when a specific
word is typed into the Status column. For example, I modified it for a
second date column to record the date that the word "quote" was
entered into column 5. However, either I modified it wrong, or this
code only works once? Here is how it appears in my code sheet (with
both codes present):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 5 Then '1 is column A
n = Target.Row
If Excel.Range("E" & n).Value <> "" _
And Excel.Range("M" & n).Value = "" Then
Excel.Range("M" & n).Value = Format(Date, "mm-dd-yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_ChangeQTE(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 5 Then '1 is column A
n = Target.Row
If Excel.Range("E" & n).Value = "quote" _
And Excel.Range("N" & n).Value = "" Then
Excel.Range("N" & n).Value = Format(Date, "mm-dd-yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub
 
G

GS

magmike has brought this to us :
PS: Can I have multiple versions of this code on the same sheet? I
would also like to have another date column to record when a specific
word is typed into the Status column. For example, I modified it for a
second date column to record the date that the word "quote" was
entered into column 5. However, either I modified it wrong, or this
code only works once? Here is how it appears in my code sheet (with
both codes present):

Yes! Just change the structure of the inner If...Then:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 5 Then '1 is column A
n = Target.Row
If Excel.Range("E" & n).Value <> "" _
And Excel.Range("M" & n).Value = "" Then
Excel.Range("M" & n).Value = Format(Date, "mm-dd-yyyy")

ElseIf Excel.Range("E" & n).Value = "quote" _
And Excel.Range("N" & n).Value = "" Then
Excel.Range("N" & n).Value = Format(Date, "mm-dd-yyyy")
 
M

magmike

magmike has brought this to us :







Yes! Just change the structure of the inner If...Then:


      ElseIf Excel.Range("E" & n).Value = "quote" _
             And Excel.Range("N" & n).Value = "" Then
               Excel.Range("N" & n).Value = Format(Date, "mm-dd-yyyy")


--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Fantastic - this is great! One last thing...

By doing this, I notice that if my first entry into the "E" column is
"quote", then it puts the date into column M, but NOT column N at the
same time. I also created a third column (Column O) for the "sent"
code (entered in column E) and the same thing. If I were to start by
inserting "cb", and then rewriting column E with "quote" and then
rewriting column E with "Sent" - it all works perfect - however, I may
end up speaking with a prospect and sending the quote right then and
there while I am on the phone. in that situation i would START by
typing "Sent" into the E Column and would want both columns N and O to
populate with the date.

How do I do that?

Thanks,
 
Ad

Advertisements

M

magmike

Fantastic - this is great! One last thing...

By doing this, I notice that if my first entry into the "E" column is
"quote", then it puts the date into column M, but NOT column N at the
same time. I also created a third column (Column O) for the "sent"
code (entered in column E) and the same thing. If I were to start by
inserting "cb", and then rewriting column E with "quote" and then
rewriting column E with "Sent" - it all works perfect - however, I may
end up speaking with a prospect and sending the quote right then and
there while I am on the phone. in that situation i would START by
typing "Sent" into the E Column and would want both columns N and O to
populate with the date.

How do I do that?

Thanks,- Hide quoted text -

- Show quoted text -

Also, is there a way to ignore case? This method seems case sensitive
 
G

GS

Try this...

Option Explicit

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)
Select Case s
Case "QUOTE", Range("M" & n) = ""
Range("M" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "SENT", Range("N" & n) = ""
Range("N" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")
End Select

enditall:
Application.EnableEvents = True
End If
End Sub
 
M

magmike

Try this...

Option Explicit

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)
    Select Case s
      Case "QUOTE", Range("M" & n) = ""
       Range("M" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

      Case "SENT", Range("N" & n) = ""
       Range("N" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")
    End Select

enditall:
    Application.EnableEvents = True
  End If
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I have tried to modify this to fit my situation and have not had much
luck. I'm not sure where I have gone wrong, but here is my attempt:
NOTE: I need Column M to populate with a date regardless of what I
type in the first time, and if that initial text corresponds with
another one of the Case instructions below, that column should be
populated as well.

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)
Select Case s
'Case "", Range("M" & n) = ""
'Range("M" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "in", Range("N" & n) = ""
Range("N" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "quote", Range("O" & n) = ""
Range("O" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "sent", Range("P" & n) = ""
Range("P" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "req", Range("Q" & n) = ""
Range("Q" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")

Case "done", Range("R" & n) = ""
Range("R" & n).Resize(, 2) = Format(Date, "mm-dd-yyyy")
End Select

enditall:
Application.EnableEvents = True
End If
End Sub
 
G

GS

Mike,
You did state that you wanted the entries to NOT be 'case sensitive',
and so if you look at how the string variable 's' is loaded it contains
the 'Target.Value' in UPPERCASE. This means your Select Case checks
MUST ALSO be UPPERCASE.

Here's revised code that I think will do what you want...

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)
Range("M" & n) = Format(Date, "mm-dd-yyyy")
Select Case s
Case "IN", Range("N" & n) = ""
Range("N" & n) = Format(Date, "mm-dd-yyyy")

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

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

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

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

enditall:
Application.EnableEvents = True
End If
End Sub
 
M

magmike

Mike,
You did state that you wanted the entries to NOT be 'case sensitive',
and so if you look at how the string variable 's' is loaded it contains
the 'Target.Value' in UPPERCASE. This means your Select Case checks
MUST ALSO be UPPERCASE.

Here's revised code that I think will do what you want...

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)
    Range("M" & n) = Format(Date, "mm-dd-yyyy")
    Select Case s
      Case "IN", Range("N" & n) = ""
       Range("N" & n) = Format(Date, "mm-dd-yyyy")

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

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

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

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

enditall:
    Application.EnableEvents = True
  End If
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Perfect!
 
Ad

Advertisements

M

magmike

magmike formulated the question :







Great! ..glad to help.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Got a wierd issue with this - it works - however, when I come in the
next day, it doesn't work anymore, but the code is still there and
correct. To get it to work again, I have to delete the code, save the
workbook, close it, reopen, repaste in the code and then it will work
again.

Any ideas?

PS: Was this one supposed to be under an Option Explicit?
magmike
 
G

Gord

Sounds like macros are being disabled when the workbook is opened.

Check your Trust Center settings.

Best practices require all code to under Option Explicit.

Only way to effectively trouble-shoot faulty code.


Gord
 
Ad

Advertisements

M

magmike

  He could be missing the prompt right at the top of the page.

  Select 'enable'.  :)








- Show quoted text -

Got Macro issue fixed: Excel Options > Trust Center > Trust Center
Settings > Trust access to the VBA project object model

HOWEVER...

I noticed that when I put text into column 5 (E) a second time, it
changes the date in column M. As a reminder, this column should be
populated with the date when ANY text is entered into column E the
first, and then never change after that, even if new text is entered
into column E later on. Is there a way to fix that?

magmike
 
M

magmike

Got Macro issue fixed: Excel Options > Trust Center > Trust Center
Settings > Trust access to the VBA project object model

HOWEVER...

I noticed that when I put text into column 5 (E) a second time, it
changes the date in column M. As a reminder, this column should be
populated with the date when ANY text is entered into column E the
first, and then never change after that, even if new text is entered
into column E later on. Is there a way to fix that?

magmike- Hide quoted text -

- Show quoted text -

PS: I want the date to remain in column M, even if column E is deleted
later.
 
C

Clif McIrvin

Got Macro issue fixed: Excel Options > Trust Center > Trust Center
Settings > Trust access to the VBA project object model

HOWEVER...

I noticed that when I put text into column 5 (E) a second time, it
changes the date in column M. As a reminder, this column should be
populated with the date when ANY text is entered into column E the
first, and then never change after that, even if new text is entered
into column E later on. Is there a way to fix that?

magmike- Hide quoted text -

- Show quoted text -

PS: I want the date to remain in column M, even if column E is deleted
later.


--------

I added a test to Garry's code to see if there is already something in
column M -- if column M is not empty (BLANK) it is not modified.

I think this is the latest edition of Garry's code .... you need to
verify that .... or, just copy and paste the section that I changed.

Option Explicit

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("M" & n)
If IsEmpty(.Value) Then
.Value = Format(Date, "mm-dd-yyyy")
End If
End With

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

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

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

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

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

enditall:
Application.EnableEvents = True
End If
End Sub
 
Ad

Advertisements

M

magmike

PS: I want the date to remain in column M, even if column E is deleted
later.

--------

I added a test to Garry's code to see if there is already something in
column M -- if column M is not empty (BLANK) it is not modified.

I think this is the latest edition of Garry's code .... you need to
verify that .... or, just copy and paste the section that I changed.

Option Explicit

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("M" & n)
      If IsEmpty(.Value) Then
         .Value = Format(Date, "mm-dd-yyyy")
      End If
    End With

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

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

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

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

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

enditall:
    Application.EnableEvents = True
  End If
End Sub

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :)- Hide quoted text -

- Show quoted text -


Perfect!

Thanks!
 

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

Top