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

 

[sctlippert2]

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.

I feel like I am trying to reinvent the wheel, and have spent another 4-8 hours trying to circumvent this

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

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

Ok. I just need to understand your code...

To refamiliarize... My sheets("Topic List") shows all the sheets, in which you can click on their hyperlink, taking you to the hidden sheet. Then each sheet has a hyperlink back to "Topic List".

Your code follows or takes you to any hyperlink, but the part that is confusing to me is when you select "Topic Index". I see that the sheet referenced is then hidden. Where in the process would "Topic Index" be activated? after clicking on the return hyperlink? (meaning that the first line takes you to the sheet, the "topic Index" line isn't activated until you return back)

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

 

[sctlippert2]

I feel like I am trying to reinvent the wheel, and have spent another 4-8 hours trying to circumvent this



Ok. I just need to understand your code...

To refamiliarize... My sheets("Topic List") shows all the sheets, in which you can click on their hyperlink, taking you to the hidden sheet. Then each sheet has a hyperlink back to "Topic List".

Your code follows or takes you to any hyperlink, but the part that is confusing to me is when you select "Topic Index". I see that the sheet referenced is then hidden. Where in the process would "Topic Index" be activated? after clicking on the return hyperlink? (meaning that the first line takes you to the sheet, the "topic Index" line isn't activated until you return back)

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

. My program was working quickly with no problems, other than the code leaving me hanging in the newly created private sub. That's when I decided to change the program and make it copy a worksheet with the code already in it... which is now returning the "reference isn't valid" when a new file is created.

I appreciate your input in trying to keep me from potential problems, but I also want to know if there is a way to solve my original question in how to stop being in break mode after inserting the code programatically. I know there must be a way, just a simple code of logic to tell the computer to continue.

 

[sctlippert2]

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

 

[sctlippert2]

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

I still would appreciate an answer to my original question... how do I keep from break mode after inserting code? Is there a "close object" command or something else I'm missing that keeps it from executing the next line of code?

 

[sctlippert2]

I tried your code, by removing the private sub from one of the worksheets and overwriting the code in "This workbook". I couldn't get it to do anything except hide the "Topic Index" sheet that was to remain visible.

 

[RoryA]

To replace all of the code you have posted, the routine in ThisWorkbook would look like:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
   Select Case UCase$(Sh.Name)
      Case "TOPIC INDEX"
         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
      Case Else
         Worksheets("Topic Index").Select
         Sh.Visible = False
   End Select
End Sub

 

[sctlippert2]

I tried your code, by removing the private sub from one of the worksheets and overwriting the code in "This workbook". I couldn't get it to do anything except hide the "Topic Index" sheet that was to remain visible.

I also troubleshot the "reference isn't valid" on the hyperlink. If I unhid the sheet, then went back to the "Topic Index" sheet, removed and redid the hyperlink, it worked fine going forward with no error. I went back through my code to see if there was any place that I hid the file before anchoring the hyperlink.

To replace all of the code you have posted, the routine in ThisWorkbook would look like:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
   Select Case UCase$(Sh.Name)
      Case "TOPIC INDEX"
         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
      Case Else
         Worksheets("Topic Index").Select
         Sh.Visible = False
   End Select
End Sub

That worked perfectly. Thank you. In your original posting, you didn't show any notes in parenthesis about keeping my old code, nor mention of it, so I took that to mean that what you gave me was to be it. Again, thank you for taking the time to clarify. The ThisWorkbook Event is something that I am in need of studying. The more I learn, the more I realize that I haven't scratched the surface. Unfortunately, it is whatever one is working on that leads them to ask the right questions.

What about my original question? What did I do wrong to where my code couldn't proceed?

 

[RoryA]

When I posted my original version, you hadn't mentioned any code in the index sheet. What I posted was merely an example of how to avoid putting the code you had posted into every new sheet.

Changing code in the workbook that is currently running code is always problematic in my experience. You will always have problems stepping through code that does it.

 

[sctlippert2]

My apologies. Thank you for your help