MS Word 2000 & 2003: Are Stoplight Controls Available? 2

[djoliver1]

I'm trying to create a report in MS Word that needs to be functional in both the 2000 and 2003 versions. I wonder if there is a control or field available that simulates a report stoplight, where the user can click on the control and choose a green, red or yellow value. By the way, I'm undecided whether I should create this report in Word or Excel, so I'll ask the same question twice with different heading and see what kind of answer I get. Thanks in advance for any suggestions.

 

[fumei]

that simulates a report stoplight, where the user can click on the control and choose a green, red or yellow value.

I guess I am really out of the loop. I have no idea what that means.

What is a report stoplight?

What does the green, red, or yellow value DO???? I can see making something where the user can choose green red or yellow...but....then what?

Gerry

 

[GlennUK]

Hi Gerry, I must be out of the loop too, as I've never heard of a report stoplight either.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[fumei]

OK, I was curious and googled "report stoplight". Interesting. Who knew? I can't remember what we used to call this, but OK, at least now I know what the heck you are talking about. Sort of.

From what I can see, this is a project management kind of thing. I can see it as a functioning tool in Excel...but in Word????

I am trying to picture what it would do. I mean, you could group three ActiveX radio buttons, labelled Green, Yellow, Red. The user could select one. But...yeah...so what? What is it supposed to DO?

Gerry

 

[xlbo]

yeh - can you explain what you want this to do as normally 'stop light' (or traffic light in the UK) type fields are typically output fields of Red, Yellow & Green to quickly identify action or not - kinda like quartiles but only 3 of 'em.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ThomasLafferty]

It seems to me that this sort of thing is used at Boeing, among other companies to show the status of safety items by department. Across the top of the colummn will be the name of the safety item, and down the rows will be the names of departments and the colors will indicate how they are doing with it. If you are looking to flag something in color using a control, why not use the ordinary frame control which comes with Excel's forms toolbar, set the control's Cell Link property to a cell containing the following conditional formatting:

Cell Value Is Equal to 1 Green Font, Green Pattern Fill
Cell Value Is Equal to 2 Yellow Font, Yellow Pattern Fill
Cell Value Is Equal to 3 Red Font, Red Pattern Fill

If you size the control so that it fits within the borders of the cell,you should be able to autofill down, but then you will need to point each control's cell link to an appropriate cell (the relative reference doesn't autofill properly).

Hope this helps.

Tom

Live once die twice; live twice die once.

 

[djoliver1]

Sorry I took so long to clarify this: yes, the stoplight would just be a field with a color-coded value of either red, yellow or green. It could only display one value at a time, but you should be able to click on the field to change the value. The way you can click on a flag field and choose yes or no, click on a checkbox field to check or uncheck it. The purpose of this field is that it provides an indication of a status associated with the field through a label. For example: Quality = green, means that there are no issues with the quality of the work on a particular operation or project. The report would list several status characteristics and the users would indicate what the value is for each: red, yellow or green. This control comes standard with MS Project, but not with Excel or Word. So I was hoping that someone knew of a shareware control that achieved this end.

 

[djoliver1]

