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.

NoSparks · #2 02-21-2017, 08:50 PM

Hello wlcdo2

I suggest using Option Explicit at the top of your code modules.

Using the same range for the loop and CountIf your If statement will always be >0.

Believe you need to specify the shape type (or name) as well, something like
.Shapes("Rectangle " & rngList).Visible = True

wlcdo2 · #3 02-22-2017, 05:10 PM

Thanks for the guidance NoSparks.
Your suggestion about using the range for the loop and CountIf lead me to a new test by simply searching for a specific string value which worked just fine. I therefore declared a new string (in my example called FindMe based on rngList) and this now works perfectly! My new code is:

Code:

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

        Else
        End If
    Next rngList
  
End Sub

With regards to the shape, I'm working with a simple Oval but I didn't have to specify the type; it worked just fine per my code above.

Thanks again NoSparks for pointing me on the right direction, much appreciated!

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