Solved

Get the Tab color in Excel 2007 in C#

Posted on 2012-04-12
7
1,270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 42

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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 42

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 42

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 42

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 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