ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

Excel VBA - Create Text In A Cell That Cannot Be Deleted

Updated on August 24, 2014

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 With

In 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 Sub

On 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?

See results

Rate This Guide

4 out of 5 stars from 1 rating of this guide

© 2014 Liam

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.