VBA code to change tab color in excel workbook!!!

Tabs are basic elements of each workbook in excel application. In excel, tabs can be colored with different colors. Using color coding to each tab, user can specify their use based on the data containing each tab. However, color can be applied in excel tab using excel macro. let's see how tabs can be colored using excel macro.

  

Here is the VBA code to change color for each tab in a workbook: 

Option Explicit

Sub Change_Tab_Color()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com

'Declare Variable
Dim i As Integer
Dim sht As Worksheet
    
'For loop to change color of each tab
  For i = 1 To ActiveWorkbook.Sheets.Count
    Sheets(i).Select
    Set sht = ActiveSheet
    'Change color index values 21 to your required color. refer color index values from table given at end of this article
    sht.Tab.ColorIndex = 21
  Next i

'Activate Sheet1
Sheets(1).Select

MsgBox "Color changed for all tabs.", vbInformation, "Change Tab Color"
End Sub

Refer below table for color index value for few colors:

VBA code list of all tables in a workbook by excel macro!!!

Table is a root element of any data processing application. It is used to stored data in the form of row and column. In excel, it can be used as one of data visualization. So, table is used in a workbook frequently. As a result, various table will be created in a single workbook. However, To make a list of all tables will be a manual task in excel. It can be automate by using VBA code as follows.

  
Here is the VBA code to list all tables in a workbook: 


Option Explicit

Sub List_All_Table()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim objTable As ListObject
Dim ws As Worksheet
Dim strTbl As String
Dim i As Integer

strTbl = ""
i = 1

'For loop through each worksheets
    For Each ws In ActiveWorkbook.Worksheets

'Loop to select each table in a worksheet
        For Each objTable In ws.ListObjects
           
            'Pass table names to variable in order to show in message. comment or delete this if not required
            strTbl = strTbl & i & ".  " & objTable.Name & vbNewLine
            
            'Uncomment below line to get table list in sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = objTable.Name
            i = i + 1
            
        Next objTable
    Next ws
    
MsgBox "List of Tables as Below: " & vbNewLine & vbNewLine & strTbl, vbInformation, "Table List"

End Sub

VBA code to list all hidden sheet in a workbook!!!

Excel allows user to hide worksheets which are not required or wanted it to be hidden. As a result, there will be numerous worksheets to be hidden in a single workbook. Sometime, we need to figure out which hidden sheets we want and which should be deleted from the workbook. In such scenario, list down all hidden sheets will be a quite manual task. Obviously, it can consume couple of hours if there are lot of workbooks contains hundred of worksheets in each workbook. So, this task can be automate with few line of excel macro.

  
Here is the VBA code to list all hidden sheet in the workbook: 

Option Explicit

Sub List_of_Hidden_Worksheet()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim strSht As String
Dim sht As Worksheet
Dim i As Integer

strSht = ""

i = 1

'For loop to check each worksheets for hidden property
    For Each sht In ActiveWorkbook.Worksheets
        
        If Not Worksheets(sht.Name).Visible Then
            
            'Pass names of hidden sheets into variable to show in message box. comment or delete this if not required
            strSht = strSht & i & ".  " & sht.Name & vbNewLine
            
            'Uncomment below line to get hidden Sheets list in Sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = sht.Name
            
            i = i + 1
        End If
    Next sht
    
MsgBox "List of hidden sheets as Below: " & vbNewLine & vbNewLine & strSht, vbInformation, "Hidden Worksheets"

End Sub

VBA code to list all pivot tables in a workbook!!!

Pivot table is one of the powerful data interpretation tool in excel. Which is used to summarise large amount of data at a glance. It is mostly used in various reporting task. Because of this pivots are extensively used in the excel reports and dashboards. Thus, there will be various pivots tables can be used in excel workbook. In order to validate pivot tables availability in a workbook someone needs list of all pivot tables. Obviously, to collect list of all pivot tables again result into a manual task which can be time consuming. But, this can be automate by a excel macro.

  
Here is the excel macro which will give us list of all pivot in a workbook: 

Option Explicit

Sub List_All_Pivots()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim pt As PivotTable
Dim ws As Worksheet
Dim strPvt As String
Dim i As Integer

strPvt = ""
i = 1

'For loop through each worksheets
    For Each ws In ActiveWorkbook.Worksheets
        Worksheets(ws.Name).Select

'Loop to select each pivot table for a single worksheet
        For Each pt In ws.PivotTables
           
            'Pass Pivot names to variable in order to show in message. comment or delete this if not required
            strPvt = strPvt & i & ".  " & pt.Name & vbNewLine
            
            'Uncomment below line to get pivot table list in sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = pt.Name
            i = i + 1
            
        Next pt
    Next ws
    
MsgBox "List of Pivot Tables as Below: " & vbNewLine & vbNewLine & strPvt, vbInformation, "Pivot List"

End Sub