Clear Cell When Another Is False

    Hey, first time poster here for you guys. I am pretty good w/ Excel, except for the VBA aspects of it. But this should be an easy one for you guys...


    I want to clear the value of a cell based on if a different cell is false. The cell to be cleared is a Validation List, but I want the default to be blank based upon the value of a seperate cell (located in another worksheet). Example.


    If cell C7 in WorkSheet1 = false, then cell K17 in WorkSheet2 should be cleared.


    Another option. I have a macro (called DeleteK17) set up to clear cell K17 in WorkSheet2. Is there a way to run the macro if cell C7 in WorkSheet1 = false?

    Re: Clearing A Cell When Another Cell Value Is False


    Right Click sheet1 > select view code > paste in the below


    It will call DeleteK17 if C7 equals False


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "C7" Then
    If Target.Value = "False" Then
        Call DeleteK17
    End If
End If
End Sub


    VBA Noob

    Re: Clear Cell When Another Is False


    Thank you very much. I think there is one other thing I need to add though. I don't want the macro to run until after I select Worksheet2.

    Re: Clear Cell When Another Is False


    Try this in sheet 2.


    Code
    Private Sub Worksheet_Activate()
    If Sheets("Sheet1").Range("C7") = "False" Then
            Call DeleteK17
    End If
End Sub


    VBA Noob

    Re: Clear Cell When Another Is False


    Thank you very much again. That is exactly what I needed.


    [hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Using your second example,iIs there a way to clear cell K17 w/o using a macro?


    The only problem I am running into now is that when I switch to the workbook, and the macro runs, the cell that the macro clears is selected. I'd like to clear the cell but w/o the cell actually being selected (if possible).

    Re: Clear Cell When Another Is False


    I assume your code is doing the selecting. You don't need to select to delete a cell


    e.g This clears the cell


    Code
    Sheets("Sheet1"). Range("C7").ClearContents


    VBA Noob

    Re: Clear Cell When Another Is False


    Yes sir, my macro is the culprit. I'll try this code, probably what I am looking for..


    BTw - Thx for all your help. I am slowly learning :)

    Re: Clear Cell When Another Is False


    Each time a validation item is selected for cell A1, even if the same name as already in cell A1, excel determines that a change has occurred on the sheet. Therefore, you need a helper cell to keep track of the current name in cell A1 to test against to determine if the name in cell A1 has actually changed. This code uses cell A2 as the helper cell.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If Range("A1") <> Range("A2") Then Range("B1") = ""
        Range("A2") = Range("A1")
    End If
End Sub

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login