Solved Instructional text in a cell

Microsoft Excel 2013 32/64-bit - license...
December 12, 2017 at 11:10:56
Specs: Windows 8
I need to put several lines of instructional text in a cell that disappears once the participant enters the information. I would like the text to be grey or a different color than the black.

I am not good at all with VBA (less than nil). Can anyone help?


See More: Instructional text in a cell

Report •

#1
December 12, 2017 at 11:32:08
✔ Best Answer
The following code is about as basic as it gets as it relates to your requirements.

All it does is set the Font color of the changed cell - any cell - to Automatic (Black). Therefore, if the current font color is set to grey (or red or blue, etc) as soon as the new text is placed in the cell, the font color will be set to Black.

To use it, you'll need to enter your instructions in the cell, then set the font color color to grey . If you set the font color to grey and then enter your instructions, the cell will have changed and the code will set it back to black.

Of course, the code could be written to only apply to certain ranges or to cells with certain criteria, but as written, it will apply to every cell in the sheet. If you need something more "specific" you'll need to supply some more detail.

Copy the following code, then right-click the sheet tab for the sheet that you want this to happen in and chose View Code. Paste the code into the pane that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
 Target.Font.Color = Automatic
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#2
December 12, 2017 at 11:42:16
Thank you DerbyDad03 but that doesn't help with the background instructional text that needs to display in the cell until someone clicks on the cell.

I don't want to put regular text in the cell and it changes color when other text is put in. I want to put instructional text in the cell that displays when other text is not written in the cell.

The cell has to contain instructions on what I want people to enter in the cell. Since the end users are not Excel savvy, entering the information in a comment won't do. The instructions must be displayed.

Danielle


Report •

#3
December 12, 2017 at 12:22:55
re: "Thank you DerbyDad03 but that doesn't help with the background instructional text that needs to display in the cell until someone clicks on the cell."

Actually, I believe it does. Allow me to provide some more details and we'll see if that helps.

Try this:

1 - Copy the code from my response
2 - Right-click the sheet tab for the sheet that you want this to happen in
3 - Choose View Code
4 - Paste the Code in the pane that opens
5 - Go back into the worksheet and select any cell
6 - Enter an instruction then hit Enter or click out of the cell
7 - Select that same cell, and without changing any text, set the Font color to any color you would like
8 - Select the cell and enter something else, just as if you are one of your users
9 - Once you hit Enter or click out of the cell, the text that was entered should now be black.

All that the code is doing is monitoring the sheet and whenever there is a change to the contents of a cell, it changes the format to Font Color = Automatic. That is why I noted that you have to set the font color of your instruction after you enter it and click out of the cell. The initial entry of that text is a "change" therefore the code is going to run and set the font color to black, even though the font color is probably already black. Setting the font color to grey afterwards is not a "change" as far as the macro is concerned so that will hold. It's only after your user replaces the instruction with the required information that the font color will be changed to black.

Does that help? Let me know if I have misunderstood your requirements.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
December 12, 2017 at 14:30:01
I guess I was not really paying attention when you first told me but this is great! Thank you so much DerbyDad03!
I will do this a little later... and let you know if I have any questions but it looks like you explained it perfectly!

Report •

#5
December 12, 2017 at 15:55:20
The only problem I see is the loss of the instructions as soon as the user starts to type. Once they are gone, they're gone. Obviously you know your process and your users, so if you think they'll remember what to do once the instructions are gone - and then the phone rings or someone walks into the office or they are otherwise distracted - then your idea may work out.

Let us know!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#6
December 12, 2017 at 16:50:18
DerbyDad03 all the cells in the column will have the information so if they type in one and forget they can look at another cell in the column.

Report •

#7
December 12, 2017 at 18:57:09
If you only want this to happen in a single column, there is no need for the macro to set the font color of every cell that is changed. You can specify the column(s) that you want this to happen in. The code will still run with every change, but it won't set the font color unless the change was made in the specified columns(s).

In VBA, a column is often referred to by its number, not its letter. In a Worksheet_Change macro, the column must be referred to by its number.

A = 1, B = 2, etc.

For example, this code will only set the font color to black if the change is made in Column C:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 3 Then
   Target.Font.Color = Automatic
 End If
End Sub

As I said earlier, the code will run with every change, but the first thing it will do is check to see if the target cell (the cell that was changed) is in Column 3 (C). If it's not, it will skip the instruction that sets the font. This is not only more efficient, but it will allow you to use different font colors in other columns and not have them set to black if you decide to change the contents.

