Setting a particular table cell background color when an option button is selected in Word 2007

a888 · #1 03-08-2015, 06:39 AM

Hi everyone,

I have a table (7 columns and 90 rows) with 90 questions each question in a row.

In each row: the question comes in col 1, and in col 2 comes a radio button for Yes, and in col 3 a radio button for No. Then in only some of the last 4 cells on the right there is a "Y" letter. Those cells are either empty or have the letter Y in them.

I named the option buttons as e.g. for first row/ question: OptionButton1y for the Yes button and OptionButton1n for the No. I also grouped each two option buttons in each row in a separate group called Q1, Q2....etc. (Note: creating those buttons made the doc take ages to load when first opened ! )

What I need to have is :

When a NO option button is selected in any row (question), a macro looks if any of the last 4 cells in that row has a Y in it and if so to change the cell color shading to red.

I need this to occur with each NO selection on each of the rows.

I hope someone can kindly help me with this.

Thanks so much in advance

Guessed · #2 03-11-2015, 04:02 PM

Post a sample document which illustrates how you have done the document.

a888 · #3 03-12-2015, 06:35 AM

Thanks so much Andrew

Here is attached a sample doc illustrating what I have and need achieved.

Thanks so much in advance

gmaxey · #4 03-12-2015, 10:40 AM

I'm not a big fan of ActiveX controls in Word. I don't have time to figure out why the cell has to be selected, but this is doing what you want I think:

Code:

Private Sub OptionButton1n_Change(): ChangeState OptionButton1n: End Sub
Private Sub OptionButton2n_Change(): ChangeState OptionButton2n: End Sub
Private Sub OptionButton3n_Change(): ChangeState OptionButton3n: End Sub
Private Sub OptionButton4n_Change(): ChangeState OptionButton4n: End Sub
Private Sub OptionButton5n_Change(): ChangeState OptionButton5n: End Sub
Private Sub OptionButton6n_Change(): ChangeState OptionButton6n: End Sub
Sub ChangeState(oOB As MSForms.OptionButton)
If oOB.Value = True Then
    ShadeCells Selection.Rows(1)
  Else
    ClearCells Selection.Rows(1)
  End If
lbl_Exit:
  Exit Sub
End Sub
Sub ShadeCells(oRow As Row)
Dim lngIndex As Long
  For lngIndex = 4 To 7
    If fcnCellText(oRow.Cells(lngIndex)) = "Y" Then
      oRow.Cells(lngIndex).Range.Select
      oRow.Cells(lngIndex).Range.Shading.BackgroundPatternColor = wdColorRed
    End If
  Next
lbl_Exit:
  Exit Sub
End Sub
Sub ClearCells(oRow As Row)
Dim lngIndex As Long
  For lngIndex = 4 To 7
    Selection.Rows(1).Cells(lngIndex).Range.Select
    Selection.Rows(1).Cells(lngIndex).Range.Shading.BackgroundPatternColor = wdColorAutomatic
  Next lngIndex
lbl_Exit:
  Exit Sub
End Sub
Function fcnCellText(oCell As Cell) As String
Dim oRng As Word.Range
  Set oRng = oCell.Range
  oRng.End = oRng.End - 1
  fcnCellText = oRng.Text
lbl_Exit:
  Exit Function
End Function

a888 · #5 03-13-2015, 02:43 AM

Thanks so much Greg for your kind help

The code worked great. It first threw a runtime error (this command is not available) and figured out I had to remove protection of the doc for it to work which is fine with me.

Quote:

Originally Posted by gmaxey

I'm not a big fan of ActiveX controls in Word

I wonder if there is a better choice for having option buttons functionality (allowing only one answer be selected) that I can use instead especially as I mentioned before the document takes ages to load when the number of option buttons is big as in the case of this one (90 x 2 ).

Quote:

Code:

Private Sub OptionButton1n_Change(): ChangeState OptionButton1n: End Sub
Private Sub OptionButton2n_Change(): ChangeState OptionButton2n: End Sub
Private Sub OptionButton3n_Change(): ChangeState OptionButton3n: End Sub
Private Sub OptionButton4n_Change(): ChangeState OptionButton4n: End Sub
Private Sub OptionButton5n_Change(): ChangeState OptionButton5n: End Sub
Private Sub OptionButton6n_Change(): ChangeState OptionButton6n: End Sub

My actual document has 90 questions and I hope there is a solution to avoid having to repeat the above code for each of the 90 questions ! If having a very long code and repetition won't affect the performance/ loading of the document, then I can repeat the code line for each of the 90 though.

Quote:

Code:

 If fcnCellText(oRow.Cells(lngIndex)) = "Y" Then

I have some cells with "Y*" instead of "Y" and the code is ignoring those ones. It would be great if it can recognise Y* as well as Y.

