Strikethrough Text In Cell Excel VBA Macro

In this post we see how to strikethrough the text in a cell in Excel using VBA macros. The macro example consists of an event procedure triggered when trying to delete the content of a cell. It actually prevents deleting the value of a cell and strikes through the content instead. This somehow protects the content in a worksheet from being deleted, without using worksheet protection, and may be useful when sharing a worksheet with other users.

WARNING: The code below prevents from selecting multiple cells and deleting the value of cells in the sheet where the VBA code is added.

Macro/VBA code:

 

  [In a sheet module]

 

  Dim myval As String

 

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Selection.Cells.Count > 1 Then ActiveCell.Select
      myval = ActiveCell.Value  
  End Sub

 

  Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Value = "" And myval <> "" Then
          Target.Value = myval
          Target.Font.Strikethrough = True
      End If
  End Sub

 

 

Macro explained:

The code is added to a sheet module instead of a standard module. It will avoid selecting multiple cells and delete the content of cells in that particular sheet.

• We declare the variable myval at the module level. This variable stores the value as text of any selected cell.
• Then we have two event procedures, Worksheet_SelectionChange and Worksheet_Change. The first one triggers when the selection changes, i.e. when we select any cell using the mouse or keyboard arrows. The second procedure triggers when the value a cell changes.
• Worksheet_SelectionChange first checks if the selection is a single cell, otherwise it re-selects the active cell only. Then it saves the value of the cell into the variable myval. This variable keeps the value after macro execution because it has been declared at module level.
• Worksheet_Change checks if the cell that has changed is now empty (the value has been deleted, or it was already empty), and if the value has previously been written to myval (this will always be the case except the first time, just after opening the workbook). Then it adds the value again, and this happens so far that we cannot actually see that the value has first been deleted. Then it strikes through the value in that cell. As a result, it prevents from deleting the value, but indicates that the user wants to remove that value with strikethrough. We could also highlight the cell changing the interior color or the font color, instead of using strikethrough.

The code to strikethrough the value of a cell corresponds just to the last one line. If we want to use it outside the event procedure we could simply write the following (this applies to the active cell, but that can be changed with any other cell or range object reference): ActiveCell.Font.Strikethrough = True

This is how we strikethrough the value in a cell in Excel with VBA macros.

Other examples:

• How to Read API JSON Response with Excel VBA macros
  
• How to Send HTTP Request to Connect to API with Excel VBA macros
  
• How to Sort Elements In Array with Excel VBA macros