Solved

2007 Excel Clear Content in Indirect Cells

Posted on 2010-09-01
8
312 Views
Last Modified: 2012-05-10
How do I apply this code (from the original question,which works perfectly on a single selection) to a range of cells? If B2 changes clear C2, if B3 change clear C3 etc

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = "$A$1" Then
      [B1] = ""
   End If

End Sub
0
Comment
Question by:notpandora
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

by:Runrigger
ID: 33576596
Private Sub Worksheet_Change(ByVal Target As Range)

  Activecell.offset(0,1).value = ""

End Sub
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33576604
Try this
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.column = 2 Then
     target.offset(0,1).clear
   End If

End Sub

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33576633
runrigger's solution will work on all columns, not just B, not sure if it is intended for all columns....If it is, then runrigger's solution would be more efficient.....
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 11

Expert Comment

by:Runrigger
ID: 33576699
I did intend it for all columns (question almost points to this, but that's my interpretation!), otherwise yours is best.
0
 

Author Comment

by:notpandora
ID: 33576930
I suspect I didn't add enough detail to the question :(
Column B is a List, Column C is another list (Indirect), For example a user can enter FT to Column B and NORM to Column C (OK) and then change Column B to Cas, Cas and NORM is not an allowable selection and NORM does not appear on the indirect list. if Cas is selected first.

MWGainesJr solution causes some sort of endless loop that crashes Excel
Runrigger solution wipes out the validation list in Column C
I have attached the file ith one line working on the enter sheet
Cheers, fc
cond-lists.xlsm
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 33577031
Try this:



Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngcell As Range
    On Error GoTo err_handle
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Application.EnableEvents = False
        For Each rngcell In Intersect(Target, Range("B:B"))
            rngcell.Offset(, 1).ClearContents
        Next rngcell
   End If

clean_up:
    Application.EnableEvents = True
    Exit Sub
err_handle:
    MsgBox Err.Description
    Resume clean_up
End Sub

Open in new window

0
 

Author Closing Comment

by:notpandora
ID: 33577092
Worked a treat thanks Rorya
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 33577112
this should get around the loop/crash issue!

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then
        ActiveCell.Offset(0, 1).ClearContents
    End If

End Sub
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question