Question

Excel VBA 如何向新添加的sheet中添加worksheet_change事件呢？

ShtCodeName = ThisWorkbook.Worksheets("AAAA").CodeName

With ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule
.InsertLines 1, "Private Sub Worksheet_Changes()"
.InsertLines 2, "msgbox ""生成事件成功"""
.InsertLines 3, "'这是一个注释示例"
.InsertLines 5, "End Sub"
End With

上面的添加有什么问题？ sheet（"AAAA"）是每次worksheet_open事件自动删除已有并且生产的新的sheet("AAAA"),如何在这个新sheet（"AAAA"）中用VBA生成WorkSheet_Change事件？

Answer 1

改成下面这样就行了。

ShtCodeName = ThisWorkbook.Worksheets("AAAA").CodeName
With ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule
.InsertLines 1, "Private Sub Worksheet_Change(ByVal Target As Range)"
.InsertLines 2, "msgbox ""生成事件成功"""
.InsertLines 3, "'这是一个注释示例"
.InsertLines 5, "End Sub"
End With

试试吧。

追问

我是在一个Workbook_Open事件中调用这个sub的，而Open事件正是删除AAAA这个sheet，然后添加新的sheet("AAAA")的，你这个单独执行可以使用，但是放到Open事件中提示With ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule
这里“下标越界”，应该是Sheet AAAA没有完全添加成功？这个如何处理啊？

追答

Private Sub Workbook_Open()
Dim Flag As Boolean
Dim i As Integer
On Error Resume Next
For i = 1 To ActiveWorkbook.Worksheets.Count
If ActiveWorkbook.Worksheets.Item(i).Name = "AAAA" Then
Flag = True
End If
Next
If Flag Then
Sheets("AAAA").Select
ActiveWindow.SelectedSheets.Delete
Flag = False
End If
If Not Flag Then
Sheets.Add.Name = "AAAA"
End If
For i = 1 To ActiveWorkbook.Worksheets.Count
If ActiveWorkbook.Worksheets.Item(i).Name = "AAAA" Then
ShtCodeName = ThisWorkbook.Worksheets("AAAA").CodeName
With ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule
.InsertLines 1, "first"
End With
End If
Next
For i = 1 To ActiveWorkbook.Worksheets.Count
If ActiveWorkbook.Worksheets.Item(i).Name = "AAAA" Then
ShtCodeName = ThisWorkbook.Worksheets("AAAA").CodeName
With ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule
.InsertLines 1, "Private Sub Worksheet_Change(ByVal Target As Range)"
.InsertLines 2, "msgbox ""生成事件成功"""
.InsertLines 3, "'这是一个注释示例"
.InsertLines 5, "End Sub"
End With
End If
Next
End Sub
以上代码生成事件执行了两次，是为了解决Excel刚建立表后添加事件处理程序失败的问题。你再试试。

追问

问题是加上你的code,还是提示下标越界,停在first上面那句，ShtCodeName=“”
在代码窗口可以单独执行成功，但是每次重新打开Excel 触发Open事件就报错

追答

我已经在Excel 2007中调试通过。不知你的Excel版本是什么，要不留下邮箱，我把示例文件发给你研究。

Answer 2

换种思路行不？

Private Sub Workbook_Open()
For i = 1 To Worksheets.Count
If Sheets(i).Name = "AAAA" Then Sheets(i).Cells.Delete: Exit Sub
Next
Worksheets.Add after:=Worksheets(Sheets.Count)
Worksheets(Sheets.Count).Name = "AAAA"
End Sub

Answer 3

= Val(Target) * 3 End If End Sub 说明：只要把Worksheet_Change 改为 Worksheet_SelectionChange 即可 1楼，正确，学习了