Combo Boxes

[Lee Kiwiflame]

I have a table which has an Employees Rating and a Managers Rating in
separate cells, in each row. The rating is selected by a combo box. There
are 15 rows (therefore 30 combo boxes).

I am wanting to add the same items to each combo box. I know how to add the
items to individual combo boxes, e.g.

With ActiveDocument.ComboBox1
.AddItems "1"
.AddItems "2"
.AddItems "3"
End With

I don't want to have to add the above code for each Combo Box. Is there a
way to do this?

I am also wanting to get the value of each combo box and then shade cells
with a colour depending on the value of each combo box. (e.g. if an employee
selects 3 as a rating, the next 3 cells (next to the cell holding the combo
box) will be shaded red.

I'm wanting a form that is more "visual" than lists, combo boxes etc. I
don't want the form to look like people are filling out a questionaire.

Regards
Lee

 

[Greg Maxey]

Question 1.

Something like this:

Sub ScratchMacro()
Dim oILS As InlineShape
Dim oCtr As Object
For Each oILS In ActiveDocument.Range.InlineShapes
If oILS.Type = wdInlineShapeOLEControlObject Then
If InStr(oILS.OLEFormat.Object.Name, "ComboBox") > 0 Then
Set oCtr = oILS.OLEFormat.Object
With oCtr
.Clear
.AddItem "1"
.AddItem "2"
.AddItem "3"
End With
End If
End If
Next oILS
End Sub

 

[Jean-Guy Marcil]

I have a table which has an Employees Rating and a Managers Rating in
separate cells, in each row. The rating is selected by a combo box. There
are 15 rows (therefore 30 combo boxes).

I am wanting to add the same items to each combo box. I know how to add the
items to individual combo boxes, e.g.

With ActiveDocument.ComboBox1
.AddItems "1"
.AddItems "2"
.AddItems "3"
End With

I don't want to have to add the above code for each Combo Box. Is there a
way to do this?

Greg provided the code for that.

I am also wanting to get the value of each combo box and then shade cells
with a colour depending on the value of each combo box. (e.g. if an employee
selects 3 as a rating, the next 3 cells (next to the cell holding the combo
box) will be shaded red.

Here I assume that you have an 8 col x 15 row table.
The ActiveX comboboxes are in columns 1 and 5.

Each ActiveX must call the code.
If you have 30 ActiveX, you will need 30 Subs.
To make it more manageable, use something like this:
-------------------------------------------------
Private Sub ComboBox1_Change()

SetRed Selection.Cells(1).Range

End Sub
-------------------------------------------------
Private Sub ComboBox2_Change()

SetRed Selection.Cells(1).Range

End Sub
-------------------------------------------------
Private Sub ComboBox3_Change()

SetRed Selection.Cells(1).Range

End Sub
-------------------------------------------------
Private Sub ComboBox4_Change()

SetRed Selection.Cells(1).Range

End Sub
-------------------------------------------------
Private Sub ComboBox5_Change()

SetRed Selection.Cells(1).Range

End Sub
-------------------------------------------------
Etc.
-------------------------------------------------
Private Sub ComboBox16_Change()

SetRed Selection.Cells(1).Range

End Sub
-------------------------------------------------
Etc.
-------------------------------------------------
Sub SetRed(rgeCells As Range)

Dim oILS As InlineShape
Dim oCtr As Object
Dim i As Long

Set oILS = rgeCells.InlineShapes(1)
Set oCtr = oILS.OLEFormat.Object

i = CLng(oCtr.Value)

'remove previous red, in case user changes value
With rgeCells.Duplicate
.SetRange Selection.Rows(1).Cells(rgeCells.Columns(1) _
.Index + 1).Range.Start, Selection.Rows(1).Cells(rgeCells _
.Columns(1).Index + 3).Range.End
.Cells.Shading.BackgroundPatternColorIndex = wdNoHighlight
End With

'Apply red
With rgeCells
.SetRange Selection.Rows(1).Cells(rgeCells.Columns(1) _
.Index + 1).Range.Start, Selection.Rows(1).Cells(rgeCells _
.Columns(1).Index + i).Range.End
.Cells.Shading.BackgroundPatternColorIndex = wdRed
End With

End Sub
-------------------------------------------------