Improve company productivity with a Business Account.Sign Up

x
?
Solved

Macro to switch graphic with other input variables

Posted on 2011-03-16
11
Medium Priority
?
232 Views
Last Modified: 2013-11-27
EE Professionals,

I have a Graphic that is composed of a set of cells where I have applied certain conditional formatting.  What I want to do is have a button/toggle switch, that allows me to maintain the exact same graphic (same cells on output), but substitute a different set of input variables into the cells to have the conditional formatting apply.  This will change the color formatting to represent the second set of variables.  

I have attached a sample of this; Condition 1 is reflected in the current graphic/set of cells. Condition 2 needs to be toggled in.  When hitting the button again, Condition 1 reappears, and so on.

I plan to ask a related question (telling you in advance) to add a third condition where it is Condition 2 minus Condition 1 with the result displaying in the conditional formatting as a third state.

Thank you in advance.

B.
Conditional-Formatting-with-swit.xlsm
0
Comment
Question by:Bright01
  • 5
  • 5
11 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1600 total points
ID: 35147414
Check this file. I have used helper cells
Copy-of-Conditional-Formatting-w.xlsm
0
 
LVL 19

Assisted Solution

by:Arno Koster
Arno Koster earned 400 total points
ID: 35147597
something like this :
Sub toggle_condition()
'
' Toggle conditional layout
'

    For Each cel In Range("C9:D13").Cells
        cel.Select
        For Each formatting In cel.FormatConditions
            If InStr(formatting.Formula1, "H") > 0 Then
                formatting.Modify Type:=xlExpression, Formula1:=Replace(formatting.Formula1, "H", "I")
            ElseIf InStr(formatting.Formula1, "I") > 0 Then
                formatting.Modify Type:=xlExpression, Formula1:=Replace(formatting.Formula1, "I", "H")
            End If
        Next formatting
    Next cel

End Sub

Open in new window


 updated-Conditional-Formatting-w.xlsm
0
 

Author Comment

by:Bright01
ID: 35151427
Guys,

Thanks.  Two things;

1.) Can you have the Chart# replace "CHART" when you toggle the correct list of variables?

2.) Can you give me some guidence on how I incorporate it into my master sheet?  Specifically, I copy it into a new Module, insure that the ranges match up on the variables, insure the Chart Names are replaced properly and then link the Toggle to the routine?

Thanks much!

B.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35151462
For part 1)

In my file change

CHART

to

="CHART "&F6
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35151546
For part 2)

= move (not copy) the range which contains the references to the CF to another location. (In the given file you would move the range H8:H13 to another location like F8:F13 in my file

= copy values from the new range back to the original range (F8:F13 to H8:H13)

- enter an index number above this range (in my file it is at F6)

- enter index() formulas in the new range F8:F13 (enter =INDEX(H8:J8,1,F$6) in F8 and copy it down) This formula will pick up the value from the column referred to by the cell F6.

Now all you have to do is change the value in cell F6 from 1 to 2 and back to see the chart changing. This can be automated by a macro as given.

Saqib

-
0
 

Author Comment

by:Bright01
ID: 35151739
Guys,

Update.... I got it to work in my workbook.  I'd still appreciate it if you could add a way that when you toggle, you get the right Chart name.  Also, when the macro runs, it automatically moves the cursor down several cells when you push it.  Is there a way to prevent that?

Thank you,

B.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35151941
Can you be specific which of the two solutions you are talking about. The one from ssaqibh or the one from akoster.

The one from ssaqibh does not move the cursor when run.
0
 

Author Comment

by:Bright01
ID: 35152434
Wow guys.... ok, I spent an hour testing both of these solutions and I must say I'm impressed with both of them.  Two great ways to do the same thing.  I must say that after testing both, ssaqibh's solution is my first choice; cursor doesn't move and there is no visable macro ficker (although ssaqibh's solution took me an additional 45 minutes to change all of my conditional formatting.

So akoster, much thanks, very good work.  Ssaqibh, great job.  I need one last small fix before I close this out.  When I installed your fix for the Chart change, it changed the number from Chart 1 to Chart 2.  However, my Chart 1 is actually a name and so is Chart 2.  So I don't want to change the number of the Chart, I want to actually replace the Chart 1 name with the Chart 2 name.

Can you advise me on how to do that little switch?

Thank you both,

Bright01
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35152470
replace the chart name with an index formula similar to the ones you did in the data.
0
 

Author Comment

by:Bright01
ID: 35152819
Please show me.....
0
 

Author Closing Comment

by:Bright01
ID: 35154268
Thanks guys; I got the toggle to work.  Very good work.  Much appreciated.
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

584 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