This question has been asked before, but as a novice Excel user I cannot grasp the mechanics of getting this code to work. I'd like to have a cell contain instructions that disappear as text is entered into the cell. Is there someone that could walk me through this?
Link to previous post: http://www.excelforum.com/excel-prog...tructions.html
Sorry - also a forum novice
TIA
The information in the linked thread is pretty complete. If you need more help than that, attach your file, and tell us what cell you want the instructions in. Then we can install the code into your file and explain how it works.
shg's code:
You would copy and paste this code into the worksheet that you want it to operate on. As it stands, it only checks cell A1. If you select cell A1 and delete the contents, it will put the text "Please type question here" in faint gray italics. If you type anything else, it will change the font colour to "automatic".
Please Login or Register to view this content.
You could probably also include a Selection Change event in to make sure the prompt was put there the first time the cell was selected.
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
Hi Holly and welcome to the forum,
Excel has things called Event Macros that fire when things are done on a worksheet. The attached example has a macro behind Sheet1 that fires every time something changes (you type something into a cell). It then checks which cell you typed into which is called the "Target" cell. If the Target cell is changed then the VBA code does its thing. In this case it puts some text into the target cell based on the string you want to go there, as in the VBA code.
I've added a second cell that gets your prompt so you have two examples to see how it works. I hope a second example will let you understand it better. See the attached. Thanks goes to shg for the original code.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Great - I'd definitely like to get a better understanding of how it works. The red text is what I need to be encoded.
Coating Thickness.xlsx
Thanks!
Thanks! I'm going to have to ask a remedial question -- how do I get the VBA code to launch to the worksheet once I have identified the parameters?
One way:
Please Login or Register to view this content.
Regards, TMS
Ok Holly,
See if this works.
Wow, you guys are awesome!! Thank you!
The only thing I would like to change is that the text being entered doesn't reformat to normal conditions, but it looks like the code is correct. Any ideas?
Thanks for the rep
Not sure I understand. It works fine in the sample I tested. Makes it red and italic or automatic and not italic.the text being entered doesn't reformat to normal conditions, but it looks like the code is correct. Any ideas?
What's it not doing that it should? Or doing that it shouldn't?
Nevermind! I think I gave myself too much credit using the term "novice"!
If I did want to make any changes to the code, such as changing the font color to gray rather than red, would I simply update the part of the code that controls the color and then change the drop down to activate to get the new code to refresh the worksheet? Example:changes toPlease Login or Register to view this content.Thanks again!Please Login or Register to view this content.
vbGray isn't a valid vb constant.
Copy this code into a standard module and run it ON A BLANK WORKSHEET
Please Login or Register to view this content.
Some nice colours there to choose from.
Note a) that the original code from shg used ColorIndex = 15 for gray/grey whatever, b) the sample code above uses ColorIndex and c) the code I provided was amended to use Color = vbRed, NOT ColorIndex.
Regards, TMS
Thanks very much, TMS!
Now that I have the ColorIndex and have updated the VBA code for the color I want... when I select run I am prompted to enter a Macro Name. Where do I get that?
The code you have is an event macro. You don't run it. It sits "behind" the worksheet and it fires automatically when you make a change to any of those four cells on the worksheet.
Regards, TMS
I have my yearly question - do I really need to pay $20 to start a new thread?! (That's not the question!)
Management has finally decided to use my awesome spreadsheet, but of course, I forgot the password I used to protect the worksheet.
I have successfully recreated the macro, but now am having trouble with the security settings. The macro doesn't seem to run properly in the cells that need to be edited by users. I'm not sure if this is due to the upgrade to MS 2010, but it is driving me crazy.
Any help?
It is probably not due to the upgrade. This may be tough to diagnose by remote control; can you attach your latest file?
Here is the latest version. The mF-134 Rev 3 - Hidden Row 11.xlsmacros is active for cells A4, B4, A7, & B7 - I need users to have the ability to update these cells, while the macros continues to run. I have been receiving an error due to the italics... as far as I can tell anyway. Rows 11 and 12 need to be kept uneditable. Thanks for your help!
You are getting into a self-referential loop when your code updates the value to show a prompt. When the lines starting ".Value =" are executed, it is a worksheet change and therefore causes this sub to be called again before it finished the first pass. Use this update, add the code in red.
Also, the conditions are mutually exclusive so it is a best practice to use ElseIf instead of independent If statements. (You could also use Case Select.)
Also, I recommend you use Option Explicit to guarantee all variables are declared.
Please Login or Register to view this content.
Last edited by 6StringJazzer; 12-16-2015 at 11:24 PM.
Thank you for your help! I've locked all the necessary cells and protected the sheet, but I am still getting the error message when trying to update the editable cells that it cannot format as outlined in the Sub.
Runtime Error.JPG
You didn't mention anything about protecting the sheet earlier, and that's important. Are you using a password to protect it?Originally Posted by HollyB1285
You can't change the format of a locked cell on a protected sheet, unless it's explicitly allowed when you protect it. The code will have to turn off protection to be able to make that change. Add code in red. If you are using a password, you will have to add the password as an argument to the lines in red.
Please Login or Register to view this content.
Sorry, I should have been more explicit in the inquiry yesterday. I am password protecting the sheet, BUT the cells that need to be edited by users are the cells that are formatted by VBA. So, I guess the actual question is, how do I protect the Macro (keep it running) while leaving those specific cells unlocked for editing by users?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks