Worksheet in VB COMPONENT

A

Al

I'm using the following code to add an event procedure to a worksheets code.
Rather than having to refer to the worksheet
as "Sheet1", i would like to refer to the worksheet as its actual
name, which is 10. How could I do so? Thanks in advance. See
the following code where "SHEET1" is used.

Sub addactivate()
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("SHEET1").CodeModule
StartLine = .CreateEventProc("Activate", "Worksheet") + 1
.InsertLines StartLine, _
"If lastAddress <> """" Then Range(lastAddress).Select"
End With

End Sub
 
B

Bob Phillips

Do you mean

With
ActiveWorkbook.VBProject.VBComponents(Worksheets("10").CodeName).CodeModule
 
T

Tom Ogilvy

Sub addactivate()
Dim StartLine As Long
Dim s As String
s = ActiveworkBook.Worksheets("10").Codename
With ActiveWorkbook.VBProject.VBComponents(s).CodeModule
StartLine = .CreateEventProc("Activate", "Worksheet") + 1
.InsertLines StartLine, _
"If lastAddress <> """" Then Range(lastAddress).Select"
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add VBA code to multiple WorkSheets 0
code to delete vba code 1
Subscript out of range 7
VB editor opens by magic 4
Add Procedure To Module Via VBA 2
Xl 2007 v 2003 Re Populate ThisWorkbook via VBA 2
Creating An Event Procedure 4
Excel crashes and restarts when accessing newly created worksheet 2

Top