Loop through worksheet range to show / hide shapes.

wlcdo2 · #1 02-21-2017, 01:57 PM

A range within my worksheet contains values, lets say 1 - 50. If a value is found in any cell then the respectively named shape will be made visible and if the values aren't found, then the shape is hidden. My test code is:

Code:

Private Sub TestMe2()
    Dim rngList As Range
    Dim ws As Worksheet
    Set ws = Worksheets("Workings")
    Set wsRange = ws.Range("O2:W10")
    For Each rngList In wsRange
        If Application.CountIf(wsRange, rngList) > 0 Then
            Me.Shapes(rngList).Visible = True
            
        Else
            Me.Shapes(rngList).Visible = False
            
        End If
    
    Next rngList
  
End Sub

I guess I've declared rngList wrongly? I get Runtime Error-5 Invalid Procedure Call or Argument when it gets to Me.Shapes(rngList).Visible = True. When I step through the code, if I hover my mouse over rngList it does display the value that is found but obviously not the right context / declaration (sorry of my terminology isn't quite correct).

It's probably so simple, but I've drawn a blank (novice @ VBA). Any suggestions?

Many thanks.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy Excel Range and Paste in PowerPoint Shapes	leops	PowerPoint	4	08-31-2017 06:47 AM
How to Hide/Un-hide a worksheet based on cell on another sheet.	easton11	Excel Programming	1	06-02-2015 12:07 PM
copy a range including shapes	Marcus	Excel Programming	8	08-17-2012 01:50 AM
Loop through folder of workbooks and copy range to other workbook	Snvlsfoal	Excel Programming	3	07-29-2011 05:55 AM
PP 2010 .avi file plays during slide show, but causes the slide show to loop to begin	VictorS	PowerPoint	0	10-16-2010 10:23 AM