Macro to shade cells in a "protected" form

[GoKats78]

I have form which contains a table..using form fields and checkboxes. What I am trying to do is, when a certain checkbox is checked a group of cells is shaded (they are no needed to be completed if the checkbox is checked).
The first step is unprotect the form...but I am not even sure who to do that one...HELP!

[gmaxey]

Assumes the checkbox is "Check1" and the target cells are in column 3 and each target cell contains on formfield. Set CBOnExit to run on exit from Check1.

Sub CBOnExit()
Dim oCell As Cell
If ActiveDocument.FormFields("Check1").CheckBox.Value = True Then
ActiveDocument.Unprotect
For Each oCell In ActiveDocument.Tables(1).Columns(3).Cells
oCell.Range.Shading.BackgroundPatternColorIndex = wdBrightGreen
oCell.Range.FormFields(1).Enabled = False
Next
ActiveDocument.Protect wdAllowOnlyFormFields, True
Else
ActiveDocument.Unprotect
For Each oCell In ActiveDocument.Tables(1).Columns(3).Cells
oCell.Range.Shading.BackgroundPatternColorIndex = wdAuto
oCell.Range.FormFields(1).Enabled = True
Next
ActiveDocument.Protect wdAllowOnlyFormFields, True
End If
End Sub

[GoKats78]

That's a great start! Thanks..now if it were only as easy as all the cells in Column 3! I have cells all over the form! Looks like i have some thinking and playing around to do!

[gmaxey]

You can create an array of cell indexes and process specific cells:

Sub ScratchMacro()
'A quick macro scratch pad created by Greg Maxey
Dim arrCellIndexes(4, 1) As String
Dim i As Long
arrCellIndexes(0, 0) = 1 'Row 1
arrCellIndexes(0, 1) = 1 'Column 1
arrCellIndexes(1, 0) = 1 'Row 1
arrCellIndexes(1, 1) = 5 'Column 5
arrCellIndexes(2, 0) = 3
arrCellIndexes(2, 1) = 3
arrCellIndexes(3, 0) = 4
arrCellIndexes(3, 1) = 4
arrCellIndexes(4, 0) = 5
arrCellIndexes(4, 1) = 5
For i = 0 To UBound(arrCellIndexes)
ActiveDocument.Tables(1).Cell(arrCellIndexes(i, 0), arrCellIndexes(i, 1)).Range.Shading.BackgroundPatternColorIndex = wdBrightGreen
Next i
End Sub

[GoKats78]

That is what I was working on...in my simple minded ways!

[GoKats78]

Figured it out! Thanks for you help, Greg!

[GoKats78]

OK, I got everything I needed to be shaded when the box is checked..now...To "un-shade" everything if they un-check the box....

Here is what I have (minus most of the cell references...there are a lot of them!)

Sub CBOnExit1()
Dim oCell As Cell
If ActiveDocument.FormFields("Check1").CheckBox.Value = True Then
ActiveDocument.unprotect


Dim arrCellIndexes(22, 1) As String
Dim i As Long

arrCellIndexes(0, 0) = 9
arrCellIndexes(0, 1) = 1
arrCellIndexes(1, 0) = 9
arrCellIndexes(1, 1) = 2
For i = 0 To UBound(arrCellIndexes)
ActiveDocument.Tables(1).Cell(arrCellIndexes(i, 0), arrCellIndexes(i, 1)).Range.Shading.BackgroundPatternColorIndex = wdGray50:



Next i
ActiveDocument.Protect wdAllowOnlyFormFields, True
End If

End Sub


I thought about using the same code sequence and using white as the shade color of the box is unchecked (If ActiveDocument.FormFields("Check1").CheckBox.Value = false), but couldn't figure out where to put the it!

Also, as I have two check boxes (only one should be checked) how can I set it so if one is checked the other cannot be?

[gmaxey]

Use something like this:

Option Explicit
Sub CBOnExit1()
Dim oCell As Cell
Dim arrCellIndexes(1, 1) As String
'Dim arrCellIndexes(22, 1) As String
Dim i As Long

arrCellIndexes(0, 0) = 9
arrCellIndexes(0, 1) = 1
arrCellIndexes(1, 0) = 9
arrCellIndexes(1, 1) = 2

With ActiveDocument
.Unprotect
If .FormFields("Check1").CheckBox.Value = True Then
.FormFields("Check2").CheckBox.Value = False
For i = 0 To UBound(arrCellIndexes)
.Tables(1).Cell(arrCellIndexes(i, 0), arrCellIndexes(i, 1)).Range.Shading.BackgroundPatternColorIndex = wdGray50
Next i
Else
For i = 0 To UBound(arrCellIndexes)
.Tables(1).Cell(arrCellIndexes(i, 0), arrCellIndexes(i, 1)).Range.Shading.BackgroundPatternColorIndex = wdAuto
Next i
End If
.Protect wdAllowOnlyFormFields, True
End With

End Sub

[GoKats78]

Got it!

[GoKats78]

One last question...if there is a password on the form how do i put that in the code?

[GoKats78]

Figured it out...