VBA to insert text

Russk68

Well-known Member
Joined
May 1, 2006
Messages
589
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
Hi Rick

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

Thanks!
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top