办公室实用VBA小程序之代码部分(三).docx

办公室实用VBA小程序之代码（2）摘要： 本部分是办公室实用VBA小程序（一）的各项功能的具体代码，现分享给大家。 2、常用功能区代码：Sub一键打印()ProgressBar1.Visible = TrueApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseProgressBar1.Min = 0ProgressBar1.Max = Sheets.CountIf MsgBox(是否已经全部调整好打印格式？, vbYesNo, 警告) = vbYes Then For Each Mysht In Worksheets Mysht.PrintOut ActivePrinter:=Ricoh Aficio MP 2550B PCL 在 Ne00: cot = cot + 1 ProgressBar1.Value = cot Next Application.ScreenUpdating = TrueElse Exit SubEnd IfLabel2.Caption = 打印完毕,共打印 cot 张。cot = 0End Sub显示被隐藏的工作表Sub 显示隐藏表()Application.ScreenUpdating = FalseFor Each Mysht In ActiveWorkbook.Worksheets If Mysht.Visible xlSheetVisible Then Mysht.Visible = xlSheetVisible cot = cot + 1 End IfNext If cot 0 Then Label2.Caption = 已显示 cot 张被隐藏的工作表。 Else Label2.Caption = 该Workbook中无被隐藏的工作表。 End If cot = 0 Application.ScreenUpdating = TrueEnd Sub隐藏非活动工作表Sub 隐藏非活表()For Each Mysht In ActiveWorkbook.Worksheets If Mysht.Name ActiveSheet.Name Then Mysht.Visible = xlSheetHidden cot = cot + 1 End IfNext If cot 0 Then Label2.Caption = 已隐藏 cot 张非活动工作表。 Else Label2.Caption = 该Workbook中已无非活动工作表。 End If cot = 0End SubSub 取消合并单元格()For Each rng In ActiveSheet.UsedRange.Cells If rng.MergeCells = True Then rng.UnMerge End IfNextLabel2.Caption = 所有合并单元格取消完毕！End SubSub 查找清除空格()Dim rng As RangeIf MsgBox(是否在查找后进行替换？, vbYesNo, 提醒您:) = vbYes Then On Error Resume Next For Each rng In ActiveSheet.UsedRange If InStr(1, rng, Chr(32)) Then rng.Replace What:=Chr(32), Replacement:= rng.Interior.Color = vbYellow cot = cot + 1 End If Next Unload UserForm2 If cot = 0 Then UserForm2.Label2.Caption = 定位完毕，本表中无空格！ Else UserForm2.Label2.Caption = 共有 cot 个单元格含有空格，已黄色显示并替换！ End If Else For Each rng In ActiveSheet.UsedRange On Error Resume Next If InStr(1, rng, Chr(32)) Then rng.Interior.Color = vbYellow cot = cot + 1 End If Next Unload UserForm2 If cot = 0 Then UserForm2.Label2.Caption = 定位完毕，本表中无空格！ Else UserForm2.Label2.Caption = 定位完毕，共有 cot 个单元格含有空格,已用黄色标示！ End IfEnd Ifcot = 0End SubOption Expl