Excel VBA - Create Text In A Cell That Cannot Be Deleted
Getting Started
Just to mention: This guide assumes you are familiar with VBA to an extent where you know how to open it and navigate to different areas in VBA. If not, a quick google search will give you all the info you need
To stop text from being deleted is easy, it's as simple as telling VBA to re-write it every time a cell is selected on the sheet and when the cell is activated.
In the above example, I am interested in preserving the text inside the black box in the to right corner. I'm also interested in keeping the formatting the same.
The Code
The code we're going to use is a very simple statement:
With Range("M2")
.Font.Color = RGB(180, 0, 0)
.Font.Size = 10
.Font.Bold = True
.Font.Italic = True
.Font.Underline = True
.Value = "Created By: Liam Bee"
End WithIn this "With" command we are telling VBA to fill range M2 with the following properties:
- Font.Color - Using the property RGB you can set the individual Red, Green and Blue amounts for the font colour
- Font.Size - Standard font size
- Font.Bold - If set to TRUE the cell will be Bold
- Font.Italic - If set to TRUE the cell will be Italic
- Font.Underline - If set to TRUE the cell will be Underlined
- Value = The value of the cell
Now we need to tell VBA when to execute this code. If we really want to make sure people can't get rid of the text easily, we need to execute the code every time the selection changes and every time the sheet is opened:
On Sheet Open
Private Sub Worksheet_Activate()
With Range("M2")
.Font.Color = RGB(180, 0, 0)
.Font.Size = 10
.Font.Bold = True
.Font.Italic = True
.Font.Underline = True
.Value = "Created By: Liam Bee"
End With
End SubOn Selection Change
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("M2")
.Font.Color = RGB(180, 0, 0)
.Font.Size = 10
.Font.Bold = True
.Font.Italic = True
.Font.Underline = True
.Value = "Created By: Liam Bee"
End With
End Sub
And that's it! A nice and easy way to stop a cell from being deleted, without having to protect the sheet with a password and annoying pop up boxes. Now the cell will simply re-write itself.
How'd It Go
Did you give this a go?
Rate This Guide
© 2014 Liam
Comments
No comments yet.