Results 1 to 7 of 7

Thread: VBA to insert text

  1. #1
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    395
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to insert text

    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!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,737
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: VBA to insert text

    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    395
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert text

    Hi Rick

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

    Thanks!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  4. #4
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    395
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert text

    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 by Russk68; Jun 19th, 2019 at 12:51 AM.
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  5. #5
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,324
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert text

    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 by Trebor76; Jun 19th, 2019 at 01:05 AM.

  6. #6
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    395
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert text

    That works
    Thank you!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  7. #7
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,324
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to insert text

    You're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •