Excel VBA经典应用案例(增加按钮和代码)

Excel VBA经典应用案例（增加按钮和代码）

增加按钮和代码

Sub AddSheetAndButton()

Dim NewSheet As Worksheet

Dim NewButton As OLEObject

' Make sure access to the VBProject is allowed

On Error Resume Next

Set x = ActiveWorkbook.VBProject

If Err <> 0 Then

MsgBox "Your security settings do not allow this macro to run.", vbCritical On Error GoTo 0

Exit Sub

End If

' Add the sheet

Set NewSheet = Sheets.Add

' Add a CommandButton

Set NewButton = NewSheet.OLEObjects.Add _

("mandButton.1")

With NewButton

.Left = 4

.Top = 4

.Width = 100

.Height = 24

.Object.Caption = "Return to Sheet1"

End With

' Add the event handler code

Code = "Sub CommandButton1_Click()" & vbCrLf

Code = Code & " On Error Resume Next" & vbCrLf

Code = Code & " Sheets(""Sheet1"").Activate" & vbCrLf

Code = Code & " If Err <> 0 Then" & vbCrLf

Code = Code & " MsgBox ""Cannot activate Sheet1.""" & vbCrLf

Code = Code & " End If" & vbCrLf

Code = Code & "End Sub"

With ActiveWorkbook.VBProject. _

VBComponents().CodeModule

NextLine = .CountOfLines + 1

.InsertLines NextLine, Code

End With

End Sub

来源：Office资源宝库