A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Macro change sheet tab color and name



 
 
Thread Tools Display Modes
  #1  
Old March 8th 09, 02:40 AM posted to microsoft.public.excel.misc
Gene Augustin
external usenet poster
 
Posts: 36
Default Macro change sheet tab color and name

I have a workbook with only one sheet. I want to use a macro to change the
name of that sheet to "QIF" and its color to RED and add a second worksheet
with "IIF" name and color Yellow.

Gene Augustin

Ads
  #2  
Old March 8th 09, 03:29 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default Macro change sheet tab color and name

Try recording a macro when you do it manually and I bet you'll end up with code
that works fine.

Gene Augustin wrote:
>
> I have a workbook with only one sheet. I want to use a macro to change the
> name of that sheet to "QIF" and its color to RED and add a second worksheet
> with "IIF" name and color Yellow.
>
> Gene Augustin


--

Dave Peterson
  #3  
Old March 8th 09, 03:31 AM posted to microsoft.public.excel.misc
Sheeloo[_3_]
external usenet poster
 
Posts: 1,805
Default Macro change sheet tab color and name

Sub colorSheet()
Sheets(1).Name = "QIF"
Sheets("QIF").Tab.Color = 255
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(2).Name = "IIF"
Sheets("IIF").Tab.Color = 65535
End Sub

"Gene Augustin" wrote:

> I have a workbook with only one sheet. I want to use a macro to change the
> name of that sheet to "QIF" and its color to RED and add a second worksheet
> with "IIF" name and color Yellow.
>
> Gene Augustin
>
>

  #4  
Old March 8th 09, 06:34 AM posted to microsoft.public.excel.misc
Shane Devenshire
external usenet poster
 
Posts: 857
Default Macro change sheet tab color and name

Hi,

You can just record the steps I gave you in your previous post, that way you
get the colors you want.

You didn't tell us the version of Excel you are using.

2003:

Sub SheetSetup()
With ActiveSheet
.Name = "QIF"
.Tab.ColorIndex = 42
End With
Sheets.Add After:=ActiveSheet
With ActiveSheet
.Name = "IIF"
.Tab.ColorIndex = 47
End With
End Sub

2007:

Sub SheetSetup()
With ActiveSheet
.Name = "QIF"
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
Sheets.Add After:=ActiveSheet
End With
With ActiveSheet
.Name = "IIF"
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249977111117893
End With
End Sub

-----

If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Gene Augustin" wrote:

> I have a workbook with only one sheet. I want to use a macro to change the
> name of that sheet to "QIF" and its color to RED and add a second worksheet
> with "IIF" name and color Yellow.
>
> Gene Augustin
>
>

  #5  
Old March 8th 09, 07:07 PM posted to microsoft.public.excel.misc
Gene Augustin
external usenet poster
 
Posts: 36
Default Macro change sheet tab color and name

From Shane Devenshire

..ActiveSheet.Tab.ColorIndex = 42

*********
From Sheeloo:

Sheets("QIF").Tab.Color = 255

********

Both codes give error:

Run-time error '438'
Object doesn't support this property or method


Is there an add-in that I should be using?
I have Analysis Toolpac and Analysis Toolpac-VBA installed.




On 3/7/09 9:31 PM, in article
, "Sheeloo" <Click above
to get my email id> wrote:

> Sub colorSheet()
> Sheets(1).Name = "QIF"
> Sheets("QIF").Tab.Color = 255
> Sheets.Add After:=Sheets(Sheets.Count)
> Sheets(2).Name = "IIF"
> Sheets("IIF").Tab.Color = 65535
> End Sub



  #6  
Old March 8th 09, 07:47 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default Macro change sheet tab color and name

You can only color the worksheet tabs if you're using xl2002 or higher.

What version of excel are you using?

Gene Augustin wrote:
>
> From Shane Devenshire
>
> .ActiveSheet.Tab.ColorIndex = 42
>
> *********
> From Sheeloo:
>
> Sheets("QIF").Tab.Color = 255
>
> ********
>
> Both codes give error:
>
> Run-time error '438'
> Object doesn't support this property or method
>
> Is there an add-in that I should be using?
> I have Analysis Toolpac and Analysis Toolpac-VBA installed.
>
> On 3/7/09 9:31 PM, in article
> , "Sheeloo" <Click above
> to get my email id> wrote:
>
> > Sub colorSheet()
> > Sheets(1).Name = "QIF"
> > Sheets("QIF").Tab.Color = 255
> > Sheets.Add After:=Sheets(Sheets.Count)
> > Sheets(2).Name = "IIF"
> > Sheets("IIF").Tab.Color = 65535
> > End Sub


--

Dave Peterson
  #8  
Old March 8th 09, 10:03 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default Macro change sheet tab color and name

I don't know what features were added in what versions for Macs.

You may want to post your question he
news://msnews.microsoft.com/microsof...c.office.excel
if you don't get a good response.

Gene Augustin wrote:
>
> Office 2004, Excel 2004, Version 11.5.3, MAC Powerbook G4, OS 10.5.6
>
> On 3/8/09 2:47 PM, in article , "Dave
> Peterson" > wrote:
>
> > You can only color the worksheet tabs if you're using xl2002 or higher.
> >
> > What version of excel are you using?
> >
> > Gene Augustin wrote:
> >>
> >> From Shane Devenshire
> >>
> >> .ActiveSheet.Tab.ColorIndex = 42
> >>
> >> *********
> >> From Sheeloo:
> >>
> >> Sheets("QIF").Tab.Color = 255
> >>
> >> ********
> >>
> >> Both codes give error:
> >>
> >> Run-time error '438'
> >> Object doesn't support this property or method
> >>
> >> Is there an add-in that I should be using?
> >> I have Analysis Toolpac and Analysis Toolpac-VBA installed.
> >>
> >> On 3/7/09 9:31 PM, in article
> >>
, "Sheeloo" <Click above
> >> to get my email id> wrote:
> >>
> >>> Sub colorSheet()
> >>> Sheets(1).Name = "QIF"
> >>> Sheets("QIF").Tab.Color = 255
> >>> Sheets.Add After:=Sheets(Sheets.Count)
> >>> Sheets(2).Name = "IIF"
> >>> Sheets("IIF").Tab.Color = 65535
> >>> End Sub


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change sheet tab name and color Gene Augustin Excel Discussion (Misc queries) 1 March 8th 09 12:33 AM
change the sheet tab color Tracy Excel Discussion (Misc queries) 1 June 8th 06 06:34 PM
Macro To Change Cell Color When Value Changes carl Excel Worksheet Functions 4 March 14th 06 09:24 PM
change sheet tab color? mark Excel Discussion (Misc queries) 2 May 26th 05 11:18 PM
macro color change Dan Excel Worksheet Functions 0 February 15th 05 10:35 PM


All times are GMT +1. The time now is 11:36 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.