Topic awaiting preservation: Where does an excel question go?

I know this isn't the forum that one would generally pose a question about excel, but I thought someone might know something and point me in the right direction before I pull more of my hair out.

I need to take a chart that a user designed and make the colors for the visual representation (i.e. a bar) change color depending on the value. The values already change automatically depending on the data it's pulling from, obviously. I need to take that value and, say, make it more red the lower the number is or make it more green the higher it is. I've garnered that I'm pretty much going to have to do something with the source (which I don't know where to start with that) and it's going to have to be in vbscript or c# using the excel object model. Other than that I have no clue what to do. If someone has a good link or a fast method or a better idea then any assistance will be fully appreciated (in an appropriate manner).

Feel free to move this to the appropriate place. My logic cannot divine where this would be.

ok here's an universal guide to programming:

1) think it over step by step
2) if you're experiencing diffuculties start from the last step and work your way down to the first
3) remember: it's easy! you just have to concentrate and find out what you need
4) calm down and get to it! :)

I use the 2. alot when I'm working on something that is potentially going to drive me nuts. (for example artificial neurons) but you can't always use that ...

I didn't really understand what you needed ...

=== If the chart is in another file/place ====
Do you know how to do the visual representation part? Start with that.
Then you need to find out how to get the values from the spreadsheet. Just take your time and search the web.
You didn't specify how the colors are changing on the spreadsheet. Is it because of a macro or is the file changing? If it's a macro then better forget the spreadsheet. Take the macro and port it to C# or whatever you are using.

=== If the chart is in the same file or in another spreadsheet file ===
then just record a macro, change the colors manually, stop macro and modify it to fit your actual needs. probably no need to mess with C# or anything


I myself have managed to avoid spreadsheets so far. They are usually unnecessary and useless. Text files and databases are much more appropriate places to store your specific data like color values. Ofcourse if the chart you're talking about is in the same file as the color values then there's no need for those.
If you still need help then feel free to ask for more. I'm sure me or someone else here can come up with something. But you have to be more specific. What step do you need help with?


I looked into it a little ... didn't find out how you could change the colors of a chart ... I'm using OO.o tho maybe it's different in Excel. If you end up in the same place then you probably have 2 choices:
a) tell your client that this can't be done
b) use C# or whatever you wanna use to draw a new chart and load it into the spreadheet with a macro

I really don't have a problem programming... I just don't use anything Micro-tedious-soft to do it. And I completely concur on the uselessness of spreadsheets. However, we both know that it's pointless telling something like that to a user who has no knowledge of anything else. This isn't my usual sort of task, nor would I normally be the one it's assigned to. What can you do?

I can't tell the client (actually, user... who is senior to me even if not in my chain of command) that it's impossible because he knows for a fact that it is, he just doesn't know how it's done.

So, after much digging and using thousands of different words and combinations of words in that there newfangled google thing, this is what I found this morning:
http://www.mrexcel.com/archive/Chart/6809.html

Problem is, again, I know nothing Microsoft. Supposedly you have to add a class module. But where the heck do you add that? I found some tutorials on how to do this using Visual Studio projects, but I don't have Visual Studio and getting someone to put it on a Navy Machine in less than a year, nevermind in a day, is like pulling teeth. Supposedly VB is inside Excel, but I just can't find out where to add it anywhere.

Maybe I'm just tired.

(Edited by Red Ninja on 09-11-2005 19:07)

RN, what you want to use to write VB(A) macros in Excel / Word / PP is the built-in VBA Editor. You can open it by pressing Alt-F11 or finding it in the menu tools-macros... IIRC.

Inside that editor you right-click somewhere into the folder structure in the top/left part of the window and you'll see an option that lets you create a new module, which is where the page you linked to assumes you're pasting the macro code.

I didn't have a closer look at the code now so I can't tell if it is what you're looking for, but once you have this sub in the module you can just add a button to one of you sheets (there's a 'forms' toolbar that lets you do it), assign the macro to it and give it a try.

Hope that gets you started.

/k

That's just what I need. Even if the above script doesn't work, when I'm able to put code in that bad boy then there's no need to worry anymore. Up until this issue I didn't know that excel had a POM or that you can write vbscript to it. Thanks all!! (or both, but all sounds better)