Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-08-2015, 06:39 AM
a888 a888 is offline Windows 8 Office 2007
Novice
 
Join Date: Feb 2015
Posts: 12
a888 is on a distinguished road
Default Setting a particular table cell background color when an option button is selected in Word 2007

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
Reply With Quote
  #2  
Old 03-11-2015, 04:02 PM
Guessed's Avatar
Guessed Guessed is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 781
Guessed is on a distinguished road
Default

Post a sample document which illustrates how you have done the document.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 03-12-2015, 06:35 AM
a888 a888 is offline Windows 8 Office 2007
Novice
 
Join Date: Feb 2015
Posts: 12
a888 is on a distinguished road
Default a sample doc attachment

Thanks so much Andrew

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

Thanks so much in advance
Attached Files
File Type: docm zQuestionnaire.docm (41.6 KB, 16 views)
Reply With Quote
  #4  
Old 03-12-2015, 10:40 AM
gmaxey gmaxey is online now Windows 7 32bit Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 770
gmaxey will become famous soon enough
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #5  
Old 03-13-2015, 02:43 AM
a888 a888 is offline Windows 8 Office 2007
Novice
 
Join Date: Feb 2015
Posts: 12
a888 is on a distinguished road
Default

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 View Post
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
Reply With Quote
  #6  
Old 03-13-2015, 06:51 AM
gmaxey gmaxey is online now Windows 7 32bit Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 770
gmaxey will become famous soon enough
Default

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"
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #7  
Old 03-16-2015, 07:09 AM
a888 a888 is offline Windows 8 Office 2007
Novice
 
Join Date: Feb 2015
Posts: 12
a888 is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 03-19-2015, 07:40 AM
a888 a888 is offline Windows 8 Office 2007
Novice
 
Join Date: Feb 2015
Posts: 12
a888 is on a distinguished road
Question

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 !
Reply With Quote
  #9  
Old 03-19-2015, 04:11 PM
Guessed's Avatar
Guessed Guessed is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 781
Guessed is on a distinguished road
Default

Again, you will need to post a sample doc - especially if you have included frames to complicate things further.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #10  
Old 03-23-2015, 06:17 AM
a888 a888 is offline Windows 8 Office 2007
Novice
 
Join Date: Feb 2015
Posts: 12
a888 is on a distinguished road
Default

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
Attached Files
File Type: docm zQuestionnaire_chckbx.docm (15.5 KB, 7 views)
Reply With Quote
  #11  
Old 03-24-2015, 07:44 PM
Guessed's Avatar
Guessed Guessed is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 781
Guessed is on a distinguished road
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #12  
Old 03-25-2015, 05:33 AM
a888 a888 is offline Windows 8 Office 2007
Novice
 
Join Date: Feb 2015
Posts: 12
a888 is on a distinguished road
Default

Thanks so much Andrew

Working great

Thank you everyone for your wonderful kind support
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 06:25 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft