excelvba

VBA - What could interfere with a ClearContents function?


I am trying to use the following code to clear contents of a given cell depending on which input method the user chooses (Simplified vs calculator).

Private Sub Worksheet_Changeo(ByVal ChangedCell As Range)

    Application.EnableEvents = True
    
    If Not Intersect(ChangedCell, Me.Range("B31")) Is Nothing Then
    
        If Me.Range("B31").Value = "Calculator" Then
                Range("D30:E30").Select
                Selection.ClearContents
        End If
        
    End If
    
End Sub

The code seems to work just fine when using it in a fresh workbook, so it seems that something else in my current workbook might be interfering with it (I did directly save it in the relevant sheet module btw). Does anyone have any ideas on what might be stopping the code from erasing the contents of those two cells?

Thanks,

I tried writing a code which automatically erases old iput cell contents when switching input method, it seems to work on a new workbook but not my current one and I can't tell why. There is no error message or anything.


Solution

  • A Worksheet Change: Conditionally Clear Another Range

    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Const CLEAR_RANGE_ADDRESS As String = "D30:E30"
        Const TARGET_CELL_ADDRESS As String = "B31"
        Const TARGET_VALUE As String = "Calculator"
        
        Dim tcell As Range: Set tcell = Me.Range(TARGET_CELL_ADDRESS)
        If Intersect(tcell, Target) Is Nothing Then Exit Sub
        
        Dim TargetString As String: TargetString = CStr(tcell.Value)
        If StrComp(TargetString, TARGET_VALUE, vbTextCompare) <> 0 Then Exit Sub
            
        On Error GoTo ClearError
            
        Application.EnableEvents = False
        
        Me.Range(CLEAR_RANGE_ADDRESS).ClearContents
        
    ProcExit:
        Application.EnableEvents = True
        Exit Sub
    ClearError:
        MsgBox "Run-time error [" & Err.Number & "]:" & vbLf & vbLf _
            & Err.Description, vbCritical
        Resume ProcExit
    End Sub