Thanks so much for all your kind help

gmaxey · #6 03-13-2015, 06:51 AM

Code:

Select Case fcnCellText oRow.Cells(lngIndex)
  Case "Y", "Y*"
      oRow.Cells(lngIndex).Range.Select
      oRow.Cells(lngIndex).Range.Shading.BackgroundPatternColor = wdColorRed
 Case Else 'Do nonthing
 
 End Select

You could use a Content Control dropdown "Yes/No"

a888 · #7 03-16-2015, 07:09 AM

Thanks so much for your kind help always Greg.

Quote:

You could use a Content Control dropdown "Yes/No"

I'm afraid dropdown lists is not a good choice for me and it would be less user-friendly having too many option buttons in my docs.

I noticed that only my docs that have many option buttons (ActiveX Controls) are too slow to open and I was hoping I could have a macro to be able to use checkboxes instead and I came across your good article (linked below) but I'm afraid it only applies to Word 2010 and above as far as I understand so far from reading it ! I hope I'm wrong about this

http://gregmaxey.com/word_tip_pages/...n_buttons.html

Cheers

a888 · #8 03-19-2015, 07:40 AM

Update:

Every document I have with a number of Active X Controls (Option Buttons mainly) is a big headache taking very long time to open !

I came across this nice solution MakeCheckBoxesExclusive macro to make checkboxes behave like Option Buttons.

I tested it on the previously attached doc sample and worked fine (I kept the table and inserted frames in each row and still worked fine).

However, the ShadeCells macro does not work anymore for it needs to first be tweaked to work with the checkboxes now instead of the OBs and, second, it needs the document be unprotected while the MakeCheckBoxesExclusive macro needs it protected to work !

Guessed · #9 03-19-2015, 04:11 PM

Again, you will need to post a sample doc - especially if you have included frames to complicate things further.

a888 · #10 03-23-2015, 06:17 AM

Thanks Andrew,

Here is an updated sample doc attached, thanks.

I gave up using frames and the whole "checkbox with option button functionality" thing. I now have only checkboxes instead of option buttons, to make the document load faster.

The kindly provided Greg's ShadeCells macro above needs now be re-written to work with checkboxes in place of option buttons !

I hope this is feasible with your great help everyone

Thanks so much in advance

Guessed · #11 03-24-2015, 07:44 PM

Try this version of the code and set every checkbox to run MakeCheckBoxesExclusive on Exit

Code:

Sub MakeCheckBoxesExclusive()
  Dim oFF As FormField, rng As Range
  
    Set rng = Selection.Rows(1).Range
  
  If Selection.FormFields(1).CheckBox.Value Then
    For Each oFF In rng.FormFields
      oFF.CheckBox.Value = False
    Next oFF
    Selection.FormFields(1).CheckBox.Value = True
  End If
  
  'Must unprotect to change cell shading
  ActiveDocument.Unprotect Password:=""
    If rng.FormFields(2).CheckBox.Value = True Then
      ShadeCells rng.Rows(1)
    Else
      ClearCells rng.Rows(1)
    End If
  ActiveDocument.Protect Type:=wdAllowOnlyFormFields, noReset:=True, Password:=""
End Sub

Function ShadeCells(oRow As Row)
  Dim lngIndex As Long
  For lngIndex = 4 To 7
    Select Case fcnCellText(oRow.Cells(lngIndex))
      Case "Y"
        oRow.Cells(lngIndex).Range.Shading.BackgroundPatternColor = wdColorRed
      Case "Y*"
        oRow.Cells(lngIndex).Range.Shading.BackgroundPatternColor = wdColorBrown
     End Select
  Next
lbl_Exit:
  Exit Function
End Function
Function ClearCells(oRow As Row)
  oRow.Range.Shading.BackgroundPatternColor = wdColorAutomatic
End Function
Function fcnCellText(oCell As Cell) As String
  Dim oRng As Word.Range
  Set oRng = oCell.Range
  oRng.End = oRng.End - 1
  fcnCellText = oRng.Text
lbl_Exit:
  Exit Function
End Function

a888 · #12 03-25-2015, 05:33 AM

Thanks so much Andrew

Working great

Thank you everyone for your wonderful kind support

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to remove background color in a table that isn't there but is there?	pintree3	Word	5	10-27-2014 10:23 AM
Can't change table background color in the header in Microsoft Word 2011	simeon1	Word	0	10-13-2014 11:07 AM
Option Button selected will display text	ksigcajun	Word VBA	7	07-14-2014 09:31 AM
Cell Background Color: Base it on Content of Cell?	tatihulot	Excel	4	08-14-2013 03:24 PM
Change cell color everytime a value is selected in dropdown list	angelica_gloria	Excel	4	01-27-2012 06:47 PM