Solved

Get the Tab color in Excel 2007 in C#

Posted on 2012-04-12
7
1,165 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
ID: 37840816
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
ID: 37842672
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
ID: 37842866
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 41

Expert Comment

by:dlmille
ID: 37843616
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
ID: 37845712
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
ID: 37845803
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
ID: 37851609
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 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

19 Experts available now in Live!

Get 1:1 Help Now