时间戳

首页 > 解决方案 > 隐藏工作表上的所有对象

excel - 隐藏工作表上的所有对象

问题描述

在我的工作簿中，我有一个宏来隐藏工作表上的所有对象。

它一直工作到今天。

Sub Hiding()
    Dim sObject As Object
    Dim a As Integer, v As Integer

'Count all visible Worksheets
    v = 0
    For Each s In ActiveWorkbook.Worksheets
        If s.Visible = True Then
            v = v + 1
        End If
    Next s

'Hide all Objects
    For a = 2 To v
        For Each sObject In Worksheets(a).Shapes
            sObject.Visible = False
        Next
    Next
End Sub

我也试过Dim sObject As Shape

标签： excelvba

解决方案

您使用 v=v+1 循环创建的 Visible Worksheets.Count 应该是可见工作表序号索引的数组，而不是可见工作表的总数。当您循环通过 a 时， a 应该检索数组中的值，而不仅仅是1、2、3等工作表索引号。

dim w as long, v as long, a as long, vws as variant

'prep vars
v = 0
redim vws(1 to ActiveWorkbook.Worksheets.Count)

'IDENTIFY all visible Worksheets
For w=2 to ActiveWorkbook.Worksheets.count    'note I started at the 2nd worksheet here
    If ActiveWorkbook.Worksheets(w).Visible Then
        v = v + 1
        vws(v) = w
    End If
Next s

redim preserve vws(1 to v)

'Hide all Objects
For a = lbound(vws) To ubound(vws)
    For Each sObject In ActiveWorkbook.Worksheets(vws(a)).Shapes
        sObject.Visible = False
    Next
Next

推荐阅读