+ Reply to Thread
Results 1 to 12 of 12

VBA code to automatically clear a cells contents when another cell is empty

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-08-2010
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    386

    Re: VBA code to automatically clear a cells contents when another cell is empty

    Not sure what you are trying to accomplish but instead of 100 if statements use this

    Dim tmpbox as range
    
    set tmpbox = range("A8")
    activesheet.unprotect
    
    Do until tmpbox.row = 26
    If tmpbox.value = "" then
    tmpbox.offset(0,1).value = ""
    end if
    set tmpbox = tmpbox.offset(1,0)
    loop
    activesheet.protect
    Last edited by GaidenFocus; 07-25-2012 at 10:13 AM.

  2. #2
    Registered User
    Join Date
    07-25-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: VBA code to automatically clear a cells contents when another cell is empty

    hi GaidenFocus

    thank you very much for your reply,

    that actually cleared what i needed once i changed it to the following

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tmpbox As Range
    
    Set tmpbox = Range("A8")
    ActiveSheet.Unprotect
    
    Do Until tmpbox.Row = 26
    If tmpbox.Value = "" Then
    tmpbox.Offset(0, 2).Value = ""
    End If
    Set tmpbox = tmpbox.Offset(1, 0)
    Loop
    ActiveSheet.Protect
    
    End Sub

    however it only cleared cells C8:C25 if i already had the sheet unlocked. but when its locked it wont seem to unlock the sheet for me. Also it still only deletes contents of c8:C25 once i become active on the same sheet like select another cell or something with my mouse. Is there a way to code it so it does cell clearing of c8 to c25 with the sheet locked and hidden.

    thanks, and sorry my first post wasnt very clear

    Andrew
    Last edited by Cutter; 07-25-2012 at 10:51 PM. Reason: Added code tags

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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