none
VBA Editor - Insert Code into each Sheet RRS feed

  • Question

  • Hi all,

    does anyone have any idea of how I may be able to copy some code onto 100+ worksheets.

    In the VBA Editor  > Microsoft Excel Objects  - - You can click on each worksheet and add private code.

    Now I have the same code snippet that I need to add to 100+ sheets, and well it seems like i will have to copy and paste, then later delete and copy and paste again

    Any ideas?

    cheers
    Wednesday, June 22, 2016 9:33 PM

Answers

  • Maybe it is necessary to create an exception for TOC worksheet:

    Option Explicit
    
    Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
        Dim wsTOC As Worksheet
        
        If ActiveSheet Is wsTOC Then Exit Sub
        Set wsTOC = ThisWorkbook.Worksheets("TOC")
        wsTOC.Activate
        Sh.Visible = xlSheetHidden
    End Sub


    http://www.ambienteoffice.com.br - http://www.clarian.com.br



    Thursday, June 23, 2016 4:06 PM

All replies

  • There is a way to do that, but I believe that you can do a better programming practice.

    Could you paste the snippet code here?



    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Wednesday, June 22, 2016 9:54 PM
  • Hi Felipe,

    I wanted to paste


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


    into each worksheet, so I may make a table of content  - link to each worksheet.

    100 worksheets is a lot to manage.

    Later I would like to also paste a checkbox  code there, or shape code to help manage each worksheet.

    I am a vba newbie - but I do understand how to run macros

    thank you

    sam
    Wednesday, June 22, 2016 10:07 PM
  • Paste this code in your ThisWorkbook class:

    Option Explicit
    
    Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
        Application.Goto ThisWorkbook.Worksheets("TOC")
        Sh.Visible = xlSheetHidden
    End Sub
    


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, June 23, 2016 12:18 PM
  • Hi Felipe,

    thank you for the code. I pasted it >  ThisWorkbook

    Sadly I am not sure if i did it right Here is the code that goes inside  TOC Sheet

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

    End Sub

    Basically

    The TOC worksheet is like a menu -  it has all the hyperlinks to all the various worksheets.

    I click on a hyperlink   - it goes to that work sheet. 

    On each worksheet I have a hyperlink back to the TOC - When I click on that it makes the active worksheet hidden.

    Before I was copying and pasting the same code on to each worksheet.

    For some reason when i pasted the new code into ThisWorkbook -  it does not hide the worksheet

    I hope this makes sense

    thank you

    Sam

    Thursday, June 23, 2016 3:23 PM
  • Maybe it is necessary to create an exception for TOC worksheet:

    Option Explicit
    
    Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
        Dim wsTOC As Worksheet
        
        If ActiveSheet Is wsTOC Then Exit Sub
        Set wsTOC = ThisWorkbook.Worksheets("TOC")
        wsTOC.Activate
        Sh.Visible = xlSheetHidden
    End Sub


    http://www.ambienteoffice.com.br - http://www.clarian.com.br



    Thursday, June 23, 2016 4:06 PM
  • Thanks Felipe,

    I will be able to take it from there.

    sam

    Thursday, June 23, 2016 4:35 PM