Show / hide chart based on value in cell assigned to chart (multiple charts)

Start with this simple test:

Sub test()
If Len(Range("A1")) > 0 Then
    ActiveSheet.Shapes("Chart 1").Height = 300
    ActiveSheet.Shapes("Chart 1").Width = 400
Else
    ActiveSheet.Shapes("Chart 1").Height = 0
    ActiveSheet.Shapes("Chart 1").Width = 0
End If
End Sub

If it works as expected, just do it in a loop.

Hi,

Well, it could be in sheet code, but better place for it will be in a general module.
So:
* in VBA Editor (VBE) delete code from where it is now
* From menu bar (in VBE) select Insert -> module
* Paste in module slightly modified code (refering to sheets):

Sub test()
Sheets("Charts").activate
If Len(Sheets("Data").Range("A1")) > 0 Then
    ActiveSheet.Shapes("Group 14").Height = 300
    ActiveSheet.Shapes("Group 14").Width = 400
Else
    ActiveSheet.Shapes("Group 14").Height = 0
    ActiveSheet.Shapes("Group 14").Width = 0
End If
End Sub

As for looping - it would be easy if names of graps are related to for instance cell rows
like A2 - group 15
A3 Group 16 etc
then the code could be:

Sub test()
dim i as integer
Sheets("Charts").activate
for i = 1 to 5 'or more
  If Len(Sheets("Data").Range("A" & i)) > 0 Then
      ActiveSheet.Shapes("Group " & 13+i).Height = 300
      ActiveSheet.Shapes("Group " & 13+i).Width = 400
  Else
      ActiveSheet.Shapes("Group " & 13+i).Height = 0
      ActiveSheet.Shapes("Group " & 13+i).Width = 0
  End If
next i
End Sub

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

0) wow - 3 months delay :-)

as for 1):

Insert the code in "Charts" sheet code (right click on "Charts" tab, and from pop-up select View code):

Private Sub Worksheet_Activate()
If Len(Sheets("Data").Range("A1")) > 0 Then
    ActiveSheet.Shapes("Group 14").Height = 300
    ActiveSheet.Shapes("Group 14").Width = 400
Else
    ActiveSheet.Shapes("Group 14").Height = 0
    ActiveSheet.Shapes("Group 14").Width = 0
End If
End Sub

2) It's already there.

3) You shall determine pixels per inch at screen. As there is no attachment I've not tested the following, but it could work.

In standard module you can put the following code:

'Written: June 03, 2008
'Author:  Leith Ross
'Summary: Returns the Logical pixels per inch for the active monitor screen.
'         Both X and Y values are returned as an array with X as element(0).

 Public Declare Function GetDeviceCaps _
   Lib "GDI32.dll" _
     (ByVal hDC As Long, _
      ByVal nIndex As Long) As Long
 
 Public Declare Function GetDesktopWindow _
   Lib "User32.dll" () As Long

 Public Declare Function GetWindowDC _
   Lib "User32.dll" _
     (ByVal hWnd As Long) As Long
     
 Public Declare Function ReleaseDC _
   Lib "User32.dll" _
     (ByVal hWnd As Long, _
      ByVal hDC As Long) As Long


 Const LogPixelsX As Long = 88
 Const LogPixelsY As Long = 90

Function GetLogicalPixels()

  Dim Desktop As Long
  Dim hDC As Long
  Dim Pixels(1)
  Dim RetVal As Long
  
   'To get Points Per Pixel divide 72/Pixels(n)
    hDC = GetWindowDC(GetDesktopWindow)
      Pixels(0) = GetDeviceCaps(hDC, 88)
      Pixels(1) = GetDeviceCaps(hDC, 90)
    RetVal = ReleaseDC(GetDesktopWindow, hDC)
    GetLogicalPixels = Pixels
  
End Function

(from here: https://www.mrexcel.com/forum/excel-...-per-inch.html) and then use it in your code, like:

Private Sub Worksheet_Activate()
If Len(Sheets("Data").Range("A1")) > 0 Then
    ActiveSheet.Shapes("Group 14").Height = 4.28*GetLogicalPixels(0)
    ActiveSheet.Shapes("Group 14").Width = 6.44*GetLogicalPixels(1)
Else
    ActiveSheet.Shapes("Group 14").Height = 0
    ActiveSheet.Shapes("Group 14").Width = 0
End If
End Sub

Glad to hear so.
It convinces me that it's good time to mark thread SOLVED.

Please select Thread Tools from the menu link just above your first post and mark this thread as SOLVED. Thanks.