Create new worksheet with Event Handler code with vba.

[antmud]

Hi,

I'm trying to create a new sheet with event handling code using vba. I've read and tried heaps of options but none work for me.

My code is:
Sub Macro3()
Dim code As String
Dim NextLine As Integer

Sheets.Add.Name = "Test"
code = "Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)" & vbCrLf & vbCrLf
code = code & "Dim j As Integer" & vbCrLf
code = code & "j = Target.Range.Row" & vbCrLf
code = code & "MsgBox ""row is "" & j" & vbCrLf
code = code & "Call NowTryThis(j)"
code = code & "end sub"

With ActiveWorkbook.VBProject.VBComponents("Test").CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, code
End With
End Sub

I've also tried:


Dim code As String
Dim NextLine As Integer
Sheets.Add.Name = "Test"

With ActiveWorkbook.VBProject.VBComponents("Test").CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, "Text goes here"
End With

It's falling over at the line : With ActiveWorkbook.VBProject.VBComponents("Test").CodeModule
The error message is run-time error 9
Subscript out of range.

I've looked at heaps of examples that don't seem to differ from mine. What am I doing wrong?

Cheers, Ant.

ps How do I get my code to appear in a neat box like other posts I've read?

 

[pgc01]

Hi

A sheet has 2 names

- the Name that the user can see in the tabs and can change at will

- the CodeName, that is a name automatically created by excel when you create the sheet and that the user does not see nor can change (unless it's a power user )

VbComponents() expects the codename, not the name. You create the worksheet and change its name to "Test" but the codename remains the same, for ex. "Sheet8" and that's the one you must use with VbComponents()

Try this example that add a worksheet and writes something to its module:

Sub test()
Dim sCodeName As String

Worksheets.Add.Name = "Test"

' Get the codename of the newly created sheet
sCodeName = Worksheets("Test").CodeName

' Write some text in the module
With ActiveWorkbook.VBProject.VBComponents(sCodeName).CodeModule
    If .CountOfLines > 0 Then .DeleteLines (1)
    .AddFromString ("Rem This is a test")
End With
End Sub

For posting code click on the Go Advanced button and then on the # button in the Editor

 

[antmud]

Thank you, that has given me renewed hope.

I've tried your code and also updated my code. I'm now getting a message:

"Can't enter break mode at this time".

In your code it comes at .deletelines (1)

In my code it comes at .InsertLines NextLine, code

Apart from that the new worksheet is being created and code is appearing in the right place.

Hoping you can help me on this too.

Thanks, Ant.

 

[pgc01]

Hi Ant

This is probably because you tried to execute this code in single step, which you cannot do with this type of code.

Try:

Create a new workbook and execute my code (Run, not Single Step).




Remark: I think it's interesting this possibility of writing code using vba but I don't think I'd use it for a real solution. Unless this is really a very special case I don't see why you'd do it.

 

[Norie]

Why not use the SheetFollowHyperlink workbook level event?

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

End Sub

This is triggered clicked whenever a hyperlink on a worksheet is clicked.

The hyperlink and the sheet it's on are passed to the sub.

 

[antmud]

Thanks for your help PGC, I'm well on my way to the next step now.

My macro creates a number of sheets for which I wanted to create hyperlinks to further macros that would be run as required. I've since found out that there is a hyperlink as a workbook event, so I'm now putting the code into the workbook code (as Norie has also suggested).

Whenever I'm tackling a macro I tend to think of the saying " One small step for man ... "

Thanks again for your help.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

 

[curious K]

Thanks PGC,

Its also help me.