2024年3月13日发(作者：)

宏文件集

▲打开全部隐藏工作表

Sub 打开全部隐藏工作表()

Dim i As Integer

For i = 1 To

Sheets(i).Visible = True

Next i

End Sub

▲循环宏

Sub 循环()

AAA = Range("C2")

Dim i As Long

Dim times As Long

times = AAA

'times代表循环次数，执行前把times赋值即可(不可小于1，不可大于2147483647)

For i = 1 To times

Call 过滤一行

返回

返回

If Range("完成标志") = "完成" Then Exit For '如果名为'完成标志'的命名单元的值等于'完成',则退出循环,如果一开始就等于'完成

'If Sheets("传送参数").Range("A" & i).Text = "完成" Then Exit For '如果某列出现"完成"内容则退出循环

Next i

End Sub

▲录制宏时调用“停止录制”工具栏

Sub 录制宏时调用停止录制工具栏()

dBars("Stop Recording").Visible = True

End Sub

▲高级筛选5列不重复数据至指定表

Sub 高级筛选5列不重复数据至Sheet2()

Sheets("Sheet2").Range("A1:E65536") = "" '清除Sheet2的A:D列

Range("A1:E65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=( _

"A1"), Unique:=True

s("A:E").Sort Key1:=("A2"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _

:=xlPinYin

End Sub

▲双击单元执行宏(工作表代码)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Range("$A$1") = "关闭" Then Exit Sub

Select Case s

Case "$A$4"

Call 宏1

Cancel = True

Case "$B$4"

Call 宏2

Cancel = True

Case "$C$4"

Call 宏3

Cancel = True

Case "$E$4"

Call 宏4

Cancel = True

End Select

End Sub

▲双击指定区域单元执行宏(工作表代码)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Range("$A$1") = "关闭" Then Exit Sub

If Not ect(Target, Range("A4:A9", "C4:C9")) Is Nothing Then Call 打开隐藏表

End Sub

▲进入单元执行宏(工作表代码)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

返回

返回

返回

返回

返回

1/37

'以单元格进入代替按钮对象调用宏

If Range("$A$1") = "关闭" Then Exit Sub

Select Case s

Case "$A$5" '单元地址(s),或命名单元名字()

Call 宏1

Case "$B$5"

Call 宏2

Case "$C$5"

Call 宏3

End Select

End Sub

▲进入指定区域单元执行宏(工作表代码)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("$A$1") = "关闭" Then Exit Sub

If Not ect(Target, Range("A4:A9","C4:C9")) Is Nothing Then Call 打开隐藏表

End Sub

▲在多个宏中依次循环执行一个（控件按钮代码）

Private Sub CommandButton1_Click()

Static RunMacro As Integer

Select Case RunMacro

Case 0

宏1

RunMacro = 1

Case 1

宏2

RunMacro = 2

Case 2

宏3

RunMacro = 0

End Select

End Sub

▲在两个宏中依次循环执行一个并相应修改按钮名称（控件按钮代码）

Private Sub CommandButton1_Click()

With CommandButton1

If .Caption = "保护工作表" Then

Call 保护工作表

.Caption = "取消工作表保护"

Exit Sub

End If

If .Caption = "取消工作表保护" Then

Call 取消工作表保护

.Caption = "保护工作表"

Exit Sub

End If

End With

End Sub

▲在三个宏中依次循环执行一个并相应修改按钮名称（控件按钮代码）

Option Explicit

Private Sub CommandButton1_Click()

With CommandButton1

If .Caption = "宏1" Then

Call 宏1

.Caption = "宏2"

Exit Sub

End If

If .Caption = "宏2" Then

Call 宏2

.Caption = "宏3"

Exit Sub

End If

If .Caption = "宏3" Then

Call 宏3

.Caption = "宏1"

返回

返回

返回

返回

2/37