Lastly, I set the code to check a single column based on your response. It could be set to check multiple columns, rows, a specific range of cells, etc. It all depends on what your overall requirements are.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#8
December 13, 2017 at 07:40:35
DerbyDadO3

Thank you... I am so new at this... I am trying to learn (teach myself). I do have a question. I decided I wanted the cell color to be grey before they enter and go back to nothing... so I put this line in: Target.Cell.ColorFormat = Automatic
It didn't work.
I also tried:
If Target.Column = 17 Then
Target.Interior.Color = 0
End If
but it didn't work either.

This is the code that I have now... and nothing is working... not even the text color

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 16 Then
Target.Font.Color = Automatic
End If

If Target.Column = 17 Then
Target.Font.Color = Automatic
End If

If Target.Column = 17 Then
Target.Interior.Color = RGB(255, 255, 255)
End If

End Sub


message edited by Daniej


Report •

#9
December 13, 2017 at 09:22:22
Please take my following comment in the spirit in which it is intended. I'm simply trying to make your life easier.

I don't know how you came up with that syntax of Target.Cell.ColorFormat = Automatic, but if you simply "made it up", I strongly suggest that you refrain from that practice until you are much further along in your VBA skills. You'll just frustrate yourself by guessing at the syntax and the chance of stumbling across the correct syntax is infinitesimal.

How many guesses would it have taken you to get to one of these, both of which should work for you?

Target.Interior.Color = xlNone

or

Target.Interior.Pattern = xlNone

Google is a great way to find the correct way to accomplish things in VBA. Something like vba cell color no fill or vba set cell color to white etc. will return lots of hits. Start your search string with vba to help eliminate Excel related suggestions. (Excel and VBA are 2 very different things.)

You might want to review this Tutorial which I wrote many years ago while learning VBA. I still use these debugging techniques on a daily basis.The techniques are not just for fixing broken code, they can also be used to help you understand how working code that you find on the web does what it does.

https://www.computing.net/howtos/sh...

message edited by DerbyDad03


Report •

#10
December 13, 2017 at 10:16:18
Thank you DerbyDadO3

I would not have found this syntax xlNone but I had the first part!

I actually did look on the net but nothing I was entering was working.

I will definitely review your tutorial!


Report •

#11
December 13, 2017 at 11:03:12
DerbyDad03

The following is my code for the spreadsheet. Columns 27 and 28 work fine. In Column 29 the color of the text doesn't change but the cell color does. Can you please me what I am doing wrong for column 29?

Private Sub Worksheet_Change(ByVal Target As Range)
'
If Target.Column = 27 Then
Target.Font.Color = Automatic
End If
'
If Target.Column = 28 Then
Target.Font.Color = Automatic
End If
'
If Target.Column = 29 Then
Target.Font.Color = Automatic
End If
'
Target.Interior.Color = xlNone
End Sub


Also DerbyDad03 - will this work if I hide columns? My current spreadsheet has 8 sheets. Not all of the locations (a location is a sheet) has the same job type so my boss wants me to only display the job types for that location.

The other thing I plan to do is send only the sheet pertaining to the location to that location. Once I get the sheet back I will paste it into a Master that is set up to count how many "yeses" are displayed in a given location (cell).

A bit confusing (sorry for that).

By the way - I can't thank you enough for all your help!

message edited by Daniej


Report •

#12
December 13, 2017 at 13:13:43
First, please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum.

As far as your question about Column 29, I'm not having any problem with the font color changing to black.

Are you sure you don't have a column hidden such that you aren't really changing Column 29? Column 29 is Column AC.

The Interior cell color change is going to be set to black for every cell because you didn't specify a column for that instruction. In other words, it isn't clearing the fill color in Column 29 because you changed Column 29, it's clearing the target cell because it will clear any and every cell in the worksheet.

As far as the code working if you hide Columns, yes, it will work. The Column number doesn't designate the number of the visible columns, it designates the number of the physical column. In other words, Column AA will be Column 27 regardless of whether it is hidden or not. Just think about hard it would be to write VBA code to reference a column if the number of that column changed every time a use hid or un-hid columns.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#13
December 13, 2017 at 14:37:24
Thank you

message edited by Daniej


Report •

#14
December 13, 2017 at 17:51:17
Please start a separate thread for this question with a relevant subject line.

That keeps the archives orderly and helps others find threads when searching for solutions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question