我想为我的宏引用几张纸

Question

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

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

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

Answer 1

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

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时，它"知道"要对哪个工作表进行更改。

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

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

您还可以删除对两个 Hide 过程的需求，并将它们替换为一个，其中要隐藏的列包含在传递的引用中：

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]值发生变化，则可能需要考虑这一点。

Answer 2

试试这个：

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
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