How do you use VBA to change the TAB color dynamically in Excel 2010 1

[SPYDERIX]

Hi there,

As the question states above, how would you use VBA to dynamically change the TAB color in Excel 2010 based on the formula: AB7 > AF7 to switch to red if true and revert back to the preset colors if false. I managed to figure out some kind of macro but I had to run the macro every time in order to get the color change and the common user wouldn't know how to do that or even that they are supposed to.

How can you setup some kind of event to do this dynamically on the fly?

Thanks.

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SkipVought]

Would you care to post the code that does not work as expected?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SPYDERIX]

Hi Skip,

This is the code I was trying:

Sub test()
If Sheets("Sheet2").Range("A1").Value <> "hello" Then
Sheets("Sheet1").Tab.ColorIndex = 6
Else
Sheets("Sheet1").Tab.ColorIndex = -4142
End If
End Sub

I just created a simple separate .xlsm file with two pages.

I followed all the instructions from:

http://www.pcreview.co.uk/forums/change-tab-colour-using-conditional-formatting-cell-t2944636.html

I'll tell you, it DID WORK. But every time the cell changed I had to run the macro. So for example, I had hello typed in, then I changed it to something else, ran the macro and the color changed, then when I re-typed hello again, the color wouldn't change until I re ran the macro again.

This is merely a matter of trying to get the macro to parse automatically every time the cell changes (or a formula etc). That's my problem and query.

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[MajP]

Sorry there Nate, but I truly thought you were joking, because your post does not seem to make any sense. Maybe you have some definition for dynamic that is different than the standard definition. In computing dynamic usually means a instantaneous or continuous change based on the presence or absence of some input.
So clearly trapping the worksheet change event meets that definition. So when the worksheet changes you run the procedure. If the criteria is met change the tab. Not sure how that does not meet your needs. Not sure how that is not considered dynamic. If you want to only run it on the cell change, AFAIK there is not cell change event. But I am even more confused about why the "common user wouldn't know how to do that or even that they are supposed to". If the event is in the on change why would the user have to do anything or even know about it.

Maybe you are right, maybe I cannot read, but I truly do not get what you are asking that is different from all those other posts showing you how to dynamically change the tab color using the worksheet change event. Hopefully you can explain it just a little better or someone who can read better than me can decipher it.

 

[SkipVought]

Right-click Sheet2 tab and select View Code

PASTE this code into the Sheet Code Window

Sub test()
    If Sheets("Sheet2").Range("A1").Value <> "hello" Then
        Sheets("Sheet1").Tab.ColorIndex = 6
    Else
        Sheets("Sheet1").Tab.ColorIndex = -4142
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    test
End Sub

enter data in Sheet2 and observe the results.

This is an example of what is possible using the worksheet change event.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SPYDERIX]

I don't know what doesn't make sense? If I didn't explain myself well enough then that should have been stated, rather than linking to a google search video. In any case here is what I was saying:

As you can now see above I have some posted code, from a site I found. The color won't change unless I manually run the macro, EVERY TIME. "the common user wouldn't know how to do that or even that they are supposed to". Maybe that makes more sense now.

What I have is a workbook with a configuration file (worksheet) at the beginning for various entries. Then I have 64 more pages after that that work off the entries. My formula: AB7 > AF7 refers to page numbers. Based on user input the workbook calculates how many of the 64 pages are relevant. I have conditional formatting to change colors on the non relevant pages because at some point there will be a page saying: "Page 37 of 28" etc and those pages currently change cell colors to point this out but I want to know if I can change the tab color too so you know not to bother with the page before you click on it.

I don't want to have to input my data, then run a macro first just to get the tabs to swap colors. The computer illiterate person wouldn't know to run a macro or that they even had to in order to change tabs colors. Therefore if you input one set of data, then press ENTER on your keyboard (or TAB) the cell solidifies and the colors should all change "dynamically". If you then change that same data again because you made a mistake or whatever, again the colors should change "dynamically" without needing an extra step or manually running a macro.

That's what I was meaning. If I didn't explain that well enough then that's my fault. Hopefully this is clear now.

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SPYDERIX]

Skip,

That's brilliant. Pretty much exactly what I wanted, now I'll have to play with this a bit to do exactly what I want.

Again, with reference to the above, I will enter data in my config sheet, then each page will calculate the total number of pages.

AB7 is a static hard coded number for the page and AF7 is the calculation (from config) for how many pages. My formula =IF(AB7 > AF7) really is the simplest form of figuring out if the page you are on is greater than the total allowed pages. Each page does this calculation though and so any data entered on the config would have to change many page tab colors based on the formula from each sheet for each sheet event.

I may in fact need to simply run the color change from the config file rather than each page or calculate total pages from the config and simply display it on each page rather than run the same calculation over and over.

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SkipVought]

I may in fact need to simply run the color change from the config file rather than each page or calculate total pages from the config and simply display it on each page rather than run the same calculation over and over.

There are also sheet events in the ThisWorkbook Object, so you can have code in ONE PLACE that applies to multiple sheets.

Not sure what your PURPOSE is for this tab shading, but I'm fairly certain that it can be accomplished with much less adieu.

Let me also say that sheets are not pages. Sheets are objects in the workbook. Pages only occur for purposes of printing and can vary from one pc or printer to another.

Furthermore, when I see 64, seemingly similar sheets, I immediately think that this could be greatly simplified by having ALL IN ONE TABLE on ONE SHEET. Think about it!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SPYDERIX]

I am going to control all the colors from my one config sheet instead. That will be easier.

Ultimately I was thinking of making my sheet only 2 pages, one for a config and one for calculations etc, but the thing is, I need someone to be able to print the whole thing or at least X number of pages with only one print command, otherwise you would need to enter in data every time you need a different page generated.

It's hard to explain what I'm doing and why really. What makes this more challenging is the fact that many computer illiterate people are going to use this (not all, but many). My system needs to be extremely simple for the end user.

I just re-read what you wrote and maybe I'm still not quite getting it or going about this the right way with regards to printing and having pages. If you knew exactly what I was up to you would probably understand more but at the same time this is for a private industry and not to be released to the general public.

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.

 

[SkipVought]

This does not make sense, "Ultimately I was thinking of making my sheet only 2 pages, one for a config and one for calculations"
Pages are a PRINTING thing, and have nothing to do with config & calculations.

I need someone to be able to print the whole thing or at least X number of pages with only one print command

These options are built-in features, in the Print dialog.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SPYDERIX]

Sorry: my workbook only 2 sheets. That's what I meant.

NATE

http://www.nateryder.com

Got a question? Search G O O G L E first.