Friday, September 23, 2016

How to Fix Error - creating an index page and referencing cells within those pages?

creating an index page and referencing cells within those pages

So, I've seen several ways to create an index page in Excel by referencing tab names, but I would also like to include information in certain cells on that main index page that are dynamic so that they will update when I update information.
 For example,
I keep client lists in Excel.
 The tab name is the client name, which is what appears on the index page, but, within those tabs, I have a cell (B1) that contains the status of that client and another cell (B2) that tells me when I need to follow up with them.
 I would like both of these cells to be included on the index page so I can quickly look down the list without having to go through every tab.
 Thank you!

Currently I am using this code as a TOC

Sub CreateTOC()
     'Declare all variables
    Dim ws As Worksheet, curws As Worksheet, shtName As String
    Dim nRow As Long, i As Long, N As Long, x As Long, tmpCount As Long
    Dim cLeft, cTop, cHeight, cWidth, cb As Shape, strMsg As String
    Dim cCnt As Long, cAddy As String, cShade As Long
     'Check if a workbook is open or not.
 If no workbook is open, quit.
    If ActiveWorkbook Is Nothing Then
        MsgBox "You must have a workbook open first!", vbInformation, _
                "No Open Book"
        Exit Sub
    End If
     '--------------------------------------------------------
    cShade = 37 '<<== SET BACKGROUND COLOR DESIRED HERE
     '--------------------------------------------------------
     'Turn off events and screen flickering.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    nRow = 4: x = 0
     'Check if sheet exists already; direct where to go if not.
    On Error GoTo hasSheet
    Sheets("TOC").Activate
     'Confirm the desire to overwrite sheet if it exists already.
    If MsgBox("You already have a Table of Contents page." _
    & vbLf & vbLf & _
    "Would you like to overwrite it?", _
    vbYesNo + vbQuestion, "Replace TOC page?") = vbYes Then GoTo createNew
    Exit Sub
hasSheet:
    x = 1
     'Add sheet as the first sheet in the workbook.
    Sheets.Add before:=Sheets(1)
    GoTo hasNew
createNew:
    Sheets("TOC").Delete
    GoTo hasSheet
hasNew:
     'Reset error statment/redirects
    On Error GoTo 0
     'Set chart sheet varible counter
    tmpCount = ActiveWorkbook.Charts.Count
    If tmpCount > 0 Then tmpCount = 1
     'Set a little formatting for the TOC sheet.
    ActiveSheet.Name = "TOC"
    With Sheets("TOC")
        .Cells.Interior.ColorIndex = cShade
        .Rows("4:65536").RowHeight = 16
        .Range("A1").Value = "Designed by VBAX"
        .Range("A1").Font.Bold = False
        .Range("A1").Font.Italic = True
        .Range("A1").Font.Name = "Arial"
        .Range("A1").Font.Size = "8"
        .Range("A2").Value = "Table of Contents"
        .Range("A2").Font.Bold = True
        .Range("A2").Font.Name = "Arial"
        .Range("A2").Font.Size = "24"
        .Range("A4").Select
    End With
     'Set variables for loop/iterations
    N = ActiveWorkbook.Sheets.Count + tmpCount
    If x = 1 Then N = N - 1
    For i = 2 To N
        With Sheets("TOC")
                shtName = Sheets(i).Name
                 'Add a hyperlink to A1 of each sheet.
                .Range("C" & nRow).Hyperlinks.Add _
                Anchor:=.Range("C" & nRow), Address:="#'" & _
                shtName & "'!A1", TextToDisplay:=shtName
                .Range("C" & nRow).HorizontalAlignment = xlLeft
            .Range("B" & nRow).Value = nRow - 2
            nRow = nRow + 1
        End With
continueLoop:
    Next i
     'Perform some last minute formatting.
    With Sheets("TOC")
        .Range("C:C").EntireColumn.AutoFit
        .Range("A4").Activate
    End With
     'Turn events back on.
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    strMsg = vbNewLine & vbNewLine & "Please note: " & _
    "Charts will have hyperlinks associated with an object."
     'Toggle message box for chart existence or not, information only.
    If cCnt = 0 Then strMsg = ""
    MsgBox "Complete!" & strMsg, vbInformation, "Complete!"
End Sub

Keys to the Problem creating an index page and referencing cells within those pages

Download Error Fixer for Free Now

Make Sure that your Computer Meets the System Requirements

You need to double-check your computer's hardware configuration against the following Microsoft requirements. If your computer hardware is not up to par, then make sure you upgrade where necessary before continuing:

Microsoft Windows Requirements :

  • 1 gigahertz (GHz) or faster 32-bit (x86) or 64-bit (x64) processor
  • 1 gigabyte (GB) RAM (32-bit) or 2 GB RAM (64-bit)
  • 16 GB available hard disk space (32-bit) or 20 GB (64-bit)

If your computer meets the minimum requirements above, then continue on…

Another Safe way to Repair the Problem: creating an index page and referencing cells within those pages:

How to Fix creating an index page and referencing cells within those pages with SmartPCFixer?

1. Download SmartPCFixer . Install it on your computer.  Click Scan, and it will perform a scan for your computer. The junk files will be shown in the scan result.

2. After the scan is finished, you can see the errors and problems which need to be fixed.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been fixed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating and print spooler error repair.


Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: Solution to Error: convert a cells text to basic html code,[Solution] consantly getting message "not responding" how can I correct this? My computer freezes.,Troubleshooting:Constant DNS Server problems Error,context menu problem,Troubleshoot:count unique dates Error,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly

No comments:

Post a Comment