Consulting

Results 1 to 8 of 8

Thread: Table Of Contents - Help required to customize the code

  1. #1
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location

    Table Of Contents - Help required to customize the code

    Hi, I'm a newbie in VBA.
    I request your help in the attached code downloaded from the internet (workbook attached). The code creates a new sheet in front of all the sheets with a list of sheets with hyperlink. I need help in the following-
    (1) Adding the values also from cell A1 in all the sheets against the sheet name. (Highlighted in yellow).
    (2) And a link back to the Contents (TOC) sheet in all other sheets in cell N1
    Thanks in advance
    Attached Files Attached Files

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    In attached



                '----------------------------------------------------
                Application.EnableEvents = False
                .Cells(x + 2, 4).Value = sht.Range("A1").Value
                On Error Resume Next
                sht.Range("N1").Hyperlinks.Delete
                On Error GoTo 0
                sht.Range("N1").ClearContents
                sht.Range("N1").Hyperlinks.Add Anchor:=sht.Range("N1"), Address:="", SubAddress:="Contents!A1", TextToDisplay:="Go To TOC"
                Application.EnableEvents = True
                '----------------------------------------------------
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Thanks again for the continued support

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Lists all sheets. Sorts Listing alphabetically. Activates selected sheet. Always stay next to active sheet.

    Copy TOC sheet to any workbook. Use Rows 1 to 3 for notes/info/instructions
    Attached Files Attached Files
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    This is awesome Mr. Sam

  6. #6
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Quote Originally Posted by SamT View Post
    Lists all sheets. Sorts Listing alphabetically. Activates selected sheet. Always stay next to active sheet.

    Copy TOC sheet to any workbook. Use Rows 1 to 3 for notes/info/instructions
    Hi,
    I have made few modifications in the code as per my requirement. Request your help in the following areas where my knowledge is limited as a beginner to VBA.
    (1) I have added serial number before the sheet names. But my code is skipping 1 number where the TOC sheet is moving
    TOC.Cells(r, 2).Value = i
    (2) Can i move to the sheet even if i click on column D
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Application.ScreenUpdating = False
    
    
        ''''Check criteria for running this sub
            'Is only one Cell selected
          If Target.Count <> 1 Then Exit Sub
            'Is there a List
          If LastRow < 3 Then Exit Sub
            'Is the selection in the List
          If Intersect(Target, Range("C3:C" & CStr(LastRow))) Is Nothing Then Exit Sub
               
        ''''Move the Index Sheet, Activate the chosen Sheet, and Select "A1" _
            on the chosen Sheet.
          TOC.Move Before:=Sheets(Target.Value)
          Sheets(Target.Value).Activate
          ActiveSheet.Range("A1").Select
         
        Application.ScreenUpdating = True
        End Sub
    Thanks in advance
    Unable to attach the file
    Last edited by anish.ms; 11-28-2020 at 05:16 AM.

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I had done it a little differently.

    I used an add in to insert a TOC worksheet into the active workbook. It doesn't refresh when activated like SamT's (although it could)

    That way I didn't have to worry about copying a TOC worksheet into every workbook

    On the QAT i just added a call to the add in

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location
    Dear Paul, thanks for your response
    I have added the code I shared initially with your suggested codes in my personal.xlsb workbook to call it in other workbooks when required.
    I found SamT's code for TOC sheet to move along with interesting and thought of using it in one of the workbook.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •