Check box and conditional formatting

[mars1985]

Hi there,
I apologise in advance as I am a psychologist (and therefore not technologically minded at all!) but I really, really need some help with a word document to put together a new report template for patients. Essentially, what I'm trying to do is this....

I have a horizontal bar chart in excel that I need to put into a word based report template. The chart contains a data point and error bars either side of this. I need to then be able to copy and paste this over the top of a basic table in word, which one of about seven of the table columns (depending on the patient) will be shaded. If I try to just make my chart 'in front of text', you can't see the chart for any of shaded cells in the word table. That is, the excel chart can't be seen in any word cells that are shaded.

SO... What I've been trying to do is use check boxes (to indicate which column I need shaded, depending on the patient) to conditionally format the cells. For example, I click the check box 'Average' which would leave only the middle column shaded. But I can't figure out how to do this! What I had done previously in another document was use code that said if you click on this particular check box, the text underneath it disappears (e.g. because I've nominated it as a 'bookmark'), then when the box is unchecked, the text re-appears. THAT I can do, but I can't figure out how to get the shading happening, so I can still see the chart pasted over the top of it. I've attached a document to this post showing basically what I'd like, which HOPEFULLY makes some more sense!

Ok - I really hope that made sense, and I'm really sorry in advance if it doesn't. This is the last step in a long process of creating a new report template that will save hospital workers a lot of time, and any help is greatly appreciated!!!!

Take care,
Tamara

[Paul_Hossler]

On the Developer tab, click Design Mode

Then right click the checkbox and then View Code

I used the Change Event which fires everytime the checkbox changes

Checked = True, Not Checked = false

This is just quick example that will shade column 3 when it's checked, and clear column 3 when you uncheck it

Unclick (?) Design Mode to use it




Option Explicit

Private Sub extremelylow_Change()
If Me.extremelylow.Value = True Then
ActiveDocument.Tables(1).Columns(3).Shading.BackgroundPatternColor = wdColorGray10
Else
ActiveDocument.Tables(1).Columns(3).Shading.BackgroundPatternColor = wdColorAutomatic
End If
End Sub



Good luck

Paul

[gmaxey]

Paul,
I was going to post something like that, but only realized that is doesn't appear to solve the problem. If the Excel object is behind the text the shading will mask the object content. If the object is from of the text, then the object masks the shading.

At least that is what I see here.

[Paul_Hossler]

Greg -- I didn't read the Op's that way, but you're probably right

I never saw a table on top of a chart or vice versa before.

One possible workaround (a lot of work around) would be to strategically position shapes on top of the chart and then use the check boxes to make them transparent or shaded

Paul

[mars1985]

Dear Paul and Greg,
Thank you both so much for replying, I'll have a play with the code you posted and see what I can do - the main problem seems to be getting the chart pic to be visible over the top of the shading, which I'll keep working on. Thanks again, any help is greatly appreciated!!!!
Take care,
Tamara