VBA to insert text

Russk68

Active Member
Joined
May 1, 2006
Messages
466
Hi all
I would like a word to be inserted into a cell if it is blank.

Example:
If A1 has any value in it and it is deleted and A1 is blank, the word "Spare" will appear.

Thank you!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,932
Office Version
2010
Platform
Windows
Maybe this Change event code...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Len(Target.Value) = 0 Then
      Application.EnableEvents = False
      Target.Value = "Spare"
      Application.EnableEvents = True
    End If
  End If
End Sub
HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

Russk68

Active Member
Joined
May 1, 2006
Messages
466
Hi Rick

How could I add individual cells to this? I tried "A1,A3" but did not work.

Thanks!
 

Russk68

Active Member
Joined
May 1, 2006
Messages
466
Hi Rick

I want to add this to numerous cells and this is what I did, which is working. Is there an easier way? I was hoping to add ranges.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Len(Target.Value) = 0 Then
      Application.EnableEvents = False
      Target.Value = "Spare"
      Application.EnableEvents = True
    End If
  End If
  If Target.Address(0, 0) = "A2" Then
    If Len(Target.Value) = 0 Then
      Application.EnableEvents = False
      Target.Value = "Spare"
      Application.EnableEvents = True
    End If
  End If
End Sub
 
Last edited:

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,597
Hi Russk68,

As an example, the following will work for cells A1, A3 and the range B5:B10

Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1,A3,B5:B10")) Is Nothing Then
        If Len(Target.Value) = 0 Then
            Application.EnableEvents = False
                Target.Value = "Spare"
            Application.EnableEvents = True
        End If
    End If
    
End Sub
HTH

Robert
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,605
Messages
5,469,645
Members
406,664
Latest member
Bruister

This Week's Hot Topics

Top