+ Reply to Thread
Results 1 to 2 of 2

VBA to Show/Hide multiple shapes based on cell values

Hybrid View

  1. #1
    Registered User
    Join Date
    06-29-2012
    Location
    Stratford, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    6

    VBA to Show/Hide multiple shapes based on cell values

    I have the following code in a workbook I am currently working on that hides certain shape files. The macro works as intended. The issue I have is that there are 200 shapes per work book and 6 workbook so I would prefer to not have to write the code for 1200 shapes. Is there a way to modify this code that will allow it to look for all shapes at once. I am not concerned about the name of the shape at all as long is it is unique. Essentially if the cell the shape is associated with is empty I would like to hide the shape.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim KeyCells As Range
    
        Set KeyCells = Range("e3:e203")
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) _
               Is Nothing Then
    
            If Range("E3").Value = 0 Then
            ActiveSheet.Shapes("P-S1").Visible = False
            Else
            ActiveSheet.Shapes("P-S1").Visible = True
            End If
           
            If Range("E4").Value = 0 Then
            ActiveSheet.Shapes("P-S2").Visible = False
            Else
            ActiveSheet.Shapes("P-S2").Visible = True
            End If
            
        End If
    End Sub
    To further make my life easier is there a way in which I can auto name the shapes to a specific scheme. Liek I sadi I am not picky on names as long as they follow a logical scheme. You will have to bear with me on my ignorance of excel as I am an AutoCAD guy!

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: VBA to Show/Hide multiple shapes based on cell values

    The attached VBA subroutine looks at all the 'Shapes' on the active sheet and displays the results in the 'Immediate Window'. You should be able to adapt this to your situation.

    Sub Shapes()
      Dim Sh As Object
    
      For Each Sh In ActiveSheet.Shapes
        Debug.Print
        Debug.Print Sh.Name
        Debug.Print Sh.Type
        Debug.Print Sh.ID
        Debug.Print Sh.DrawingObject.Caption
        Debug.Print Sh.DrawingObject.OnAction
        Debug.Print Sh.Top
        Debug.Print Sh.Left
        Debug.Print Sh.Height
        Debug.Print Sh.Width
        Debug.Print Sh.Visible
        Debug.Print Sh.ZOrderPosition
      Next Sh
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need help to show/hide rows based on values from a cell
    By concurmgr in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-19-2012, 02:56 PM
  2. How to hide a row based on multiple cell values
    By csh8428 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-08-2012, 10:02 AM
  3. Hide shape based on cell value - For mutliple shapes
    By [Jimmy] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2012, 09:36 AM
  4. Replies: 4
    Last Post: 05-25-2012, 04:16 AM
  5. Hide/Show Shapes on Worksheet??
    By Ken Loomis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1