VBA Code to Desplay text format of cell when cell is empty

[James Allensworth]

In my worksheet cell A30, which is the anchor cell of multiple merged cells. I have the custom format "Comments/Clarifications:" @. When characters including spaces are entered in the merged range then the format appears. When the cell is empty of all characters including spaces then the merged range appears empty without the "Comments/Clarifications:" text. Which, as you know, is the way Excel operates. What I cant to do is use VBS Code to look at the cell when data has been deleted and insert a blank space so the formatted text statement will appear.

This what I have tried;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
Set c = Range("a30")

If Selection.Count = 1 Then
If Not Intersect(Target, c) Is Nothing Then
c.Value = " " & c.Value On this command line I have also tried using a call statement to load a Macro which basically would put a space in cell a30
End If
End If
End Sub

Thank you for any thoughts or workarounds.
Jim

 

[6StringJazzer]

I think your overall approach is bending over backwards to do something that would be easier to do a completely different way.

First, the use of merged cells is inadvisable, especially when using VBA. However, you would still have this same question without the merged cells.

I would redesign your sheet to use a cell to have the label "Comments/Clarifications" instead of using a custom format to put it in the same cell with the data. That just eliminates the whole problem.

 

[James Allensworth]

Thank you for your thoughts. Maybe that is what I will need to do in the end. Still hoping that someone will have the answer.