When we prepare a report or a dashboard it is easy to identify or analyze reports with a change of color sheet tabs. Analysts generally give the same color to the tabs which are related to same function.
For example if you are preparing a dashboard for all the departments in an organization. All the worksheet tabs related finance can be highlighted in red, HR can be in Blue, etc. so with this report looks good and is easy to understand.
We can change the Worksheet tab colors by setting the Tab.ColorIndex property using Excel VBA.
This Example will show you how to change the Color of Sheet tabs using Excel VBA. In the following Example we are changing the Sheet2 tabs color to Red. Lets have a look
Sub redColorSheetTab() Sheets("Sheet2").Tab.ColorIndex = 3 '3=Red, 4=green, 5=blue,6=yellow etc. End Sub
Here is another help code for programmers to change the color of Excel sheet tab. I have shown another way to achieve this:
'This function changes the tab color of a sheet
Sub ChangeSheetTabColor()
'
'Option 1 - using standard colors vbGreen, vbRed, vbBlack, vbYellow, vbBlue, vbWhite etc.
Sheet1.Tab.Color = vbGreen
'
'Option 2 - using RGB colors
Sheet1.Tab.Color = RGB(117, 117, 117)
'
End Sub
You may have noticed that in Option 2, I have used RGB function to change the sheet tab color. You may read this post to know more about how to get RGB codes of a color.
If you need to change the color of the tab you are currently viewing, you can use the following VBA macro code along with your desired RGB color code:
Sub ChangeTabColor()
'Objective: Change Selected Tab To Specific Color
ActiveSheet.Tab.Color = RGB(25, 25, 25)
End Sub
If you need to write a VBA loop to ensure all worksheet tabs have their color removed, you can use a macro similar to the below code:
Sub ClearAllTabColor()
'PURPOSE: Remove Tab Color from all Sheets
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Tab.Color = xlNone
Next sht
End Sub
To help you practice this code, we have made this code available through practice file. Click on the below link to download the practice file.
In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location
Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.
To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.
How to use VBA to open Workbook in Excel? There are few VBA codes which are commonly used by every developer. One of them is giving an option to user to browse a file. Below is a…
VBA Code to Read Outlook Emails Reading emails from Outlook and capture them in Excel file is very common activity being performed in office environment. Doing this activity manually every time is quite boring and…
Dummy Data Generator is an MS Excel based tool which has capability of generating 45 types of data which includes numbers, text, date, time, Memo (long text), Boolean etc.