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

gmaxey · #1 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 · #2 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

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