VBA Code Doesn't Recognize Cell Formular

ddddhhhh

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I am trying to get a shape to appear and disappear based on the result of a formula, but I am having trouble making this dynamic. The code below works when I hard code the value into cell U2, but when I try to have this formula =IF(V2<=Dashboard!$Z$2,1,0) in the cell to make it more dynamic, it will not work. I have seen a few mentions of this by setting up Cases, but honestly am not that well versed to make it work. Any advise would be great.


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
       
    If Intersect(Target, Range("U2")) Is Nothing Then Exit Sub
        If IsNumeric(Target.Value) Then
        If Target.Value = 1 Then
            With ActiveSheet.Shapes("Freeform 29")
                .Fill.Transparency = 0.5
                .Fill.ForeColor.RGB = RGB(20, 55, 90)
                .Line.ForeColor.RGB = RGB(20, 55, 90)
                .Line.Transparency = 0
            End With
        ElseIf Target.Value = 0 Then
            With ActiveSheet.Shapes("Freeform 29")
                .Fill.Transparency = 1
                .Fill.ForeColor.RGB = RGB(20, 55, 90)
                .Line.ForeColor.RGB = RGB(20, 55, 90)
                .Line.Transparency = 1
            End With
        End If
        End If
End Sub
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
The change event does run if the cell has a formula in it. You could use a calculation event, but that will run whenever any cell on the sheet re-calculates.
Another option would be to look at V2 rather than U2, but that won't register if Z2 on the dashboard changes.
 

ddddhhhh

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
The change event does run if the cell has a formula in it. You could use a calculation event, but that will run whenever any cell on the sheet re-calculates.
Another option would be to look at V2 rather than U2, but that won't register if Z2 on the dashboard changes.
Thanks for the quick reply! I need it to be dynamic so I need it to look at U2.

Can you please provide a little more detail on the change event! Would I just have to add in a code at the end of the sub to make it calculate the page every time?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
You would ned to use
VBA Code:
Private Sub Worksheet_Calculate()
   If Range("U2").Value = 1 Then
       With ActiveSheet.Shapes("Freeform 29")
           .Fill.Transparency = 0.5
           .Fill.ForeColor.RGB = RGB(20, 55, 90)
           .Line.ForeColor.RGB = RGB(20, 55, 90)
           .Line.Transparency = 0
       End With
   ElseIf Range("U2").Value = 0 Then
       With ActiveSheet.Shapes("Freeform 29")
           .Fill.Transparency = 1
           .Fill.ForeColor.RGB = RGB(20, 55, 90)
           .Line.ForeColor.RGB = RGB(20, 55, 90)
           .Line.Transparency = 1
       End With
   End If
End Sub
But as I said, it will run whenever any cell recalculates.
 
Solution

ddddhhhh

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You would ned to use
VBA Code:
Private Sub Worksheet_Calculate()
   If Range("U2").Value = 1 Then
       With ActiveSheet.Shapes("Freeform 29")
           .Fill.Transparency = 0.5
           .Fill.ForeColor.RGB = RGB(20, 55, 90)
           .Line.ForeColor.RGB = RGB(20, 55, 90)
           .Line.Transparency = 0
       End With
   ElseIf Range("U2").Value = 0 Then
       With ActiveSheet.Shapes("Freeform 29")
           .Fill.Transparency = 1
           .Fill.ForeColor.RGB = RGB(20, 55, 90)
           .Line.ForeColor.RGB = RGB(20, 55, 90)
           .Line.Transparency = 1
       End With
   End If
End Sub
But as I said, it will run whenever any cell recalculates.
Great way to simplify it! Thanks for the help. I am trying to do this for Freeform shapes 29-75 and the corresponding cells U2-u50. Is there an easy way to make this a loop so I dont have to repeat it 49 times!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Calculate()
   Dim Cl As Range
   For Each Cl In Range("U2:U50")
      If Cl.Value = 1 Then
          With ActiveSheet.Shapes("Freeform " & Cl.Row + 27)
              .Fill.Transparency = 0.5
              .Fill.ForeColor.RGB = RGB(20, 55, 90)
              .Line.ForeColor.RGB = RGB(20, 55, 90)
              .Line.Transparency = 0
          End With
      ElseIf Cl.Value = 0 Then
          With ActiveSheet.Shapes("Freeform " & Cl.Row + 27)
              .Fill.Transparency = 1
              .Fill.ForeColor.RGB = RGB(20, 55, 90)
              .Line.ForeColor.RGB = RGB(20, 55, 90)
              .Line.Transparency = 1
          End With
      End If
   Next Cl
End Sub
 

ddddhhhh

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

1609256506327.png
Getting an issue with the below. Again, thank you for your help!
ElseIf Cl.Value = 0 Then
With ActiveSheet.Shapes("Freeform " & Cl.Row + 27)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
Make sure that the shapes are consecutively numbered from 29 to 77
 

ddddhhhh

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I found the issue. Needed the range to be U2:U48 to correspond correctly with the shapes! Thanks, you have been alot of help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,091
Messages
5,640,059
Members
417,125
Latest member
sfreind

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top