Programmatically adding a Private Sub to a worksheet

[sctlippert2]

I have adapted a macro to create a PrivateSub into a worksheet using "With"... insert lines... and "End With". The macro automatically adds the "PrivateSub" title and the "End Sub", so all I had to do was add the middle lines. This macro is used to create a hyperlink on each newly created worksheet. After the new sheet is created, the macro is called, and after PrivateSub insertion there are other lines of code following. However, after the last line of code is inserted, just before the "End With", an alert pops up "Can't enter break mode at this time". Whether I click "Continue" or "End", I end up out of my loop and stuck in the new worksheet where the PrivateSub was just inserted. I figure that the conflict might be the "With"/"End With", needing another line of code, or some other way to write the code to insert each line like you would in a module (including "PrivateSub" & "End Sub"). I am presently readapting my code to use a template worksheet with the code already in the sheet, and then copy and rename each time. But, it is annoying to me that I cannot fix the code and make it work... hoping someone can advise. Below is the macro being used:

'INSERT PRIVATE SUB INTO EACH TOPIC WORKSHEET
Sub InsertPrivateSubInTopic()
Dim VBP As Object, strProcName As String

Set VBP = ThisWorkbook.VBProject
strProcName = "WorkSheet_FollowHyperlink"

With ThisWorkbook.VBProject.VBComponents(ThisWorkbook.Worksheets(ActiveSheet.Name).CodeName).CodeModule
.insertlines Line:=.CreateEventProc("FollowHyperlink", "Worksheet") + 1, String:= _
"Worksheets(""Topic Index"").Select" & vbCrLf & _
"Target.Parent.Worksheet.Visible = False"
End With
End Sub

 

[RoryA]

Why do you need to do this rather than just using a workbook-level equivalent event?

 

[sctlippert2]

Why do you need to do this rather than just using a workbook-level equivalent event?

My education is limited, as I am self-taught, but I have been working on macros for about 10-15 years. I am not familiar with this... how does it work?

Well, I just looked online, and found info for pasting code when a new sheet is created...
url: Excel VBA: Workbook Level Events and Their Uses - ExcelDemy
This would work great, but what is the year cut-off for Excel versions? 2016 and above?

 

[sctlippert2]

My education is limited, as I am self-taught, but I have been working on macros for about 10-15 years. I am not familiar with this... how does it work?

Well, I just looked online, and found info for pasting code when a new sheet is created...
url: Excel VBA: Workbook Level Events and Their Uses - ExcelDemy
This would work great, but what is the year cut-off for Excel versions? 2016 and above?

So far, I have not found any examples on how to add the new sheet event to the workbook.

 

[sctlippert2]

So far, I have not found any examples on how to add the new sheet event to the workbook

So far, I have not found any examples on how to add the new sheet event to the workbook.

Am I on the right track? In VBA, click on workbook, this pulled up the "general" section.
Then the first two lines look for a new sheet. I have no clue as to what to do after that or if this is completely wrong.


Private Sub Workbook_NewSheet(ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then

'add PrivateSub to worksheet (somehow insert the code into the sheet

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Worksheets("Topic Index").Select
Target.Parent.Worksheet.Visible = False
End Sub

End If
End Sub

 

[RoryA]

In my experience, it is very rarely necessary, or a good idea, to have code that writes code, especially if it's in the same workbook. It is also rarely a good idea to put the same code into multiple worksheets. There is a Workbook level event for every worksheet event, that will respond for every worksheet. For example, you could put this into the ThisWorkbook module of your workbook:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Worksheets("Topic Index").Select
Sh.Visible = False
End Sub

and it will work for all worksheets in the workbook.

 

[sctlippert2]

In my experience, it is very rarely necessary, or a good idea, to have code that writes code, especially if it's in the same workbook. It is also rarely a good idea to put the same code into multiple worksheets. There is a Workbook level event for every worksheet event, that will respond for every worksheet. For example, you could put this into the ThisWorkbook module of your workbook:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Worksheets("Topic Index").Select
Sh.Visible = False
End Sub

and it will work for all worksheets in the workbook.

I have 3 visible sheets in the workbook. This is actually a bible topic search where you enter a word and if that word shows up in any verses, they are created in that name or topic worksheet. The worksheets are then hidden and also listed on the Topic Index sheet which is visible. Each worksheet listing is also a hyperlink to that sheet. I don't see how to adapt the above code to work for all the hyperlinks on "Topic Index" by only pasting in ThisWorkbook, and also only make sheets 7 to sheets.count hidden.

 

[RoryA]

If you need to exclude a sheet from triggering that code, you can test Sh.name to see how the code should respond. For example:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Select case ucase$(sh.name)
Case "TOPIC INDEX"
‘ do nothing
Case else
Worksheets("Topic Index").Select
Sh.Visible = False
End select
End Sub

 

[sctlippert2]

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Select case ucase$(sh.name) Case "TOPIC INDEX" ‘ do nothing Case else Worksheets("Topic Index").Select Sh.Visible = False End select End Sub

One thing I didn't mention before is that I already have the following private sub in sheets("TOPIC SHEET"):

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
LinkTo = Target.SubAddress
TopicSheet = InStr(1, LinkTo, "!")
If TopicSheet > 0 Then
MySheet = Left(LinkTo, TopicSheet - 1)
Worksheets(MySheet).Visible = True
Worksheets(MySheet).Select
MyAddr = Mid(LinkTo, TopicSheet + 1)
Worksheets(MySheet).Range(MyAddr).Select
End If
End Sub

This, along with the other private sub to go into each sheet was working successfully. I was trying to understand your macro before implementing. Are you saying that your macro will accomplish the same thing that I already have, without each sheet having a private sub?

Also, I am now getting a "Reference Isn't Valid" when clicking on the hyperlink of a newly created sheet . After clicking OK, it jumps right to the sheet.

 

[RoryA]

Yes, the code I posted will respond to clicking a hyperlink on any sheet in the workbook.