Tom has got the right idea for what I was trying to achieve in Excel. Using or misusing his idea, I put the text: Green, Yellow, Red in three adjacent cells and then used the data validation function to associate this list as the three possible values for a stoplight cell. That creates a dropdown list on that cell. Then conditional formatting as you indicated will set the cell to fill green, yellow, or red depending on the dropdown value you choose from the list. [I looked on the Forms Toolbar for a grid control, but only found a grid toggle, which maybe what you meant (?). The combo box looks about right, but I can't get the background color to change.]

Anyway, how can I do something like this in Word??? I'd appreciate any suggestions you might have for creating a stoplight field in Word 2000 / 2003. Thanks again for previous and any additional input.

 

[ThomasLafferty]

From the forms toolbar, select the control that looks like a box with the letters xyz in the top left corner. That's an option group. Next, put 3 option buttons inside the borders of the option group. Right click any of the option buttons and set the cell link where you want the result to appear. Change the labels of the option buttons to Red (Value 1), Yellow (Value 2) and Green (Value 3). Format the linked cell conditionally as appropriate. I will look into Word for you.

Tom

Live once die twice; live twice die once.

 

[djoliver1]

Tom and Fumei,
In Word there is a dropdown form control, where I can easily create a drop down list of green, yellow, red, but I have no idea how to change the background color of this control. These Word controls seem to have almost no properties to set or change. There is the ability to run a macro on exiting the control, so I assume that is where I'd have to set background to green. Any advice on macros belongs in the VBA forum, doesn't it? I probably need to post any further questions there. I'll wait to hear if I get any further responses here before posting there. Thanks again.

 

[ThomasLafferty]

Try checking out this article on programming an active x control (Microsoft).

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaxctrl/html/msdn_abcsmfc.asp

Seems like it would be quite a bit of work...

Tom

Live once die twice; live twice die once.

 

[djoliver1]

Yes, it's clearly over my head. Do you or anyone else know a good place to look for activeX controls that others have written. Maybe somebody's already created something like this. I'm sure I'm not the only one who needed something like this in a Word form.

 

[fumei]

The Forms control background can not be changed.

That link was to ActiveX control on a web page. The code and structure described is not really applicable for Word or Excel.

As for ActiveX control in Word and Excel, Tom had a suggestion for Excel. Word? Here is the situation.

There is an Image ActiveX control. You CAN change the backcolor. The Image control is always square - so you can not make a circle visual.

You COULD make a circle visual but loading an image into the Image control, and making the border clear.

But let's keep it fairly simple. So you have an Image control, and say make the background green. You DO have events with these. There is a _Click event. So when you click the control you can make code run.

As much as this seems wildly odd - and perhaps silly - you could have in the _Click event code to change the background. Note unless you explicitly reference the control in another module, you MUST put this in the ThisDocument module. The ThisDocument module handles ActiveX control natively - you can select them from the Object dropdown. The code would be like this:

Sub Image1_Click()
Dim sColor As String
sColor = InputBox("Please type in (text) red, yellow or green, and press OK.")
Select Case UCase(sColor)
    Case "RED"
        Image1.BackColor = &HFF&
    Case "YELLOW"
        Image1.BackColor = &HFFFF&
    Case "GREEN"
        Image1.BackColor = &HC000&
    Case Else
        MsgBox "An invalid input.  Please click control again."
End Select
End Sub

NOTE: you can NOT - repeat can NOT - use normal VBA color constants, like wdRed, wdYellow. WHY....I have no idea. Seems amazingly stupid to me. But you can't. You must use hex or RGB, as in .BackColor = RGB(0, 128, 64). Interestingly, if you go to Properties of the control and copy the hex into code, it removes the 0's.

&H00000006& becomes &HFF&

Using "normal" color constants makes the control go black. Shrug...what can I say.

So there you go. You can make an ActiveX control change color with a click.

I still have to say....BIG DEAL. So what? I don't get it. Is this just a visual clue? What does this have to do with a document????????

Other Notes: You certainly could make a better user interface. This used an inputbox. As I have not got a clue how you were intending this to be used...how is it supposed to change...you mentioned a yes/no example..so I did a inputbox. You COULD use an actual userform with option buttons - that way you would not have to error trap for gibberish input. The user types in "Go to hell" etc etc.

Gerry

 

[ThomasLafferty]

Hi all! After thinking about this for some time, it occurs to me that we really need to think along the lines of chosing the proper tool for the job. Bearing in mind, of course, that the user wrote:

By the way, I'm undecided whether I should create this report in Word or Excel

I would strongly recommend Excel for this for the reason that Word is designed primarily to be a word processor with lots of nifty tools to help produce a pretty page. When it comes to crunching numbers and performing analysis, Excel is clearly superior. It seems to me that since this is likely going to be used in some sort of management support reporting role, doesn't it make sense that management might want averages, percentages and totals concerning how many items have a status of green vs red vs yellow? Maybe we could find some sort of convoluted way of doing this, but is it worth the hassle only to find that if we are asked for the next level of complexity we are back to the drawing board? True, Word can crunch numbers, but it's not very good at it.

Above I recommended using a series of option groups in Excel, and I am convinced that this is the easiest way to go since it will give numeric values in the sheet which can be totaled, averaged and otherwise easily manipulated.

Heck, you can even generate cool supporting charts, nifty pivot tables, whatever you need. So why use a word processor when a data analysis tool will simply outperform it 10 to 1 in this kind of context?

For what it's worth, that's my advice.

Tom

Live once die twice; live twice die once.

 

[fumei]

We don't really know what is going on with this. So...yes of course. If it IS a number thing...use Excel. This is what i have been saying all along...WHAT is supposed to happen in a Word document?

Word should never be used for what Excel is designed for. Word is a word processor...let's use it for that....hmmmmmm?

Gerry

 

[djoliver1]

Thanks Fumei and Tom:
I appreciate the valuable feedback. I've found a solution for creating stoplight controls in both applications, which is what I was looking for. The value of this little control, Fumei, is that it reports is that it allows the user to provide visual feedback on the status of their work that managers can easily scan for problems. They just hone in on red signals and manage by exception.

Tom, I understand what you are saying about the superior reporting functions of Excel. I just wish it had better basic word processing functions too since users need to enter a lot of text into these reports as well. But I've decided to develop it in Excel for the reasons you said. Thanks again everyone. This is a great forum. I'm glad I found it.