CODEWITHSUNDEEP
excelruntime-errorvba
Abhi
Abhi

Reputation: 3

Excel cell as a user input and as a formula

I am trying to make cell E23 as a user input and as a formula cell if user does not enter any value. For eg: If user enters a value in cell E23, then consider that value. If user does not enter any value then copy value from cell B23. Below is the vba code which I tried. 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$23" And Target.Value = "" Then
     Target.Formula = "=B23"
End If

The code works fine until I change the value in cell B23 by a selection made in the dropdown combobox. When I change the selection in combobox from option 1 to option 2, new value gets updated in cell B23 which must be copied into E23. But it gives me a runtime error '13' Type mismatch.

Any help is appreciated. Thank you

Upvotes: 0

Views: 619

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

The issue here is that this line

Target.Formula = "=B23"

changes the target cell, and that triggers a Worksheet_Change event that changes the target cell, and that triggers a Worksheet_Change event … and so on.

So you need to disable the events before you change the target cell (and re-enable them afterwards).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$23" And Target.Value = vbNullString Then
        Application.EnableEvents = False
            Target.Formula = "=B23"
        Application.EnableEvents = True
    End If
End Sub

Alternative that works also when multiple cells are selected:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E23")) Is Nothing Then
        Application.EnableEvents = False
        If Range("E23").Value = vbNullString Then
            Range("E23").Formula = "=B23"
        Else
            Range("E23").Value = Range("E23").Value - Range("E17").Value
        End If
        Application.EnableEvents = True
    End If
End Sub

Upvotes: 0

Related Questions