• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Making a cell display text even if cell is cleared/deleted

J

JPhotonics

Member
I have attached the file I am working on. Up in my top section, I want people to input the Name of their Project, the Project Manager's Name, Contact Email and Contact Phone. I have currently set up conditional formatting to keep the text black and turn the fill of the cell from a tan to no fill when any text other than what is displayed is typed in.

What I'd like to do, is make these default displays be displayed on the form if the person goes in and deletes the information they have put in.

Example:
1.) I open the form and put in "Project 123" for Project name.
2.) Cell C3:E3 turns white and displays "Project 123" left justified.
3.) I decide that "Project 123" was the wrong name, so I want to delete it.
4.) Currently cells C3:E3 will stay white and blank if "Project 123" is deleted... is there a way to make it still display "< Project Name >" and change the fill back to the original tan color if I delete the recently added/changed contents of my cell(s)? Or is this not possible to accomplish in Excel?

If I need to explain better, please just let me know.
 

Attachments

  • Stage Gate Checklist - Newest.xlsm
    14.9 KB · Views: 1
I don't think what you are looking for is possible without VBA. Are you ok with code solution?
 
Yes, that is not a problem, but I would love to know WHY/HOW the VBA code does what it does instead of just being provided with the answer. I currently have some VBA code in this document, I'll re-attach with my current code. The current VBA code is being used to create a time stamp when either "Approved" or "Approved w/ Comments" is selected from the drop-down menu and allowing the time stamp to only be cleared if "Rejected" or "Not Applicable" is selected. I have a general idea of why/how this works, but do not know explicitly what it all means.

Code pasted below and updated file attached.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$E$11" And Target.Address <> "$E$16" And Target.Address <> "$E$21" Then
If Range("E10").Text = "Approved" Or Range("E10").Text = "Approved w/ Comments" Then
If Range("$E$11").Text = "" Then
Range("E11").FormulaR1C1 = "=NOW()"
Range("E11").Copy
Range("E11").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Else
Range("E11").FormulaR1C1 = ""
End If

If Range("E15").Text = "Approved" Or Range("E15").Text = "Approved w/ Comments" Then
If Range("$E$16").Text = "" Then
Range("E16").FormulaR1C1 = "=NOW()"
Range("E16").Copy
Range("E16").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Else
Range("E16").FormulaR1C1 = ""
End If

If Range("E20").Text = "Approved" Or Range("E20").Text = "Approved w/ Comments" Then
If Range("$E$21").Text = "" Then
Range("E21").FormulaR1C1 = "=NOW()"
Range("E21").Copy
Range("E21").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Else
Range("E21").FormulaR1C1 = ""
End If

End If

End Sub
__________________________________________________________________
Mod edit : thread moved to appropriate forum !​
 

Attachments

  • Stage Gate Checklist - Newest.xlsm
    20.5 KB · Views: 3
Put this at top of your code.
Code: 
If Not Intersect(Target, Range("C3")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Project Name >"
        Target.Interior.ColorIndex = 40
    End If
End If

Explanation:
First line checks if target intersects cell C3. Since double negative is used...
It will fire the code if target does intersect C3.

2nd line resizes target (C3:E3 merged cell) to 1 row and 1 column (I.E. C3).
Since value is only stored in Top Left cell of merged range. Otherwise, you will get type mismatch error.

3rd line simply writes string "< Project Name>" to C3.

4th line, used entire target range (C3:E3) and set the interior color by index#.
 
That works awesome! I was able to change the cell and the text value to make it work for cells C4, C5 and C6. One problem though.

In cell C5, I ask the person using the sheet to insert an email address. The problem I'm having is that it keeps "< Primary Contact Email >" formatted in blue text with an underline as if it was an actual email address if I delete the contents that have been added. Everything else works properly though. Here are the small changes I made to the code:

Code: 
If Not Intersect(Target, Range("C3")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Project Name >"
        Target.Interior.ColorIndex = 40
    End If
End If

If Not Intersect(Target, Range("C4")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Project Manager >"
        Target.Interior.ColorIndex = 40
    End If
End If

If Not Intersect(Target, Range("C5")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Primary Contact Email >"
        Target.Interior.ColorIndex = 40
    End If
End If

If Not Intersect(Target, Range("C6")) Is Nothing Then
    If Target.Resize(1, 1).Value = "" Then
        Target.Resize(1, 1).Value = "< Primary Contact Phone >"
        Target.Interior.ColorIndex = 40
    End If
End If
 
I got it to work! I added these 3 lines of code to get it to send me back text that was NOT underlined, NOT blue and to remove the clickable link (hyperlink) functionality.

Code: 
Range("C5").Hyperlinks.Delete
Range("C5").Font.Underline = False
Range("C5").Font.Color = 0
 
You must log in or register to reply here.
Back
Top