+ Reply to Thread
Results 1 to 15 of 15

Can a cell have "default" text in when empty?

  1. #1
    Registered User
    Join Date
    04-14-2015
    Location
    Key West, Fl
    MS-Off Ver
    2011
    Posts
    14

    Can a cell have "default" text in when empty?

    This is a crazy question but.........I want to have a cell display a message when it is empty, then when new text is added to display that. When new new text is removed I would like to have it revert back to the default test message that was originally put in.

    I know how to do this if I input the text in another cell using an "if" statement, but is there any way to do it all in one cell?


    I can't see how but you guys amaze me sometimes.


    Thanks,


    Mark Miller

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Can a cell have "default" text in when empty?

    I think (well, know) that you would need VBA for this. You would use a Worksheet Change event handler to monitor the cell(s) and, if there is no content, or if the content is deleted, you would put a default text message in the cell, and perhaps format it in light grey italics. When data is entered into the cell, you would check it is not the default text and, if it is not, accept it and make the format "standard".

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Can a cell have "default" text in when empty?

    I played with some code and came up with this.
    It works for cell A1 but can be adjusted to fit your needs.
    Place this in the worksheet you want to check.
    Just adjust sheet name and range.

    Please Login or Register  to view this content.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Can a cell have "default" text in when empty?

    That code will fire for any and every change on the worksheet. However, it will only check cell A1, as it stands.

    But then, in the loop, Cancel is not defined and is not a Boolean variable for the Change event. And, if there were more than one cell in the range, the loop would end when it found the first empty cell.

    As it is a Worksheet Change event handler, the ThisWorkbook.Sheets(1) would be redundant unless you want to make a change on a different sheet. Using the sheet index also assumes that the sheets will not be moved around by the user.

    Regards, TMS

  5. #5
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Can a cell have "default" text in when empty?

    I had copied that out of a larger piece of code I have in a workbook and adjusted it to a one cell range. That's what the original post asked for "a cell". I just didn't change everything I should have. The update below will work for as many blank cells as there are in the range.

    Please Login or Register  to view this content.
    OR this will work only when the target cell is changed and only work for that cell. May be more of what is needed.

    Please Login or Register  to view this content.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Can a cell have "default" text in when empty?

    Maybe this:

    Please Login or Register  to view this content.

    Please see attached workbook example


    Regards, TMS
    Last edited by TMS; 05-22-2015 at 03:00 PM.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Can a cell have "default" text in when empty?

    The update below will work for as many blank cells as there are in the range.
    Sadly, it won't.

    Please Login or Register  to view this content.

    The "Exit For" will drop out of the loop when you hit the first blank cell.

    Also, you are making a change to the value in a Worksheet Change event handler so it is likely to loop.

    Regards, TMS

  8. #8
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Can a cell have "default" text in when empty?

    Both codes I posted worked on my sheet when I tested them. I set the range in the first one too A1:A10 and it filed all 10 cells with default text when they were empty. The second code works on whatever cell you point it to. Like I said I tried both and they worked for me

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Can a cell have "default" text in when empty?

    The second code will work for cell A1, although it will fire twice if/when you delete the contents of cell A1

    Modify the code thus, put something in cell A1, then delete it.

    Please Login or Register  to view this content.

    Debug Output in the Immediate Window

    Please Login or Register  to view this content.

    The debug output demonstrates that when you delete the contents of the cell, the WSC event fires. It replaces the contents of cell A1 (blank), with the "Default Text Here" string ... which fires the WSC event a second time.


    Regards, TMS

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Can a cell have "default" text in when empty?

    Slightly modified version of first code.

    Please Login or Register  to view this content.

    If cells A1:A10 have the default text ... this is what happens when you select column A and press the delete key.

    Please Login or Register  to view this content.

    Regards, TMS

  11. #11
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    88

    Re: Can a cell have "default" text in when empty?

    OK, I don't get that deep into excel macros. I can make them work for what I need but that's about it. All I knew was that if the cell was empty it put default text in there and if you typed something it stayed and if you deleted what you typed the default text showed back up. I thought that was what the original post was after. I didn't check as far into it as you did because it only took a split second to run the code. Thanks for elaborating on what was actually going on behind the scenes that is very interesting.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Can a cell have "default" text in when empty?

    Thanks for elaborating on what was actually going on behind the scenes that is very interesting.
    You're very welcome. In the majority of cases, that is, a change to a single cell, it's not going to make a lot of difference. You just need to be aware of what is happening so that you can cater for the more complex situations.

    It was interesting to see how it stacks up though.

    Regards, TMS

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Can a cell have "default" text in when empty?

    Is this resolved?



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  14. #14
    Registered User
    Join Date
    04-14-2015
    Location
    Key West, Fl
    MS-Off Ver
    2011
    Posts
    14

    Re: Can a cell have "default" text in when empty?

    Thanks so much to everyone that helped with this post. You guys rock. Thanks again so much.

    Mark

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Can a cell have "default" text in when empty?

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Set default data format to "text" instead of "general"
    By Delta in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2012, 02:44 PM
  2. Replies: 8
    Last Post: 10-11-2011, 01:24 PM
  3. Replies: 1
    Last Post: 10-31-2010, 02:56 AM
  4. Replies: 3
    Last Post: 01-31-2008, 12:49 PM
  5. How to change default cell format to "Text"
    By rbecerra in forum Excel General
    Replies: 2
    Last Post: 09-09-2005, 11:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1