Clear cell based on value of another

[hkelly393]

I have a cell "intsel" that changes based on the value of the drop down "intseltext". "intseltext" is a list and depending on what is selected "intsel" value changes from 1-4.

I would like to clear the value (not the formatting) from cell "digs" depending on the value of "intsel".

Therefore:
if "intsel" = 1 or 2 then "digs" is cleared of any text.

I don't want to hide text by using cell formatting or fill in. I just need it cleared of any text value.

Can anyone help?

 

[Yongle]

Erasing a value requires VBA
One cell being cleared as a result of a change in value of another cell requires an EVENT macro
The trigger is that the value in intseltext is changing (NOT intsel)

Goes in sheet module (does not work if placed in standard module) - see notes below

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [intseltext]) Is Nothing Then
        Select Case [intsel]
         Case 1, 2
            [digs].ClearContents
         Case 3
            'what happens if value is 3
         Case 4
            'what happens if value is 4
         Case Else
            'otherwise do this
        End Select
    End If
End Sub

If nothing happens if any other vlaue than 1 or 2

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [intseltext]) Is Nothing Then
        Select Case [intsel]
         Case 1, 2:             [digs].ClearContents
        End Select
    End If
End Sub

or can use a simpler construction

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [intseltext]) Is Nothing Then
         If [intsel] = 1 Or [intsel] = 2 Then [digs].ClearContents
    End If
End Sub

Notes
1. to place code in sheet module
right-click on sheet tab
view code
paste code into the window which appears
[ALT}{F11] to go back to Excel
workbook must now be saved as macro-enabled

2. include only ONE version of the sub in the sheet module

 

[Yongle]

If you simply want the value in digs to be an empty string, then can use a formula

=CHOOSE(intsel,"","","value if 3","value if 4")

Yoo cannot mix and match
if you want to retain the text for 3 and 4 but have an empty string for 1 and 2 then
- either use VBA (as above)
- or use a helper cell to hold the text and then you can use a formula in digs with this logic
if instel = 1 or 2 then empty string otherwise value in helper cell

 

[hkelly393]

Thanks for this! I was definitely using the wrong cell reference in my solution (which I didn't post here originally).

I should have mentioned that "digs" is a merged cell and I'm running into a Run Time error "you cannot do that to a merged cell".

Any work around?

 

[Joe4]

I should have mentioned that "digs" is a merged cell and I'm running into a Run Time error "you cannot do that to a merged cell".

Merged cells are awful and wreak havoc with all sorts of stuff like VBA, sorting, etc.
If you are just merging multiple columns across individual rows, try replacing merged cells with the "Center Across Selection" formatting option, which gives you the same visual effect without all the issues that merged cells cause.

See this for instructions on how to do that: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[hkelly393]

I'll see what I can do. This particular document is a contract of sorts so sorting isn't even being utilized. I'll check the center across selection and see if it can be used. Thanks!

 

[hkelly393]

Excellent tip! That worked beautifully. Now I just have to fix my conditional formatting which was based on these merged cells but that'll be a piece of cake! I think I've got it with your help. Again, Thank you!

 

[Joe4]

You are welcome!

Yes, you will want to try to avoid using merged cells for all the headaches they cause. Most Excel developers avoid them at all costs!

 

[Yongle]

I should have mentioned that "digs" is a merged cell

- agreed

Merged cells are awful and wreak havoc with all sorts of stuff like VBA, sorting, etc.

- agreed

Any work around?

- Yes. Try this but qualify with sheet ref if on a different sheet

Range("digs").MergeArea.ClearContents