+ Reply to Thread
Results 1 to 3 of 3

Automatic code to execute only if certain cell is empty

Hybrid View

kosherboy Automatic code to execute... 06-25-2014, 10:04 PM
mohan.pandey87 Re: Automatic code to execute... 06-25-2014, 10:16 PM
kosherboy Re: Automatic code to execute... 06-25-2014, 10:49 PM
  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Automatic code to execute only if certain cell is empty

    Hi.
    Not sure what i'm doing wrong here but excel is sending me an error message. Basically what i'm trying to do is have an automatic code run only if a certain cell is empty, see below ( i made the font red where the code error refers to):
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("AU26") = "" Then
        If Target.Address <> "$F$14" And Target.Address <> "$E$3" And Not Target.Column > 12 Then
            On Error GoTo ErrHandler
            Application.EnableEvents = False
            Target.Formula = UCase(Target.Formula)
        End If
        
        If Target.Address(0, 0) = "E3" And Target.Value <> "" Then
            Target.Value = Application.WorksheetFunction.Proper(Target.Value)
        End If
        
        If Target.Address(0, 0) = "F8" And Target.Value <> "" Then
        Range("D22").Value = Target
        End If
        
        If Target.Address(0, 0) = "F10" And Target.Value <> "" Then
        Range("D23").Value = Target
        Range("N8").Value = Target
        Range("N8").Value = Application.WorksheetFunction.Proper(Range("N8").Value)
        End If
        
        If Target.Address(0, 0) = "F12" And Target.Value <> "" Then
        Range("D23").Value = Range("F10") & " " & Target
        Range("N8").Value = Range("F10") & " " & Target
        Range("N8").Value = Application.WorksheetFunction.Proper(Range("N8").Value)
        End If
        
        If Target.Address(0, 0) = "N8" And Target.Value <> "" Then
            Target.Value = Application.WorksheetFunction.Proper(Target.Value)
        End If
            
    ErrHandler:
            Application.EnableEvents = True
            End If
    End Sub

  2. #2
    Registered User
    Join Date
    09-17-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Automatic code to execute only if certain cell is empty

    Try like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Me.Range("AU26").Value = "" Then
        If Target.Address <> "$F$14" And Target.Address <> "$E$3" And Not Target.Column > 12 Then
            On Error GoTo ErrHandler
            Application.EnableEvents = False
            Target.Formula = UCase(Target.Formula)
        End If
        
    'and rest of the code here
            
    ErrHandler:
            Application.EnableEvents = True
            End If
    End Sub

  3. #3
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Automatic code to execute only if certain cell is empty

    That did the trick. Thanks!
    Can you give me a brief explanation what the me.range did?
    This is new to me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] still have empty cell after run Delete empty cells code
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2012, 04:28 PM
  2. When cell changes, execute code
    By kirkland in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-15-2012, 09:06 AM
  3. macro to execute code from cell.value?
    By wamp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2010, 04:29 AM
  4. Execute code based on cell entry
    By rkjudy in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-29-2009, 01:38 PM
  5. Automatic avoiding empty cell
    By Amag in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-29-2006, 01:40 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1