我想为我的宏引用几张纸 [英] I want to reference several sheets for my macro

问题描述

我正在尝试为我的宏引用工作表1，工作表2，工作表3.目前，我引用了工作表1，但我不知道如何引用多个工作表.我希望在所有工作表中都检查单元格w6.提前谢谢了！ :)

I am trying to reference sheet 1, sheet 2 sheet 3 for my macro. At the moment, I referenced sheet 1 but I don't know how to reference multiple sheets. I hope that in all sheets, cell w6 is checked. Many thanks in advance! :)

隐藏F宏用于在单元格w6不为空时隐藏图形名称"F"并显示图形"FG". Hide FG宏用于隐藏名为"FG"的图形，并在单元格w6为空时显示图形"F".

Hide F macro is to hide a graph names "F" and show graph "FG" when cell w6 is not empty. Hide FG macro is to hide a graph named "FG" and show graph "F" when cell w6 is empty.

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
    With ws
        If .Range("W6").Value = 0 Then
            HideFG
        Else
            HideF
        End If
    End With
Next

End Sub


Sub HideF()
'
' HideF Macro
'

'
    For i = 1 To ActiveSheet.Shapes.Count
    ActiveSheet.Shapes(i).Visible = msoTrue
    Next i
    ActiveSheet.Shapes.Range(Array("F")).Visible = msoFalse
    Application.CommandBars("Selection").Visible = False
End Sub


Sub HideFG()
'
' HideFG Macro
'

'
    For i = 1 To ActiveSheet.Shapes.Count
    ActiveSheet.Shapes(i).Visible = msoTrue
    Next i
    ActiveSheet.Shapes.Range(Array("FG")).Visible = msoFalse
    Application.CommandBars("Selection").Visible = False

End Sub

推荐答案

这应该可以满足您的需求:

This should do what you need:

    Private Sub Workbook_Open()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Sheets
        With ws
            If .Range("W6").Value = 0 Then
                HideFG ws
            Else
                HideF ws
            End If
        End With
    Next

    End Sub


    Sub HideF(wsht As Worksheet)

    For i = 1 To wsht.Shapes.Count
        wsht.Shapes(i).Visible = msoTrue
    Next i
    wsht.Shapes.Range(Array("F")).Visible = msoFalse
    Application.CommandBars("Selection").Visible = False

End Sub


Sub HideFG(wsht As Worksheet)


    For i = 1 To wsht.Shapes.Count
        wsht.Shapes(i).Visible = msoTrue
    Next i
    wsht.Shapes.Range(Array("FG")).Visible = msoFalse
    Application.CommandBars("Selection").Visible = False

End Sub

现在，该循环不再只是调用HideFG，而是使用对该循环正在测试的工作表的引用对其进行调用.因此，当调用HideFG时，它知道"要进行更改的工作表.

Instead of just calling HideFG, the loop now calls it with a reference to the sheet that the loop is testing. So when HideFG is called, it 'knows' which sheet to make the changes to.

请注意，我已经更改了您尝试隐藏列的行.不应将Hidden设置为False，而应将Hidden设置为True.

Notice that I've changed the lines where you attempt to hide the columns. Instead of setting Visible to False, you should set Hidden to True.

-------------------------------------------- ------------------------------------

您还可以不需要两个隐藏"过程，而将它们替换为一个，其中要隐藏的列包含在所传递的引用中:

You could also remove the need for two Hide procedures and replace them with one, where the column(s) to hide are included in the reference passed:

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
    With ws
        If .Range("W6").Value = 0 Then
            HideColumns ws.Columns("F:G")
        Else
            HideColumns ws.Columns("F")
        End If
    End With
Next

End Sub


Sub HideColumns(rng As Range)

    For i = 1 To rng.Parent.Shapes.Count
        rng.Parent.Shapes(i).Visible = msoTrue
    Next i
    rng.Hidden = msoTrue
    Application.CommandBars("Selection").Visible = False

End Sub

最后的想法-大概[W6]可能会改变.当前，如果有的话，此代码中没有任何内容可以取消隐藏列.如果可以进行更改以导致[W6]的值发生更改，则可能需要考虑这一点.

A final thought - presumably [W6] can change. Currently there is nothing in this code to unhide the columns if it does. You may need to consider this if changes can be made that result in the value of [W6] changing.

这篇关于我想为我的宏引用几张纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！