Jean-Guy ? One click highlighting of table cells

[Mifty]

Hi Jean-Guy (or anyone else who can help),

Sorry for all these posts but it would be so satisfying to find out if and
how this can work.

I have a word document with a table of options to choose from. Each Row
contains descriptions which will be rated as Excellent (5) Good (4)
Satisfactory (3) Poor (2) and Unsatisfactory (1).

At the moment users print the document out, highlighting and scoring in pen.

Well we want to get all modern and have them do it electronically..... !

What I would like to do is have a way of clicking 1 cell in each row and
then it highlights that cell. Jean-Guy provided a way of doing it that was
working brilliantly until I came across something that said that Activex
Controls aren't compatible with Macs. No control over what OS users will use
so may be problem.

Then greedily or overoptimistically would like to automatically score each
row and sum each column then all columns. Thought I could do that by using
Text forms - calc on exit and then sum in last row, using sections to have
Activex on unprotected parts of form and Form Controls on protected parts. It
meant 2 entries per answer but would have been ok if not for the possible
problem of Macs and Activex controls.

Any suggestions/solutions would be fantastic!!!

Thank you - sorry for long post

 

[Jean-Guy Marcil]

Hi Jean-Guy (or anyone else who can help),

Sorry for all these posts but it would be so satisfying to find out if and
how this can work.

I have a word document with a table of options to choose from. Each Row
contains descriptions which will be rated as Excellent (5) Good (4)
Satisfactory (3) Poor (2) and Unsatisfactory (1).

At the moment users print the document out, highlighting and scoring in pen.

Well we want to get all modern and have them do it electronically..... !

What I would like to do is have a way of clicking 1 cell in each row and
then it highlights that cell. Jean-Guy provided a way of doing it that was
working brilliantly until I came across something that said that Activex
Controls aren't compatible with Macs. No control over what OS users will use
so may be problem.

Then greedily or overoptimistically would like to automatically score each
row and sum each column then all columns. Thought I could do that by using
Text forms - calc on exit and then sum in last row, using sections to have
Activex on unprotected parts of form and Form Controls on protected parts. It
meant 2 entries per answer but would have been ok if not for the possible
problem of Macs and Activex controls.

Any suggestions/solutions would be fantastic!!!

I believe that the only way to achieve all that is to used a protected form
and formfields.

To test the code I suggest below, first build a table as follows:

5 rows x 7 columns
Row 1 is a heading row.
Row 5 is for the result of the calculation (Average)
Column 1 has the check boxes for visual impact (Only in rows 2 to 4)
Column 2 has the question/text itself (Only in rows 2 to 4)
Columns 3 to 7 have the checkboxes for appreciation, where column 3 is the
score of 5 and column 7 is the score of 1.
In row 5, probably the last cell, have a Text formfield with the properties
as follows: Fill-in not enabled, Numnber type witht he 0.00 number format.
The three checkboxes in column 1 have the sub "ToggleFill" set OnExit.
The 15 checkboxes in columns 3 to 7 have the sub "CalcAverage" set on exit.

I was not sure if you wanted an average of the score in all rows, or just a
total. My example calculates the average. Modify it as needed.


Option Explicit

Sub ToggleFill()

ActiveDocument.Unprotect Password:=""

With Selection.Cells(1).Range.FormFields(1).CheckBox
If .Value Then
With Selection.Cells(1).Range.Shading
.Texture = wdTextureNone
.BackgroundPatternColor = wdColorAutomatic
.ForegroundPatternColor = wdColorLightYellow
End With
Else
With Selection.Cells(1).Range.Shading
.Texture = wdTextureNone
.BackgroundPatternColor = wdColorAutomatic
.ForegroundPatternColor = wdColorWhite
End With
End If
End With

ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True,
Password:=""

End Sub

Sub CalcAverage()

Dim i As Long
Dim j As Long
Dim sngTotal As Single

With Selection.Tables(1)
For i = 2 To .Rows.Count - 1
For j = 1 To 5
If .Cell(i, j + 2).Range.FormFields(1).CheckBox.Value Then
sngTotal = sngTotal + ((j + 2) - (2 * (j - 2)))
Exit For
End If
Next
Next
End With

ActiveDocument.FormFields("txtAverage").Result = Format((sngTotal / (i -
2)), "#0.00")

End Sub


But, the checkboxes are not exclusive. You need another sub to make them
like that. See:
http://word.mvps.org/faqs/tblsfldsfms/ExclusiveFmFldChbxs.htm
If you use that sub, instead of using "CalcAverage" on exit of the 15
checkboxes, create a third macro that calls the other two and use that
instead.

If you use a password, type it in in the code between the two pairs of "" in
the "CalcAverage" sub. Also, manually set the password before testing the sub.

Warn users that they need to tab out of the last formfield to update the
Average result.

Finally, I believe you can "easily" modify my code to include column total
scores as well...

 

[Mifty]

Hi Jean-Guy,

Merci beaucoup, that's fantastic

I've had a quick look through but need to do other things so maybe I'll have
to do at home over the weekend.

I looked at the website for making checkboxes exclusive do I need to use it?
I'm assuming that if they are not exclusive then you could highlight every
cell on the table - is that right?

I'm sort of trying to duck out of using it because it said on the website
that the checkboxes couldn't be in a table that they would have to be in
frames. I haven't used frames before.

If I did need to use the code for exclusive checkboxes, could I put my text
into the frames?

Many many thanks

 

[Jean-Guy Marcil]

Hi Jean-Guy,

Merci beaucoup, that's fantastic

I've had a quick look through but need to do other things so maybe I'll have
to do at home over the weekend.

I looked at the website for making checkboxes exclusive do I need to use it?
I'm assuming that if they are not exclusive then you could highlight every
cell on the table - is that right?

I'm sort of trying to duck out of using it because it said on the website
that the checkboxes couldn't be in a table that they would have to be in
frames. I haven't used frames before.

If I did need to use the code for exclusive checkboxes, could I put my text
into the frames?

If you do not want to use frames (Which might be easier for you not to if
you have never used them...), then there are other ways to make the
checkboxes exclusive.
Just make sure that each checkbox is alone in a cell and has a unique name.
Try this code:

Dim strNameCheck As String
Dim celCheck As Cell

strNameCheck = Selection.Cells(1).Range.FormFields(1).Name

For Each celCheck In Selection.Rows(1).Cells
With celCheck.Range
If .FormFields.Count > 0 Then
If .FormFields(1).Type = wdFieldFormCheckBox Then
If .FormFields(1).Name <> strNameCheck Then
.FormFields(1).CheckBox.Value = False
End If
End If
End If
End With
Next

 

[Mifty]

Hi Jean-Guy,

I don't know how to thank you enough for all your help ))))

I'll play with these over the weekend and see how I get on.

Thank you so much !