locked
Delete Cell content RRS feed

  • General discussion

  • I've got the following script in Excel 2010 and I want that if they delete the content of cell 1,31 (=A31) that also the content of cell 2,31 and 3,31 (B31 and C31) is deleted. Because they can choose an item from a list in 1,31 (A31) but when they delete that item then the other two cells should also be deleted. What do I have to type at the place with the question marks????
    Who can help me?????

      Dim objCel3 As Excel.Range, objCel4 As Excel.Range, objCel5 As Excel.Range
      
      For Each objCel3 In Target.Cells
        If ((objCel3.Row >= 31) And (objCel3.Column = 1) And (objCel3.Value <> "")) Then
          'MsgBox objCel3.Value & " | " & objCel3.Value2
          Set objCel4 = Me.Cells(objCel3.Row, 2)
          objCel4.ClearContents
          With objCel4.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=" & objCel3.Value & "Lot"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
          End With
          'MsgBox objCel3.Value & " | " & objCel3.Value2
          Set objCel5 = Me.Cells(objCel3.Row, 3)
          objCel5.ClearContents
          With objCel5.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="=" & objCel3.Value & "Norm"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
          End With
        Else
    ??????????????
        End If
        
      Next objCel3
    
      Set objCel3 = Nothing: Set objCel4 = Nothing
      Set objCel3 = Nothing: Set objCel5 = Nothing
    
    
    • Moved by kleinma Tuesday, February 1, 2011 3:27 PM VBA question (From:Visual Basic Language)
    Tuesday, February 1, 2011 10:58 AM