Thread: [Solved] Loop through worksheet range to show / hide shapes.
View Single Post
 
Old 02-21-2017, 01:57 PM
wlcdo2 wlcdo2 is offline Windows 7 32bit Office 2016
Novice
  
Join Date: Jun 2016
Posts: 17
wlcdo2 is on a distinguished road
Default Loop through worksheet range to show / hide shapes.
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.
Reply With Quote