Solved

Get the Tab color in Excel 2007 in C#

Posted on 2012-04-12
7
1,134 Views
Last Modified: 2012-06-27
I found out how to set a tab's color using c# in Excel 2007:

LastSheet.Tab.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.ForestGreen);

This does indeed set the tab color to ForestGreen (although as a side note, I would like to be able to set it based on RGB or ARGB).

However, now what I want to do is retrieve the current(initial) color of the tab into a variable. I tried this:

System.Drawing.Color currentColor;
currentColor = (System.Drawing.Color)LastSheet.Tab.Color;  

Although the compiler was happy with this, at runtime I got an error:
"Specified cast is not valid."

I suspect I need to use the ColorTranslator method somehow, but I tried some different configurations and none worked.

Any ideas?
0
Comment
Question by:bassman592
  • 4
  • 3
7 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I don't think you need to cast it.  LastSheet.Tab.Color is long, so just make currentColor long and make a direct assignment to currentColor:

'After having dimensions currentColor to Long... The following should work:
currentColor = LastSheet.Tab.Color;

Open in new window


Its been 25 years since I programmed in C, so rather than building a C# example, here's a working VBA.NET module that works with no problems:

Module Module1
    Sub doExcelStuff()
        Dim objXL As Object

        Dim objBooks As Object
        Dim objBook As Object
        Dim objSheets As Object
        Dim objSheet As Object
        Dim objrange As Object
        Dim myTabColor As Long

        objXL = CreateObject("Excel.Application")
        objXL.Visible = True
        ' Get a new workbook.
        With objXL

            .Workbooks.Add()
            objrange = .Range("A1")
            objSheet = .sheets("Sheet1")
            myTabColor = objSheet.tab.color
            objSheet.tab.color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.ForestGreen)
            MsgBox("Tab color was changed from: " & myTabColor & " to " & objSheet.tab.color)
            objSheet.tab.color = myTabColor
        End With

    End Sub
End Module

Open in new window

0
 

Author Comment

by:bassman592
Comment Utility
Unfortunately, in C# that doesn't work. If I do this:

long currentColor;
currentColor = LastSheet.Tab.Color;

The compiler complains with: "Cannot implicitly convert type 'object' to 'long'. An explicit conversion exists (are you missing a cast?)"

Then, if I use an explicit cast:

long currentColor;
currentColor = (long)LastSheet.Tab.Color;

The compiler is happy, but I get the runtime error: "Specified cast is not valid."

I think this is one of the things that makes automating office in C# a little more difficult than doing it in VB...
0
 

Author Comment

by:bassman592
Comment Utility
FYI...

I also tried this (because I saw it online somewhere as a possible solution):

System.Drawing.Color currentColor;
currentColor = System.Drawing.ColorTranslator.FromOle((int)LastSheet.Tab.Color);

but this also gives me the runtime error:  "Specified cast is not valid."

I had high hopes for this because in order to set the tab color I use the ColorTranslator.ToOle method. It had a nice symmetry.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Shucks!  I'm at work, and my VSTO for C# book is at home.  Promise I'll look tonite if someone else hasn't. All I did was create a windows form that on a button click called that module I posted.  Any words of advice?  For the sake of my sanity, can you give me a close equivalent to the VB module I posted in C#?  Its easy to spend way to long on something stupid due to lack of experience in another language but I'm good at debugging if I have everything else working.

Cheers,

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
Ok - well, its curious.  Based on the way you set up the casting, you actually have to have an exception handler via try/catch or other mechanism to ensure that the exception regarding assignment to currentColor gets handled.

To figure that out, I went down this rabbit trail:
http://msdn.microsoft.com/en-us/library/system.invalidcastexception(v=vs.90).aspx and then down to Handling and Throwing exceptions.

However, I tried something much simpler.  As you did, I didn't declare currentColor as System.Drawing.Color.  I declared it dynamically with the assignment to lastSheet.Tab.Color and the system allowed that implicit conversion.

Here's my code, which runs as a result of a Windows Forms button click:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var objExcel = new Excel.Application();
            var objbook = objExcel.Workbooks.Add(Type.Missing);
            var lastSheet = objExcel.Worksheets[objExcel.Worksheets.Count] as Excel.Worksheet;
            var i = 0;
         

            //System.Drawing.Color currentColor;
            
            objExcel.Visible = true;

            lastSheet.Tab.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.ForestGreen);
            var currentColor = lastSheet.Tab.Color;

            //try
            //{
                //currentColor = (System.Drawing.Color)lastSheet.Tab.Color;
            //}
            //finally
            //{
                // To run the program in Visual Studio, type CTRL+F5. Then 
                // click Cancel in the error dialog.
              //  Console.WriteLine("\nExecution of the finally block after an unhandled\n" +
              //      "error depends on how the exception unwind operation is triggered.");
              //  Console.WriteLine("i = {0}", i);
            //}

        }
    }
}

Open in new window


Now, no compile or runtime error.

Thank goodness for a common object model and VS or I would have been lost.  Starting up C programming after 25 years is NOT like riding a bike!  And I wrote thousands of lines, re: early Windows programming.

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
And, for your color conversions, here's a bit of play with that, using HEX RGB and the fromArgb functionality.

Enjoy!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var objExcel = new Excel.Application();
            var objbook = objExcel.Workbooks.Add(Type.Missing);
            var lastSheet = objExcel.Worksheets[objExcel.Worksheets.Count] as Excel.Worksheet;
         

            System.Drawing.Color customColor;
          
            objExcel.Visible = true;

            var currentColor = lastSheet.Tab.Color;
            MessageBox.Show("The Last Sheet's Tab Color is: " + lastSheet.Tab.Color);

            lastSheet.Tab.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.ForestGreen);
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color);

            lastSheet.Tab.Color = 0xFF0000; //RGB Blue Hex Representation
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color + " Which should be Blue!");

            lastSheet.Tab.Color = 0x00FF00; //RGB Green Hex Representation
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color + " Which should be Green!");

            lastSheet.Tab.Color = 0x0000FF; //RGB Red Hex Representation
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color + " Which should be Red!");

            //The below was my first attempt, after doing some googling
            //customColor = System.Drawing.Color.FromArgb(255, 255, 0); //RGB Yellow using FromArgb
            //int myClr = customColor.ToArgb();
            //lastSheet.Tab.Color = customColor.ToArgb();

            //I translated then to this...
            lastSheet.Tab.Color = System.Drawing.Color.FromArgb(255, 255, 0).ToArgb(); //RGB Yellow using FromArgb
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color + " Which should be Cyan!");
        }
    }
}

Open in new window


Fun - thanks to you, I've now written my first C# code, lol.  Can't wait to get back to VB.NET! lol - just joking.

Dave
0
 

Author Closing Comment

by:bassman592
Comment Utility
Brilliant! Thanks a lot. I'm just starting C# (no prior c background, though) and I've seen the "var" keyword before but I've never used it. It's weird how the default tab color appears to be "False" - I wasn't expecting that! Anyway, thanks again, and I hope the foray into c# wasn't too inconvenient. I do wish I could use VB on this project, but unfortunately I can't.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now