Excel Formula to define VB action - Please help refresh after cell changes value

Asked Mar 31 '20 at 16:34

Active Mar 31 '20 at 16:42

Viewed 25 times

I am creating a label printing app in excel. The user will pick the product from a dropdown menu which in turn will lookup values to fill Cells A1, B1 and C1 (either X or 0)

X means to show a diamond shape, 0 to hide it.

if I change the value manually, the diamond appears/disapears. If I use the dropdown menu, the formula changes values of cells A1, B1 an C1, but VB ignores the change. Any ideas? thank you.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 1 And Target.Column = 1 Then _
    Me.Shapes("Diamond 1").Visible = (Cells(1, 1).Value = "x")

If Target.Row = 1 And Target.Column = 2 Then _
    Me.Shapes("Diamond 2").Visible = (Cells(1, 2).Value = "x")

If Target.Row = 1 And Target.Column = 3 Then _
    Me.Shapes("Diamond 3").Visible = (Cells(1, 3).Value = "x")

End Sub

edited Mar 31 '20 at 16:39

BigBen

46,229
7
24
40

asked Mar 31 '20 at 16:34

Guilherme Brum

is the input `x` or `X`? I noticed you put `X` in your question but `x` in your code. – Mech Mar 31 '20 at 16:38
The change event is not triggered by formulas. There is a calculate event. – SJR Mar 31 '20 at 16:40
right. x. after the formula updates the cell value, it does not run the VB, if I click the cell and then click enter, VB runs – Guilherme Brum Mar 31 '20 at 16:41
2

You need to use `Worksheet_Calculate`, not `Worksheet_Change`. The change event isn't triggered when formulas recalculate. – BigBen Mar 31 '20 at 16:42
This completely solved my problem. Thank you – Guilherme Brum Mar 31 '20 at 20:42

Excel Formula to define VB action - Please help refresh after cell changes value

0 Answers0