Sub SetupSurveyForm_1()
'code written by Dave Peterson 2005-10-27
'creates a survey form with option buttons
'http://www.contextures.com/xlForm01.html
'
Dim grpBox As GroupBox
'
'
Dim optBtn As OptionButton
'
Dim maxBtns As Long
Dim myCell As Range
Dim myRange As Range
Dim wks As Worksheet
Dim iCtr As Long
'
Dim OptBtnCell1 As Range
'
Dim NumberOfQuestions As Long
Dim myBorders As Variant
myBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, _
xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
maxBtns = 5
NumberOfQuestions = 16
Set wks = ActiveSheet
With wks
Set OptBtnCell1 = .Range("h26")
With OptBtnCell1.Offset(-1, 0).Resize(1, maxBtns)
.Value = Array("Not Well", "A little", _
"OK", "Well", "Very Well")
.Orientation = 90
.HorizontalAlignment = xlCenter
End With
Set myRange = OptBtnCell1.Resize(NumberOfQuestions, 1)
With myRange.Offset(0, 14)
.FormulaR1C1 = "=rc[1]*1"
End With
myRange.EntireRow.RowHeight = 28
myRange.Resize(, maxBtns).EntireColumn.ColumnWidth = 4
End With
For Each myCell In myRange
With myCell.Resize(1, maxBtns)
Set grpBox = wks.GroupBoxes.Add _
(Top:=.Top, Left:=.Left, Height:=.Height, _
Width:=.Width)
With grpBox
.Caption = ""
.Visible = True 'False
End With
End With
For iCtr = 0 To maxBtns - 1
With myCell.Offset(0, iCtr)
Set optBtn = wks.OptionButtons.Add _
(Top:=.Top, Left:=.Left, Height:=.Height, _
Width:=.Width)
optBtn.Caption = ""
If iCtr = 0 Then
With myCell.Offset(0, 14)
optBtn.LinkedCell = .Address(external:=True)
End With
End If
End With
Next iCtr
Next myCell
End Sub
Sub SetupSurveyForm_2()
'code written by Dave Peterson 2005-10-27
'creates a survey form with option buttons
'http://www.contextures.com/xlForm01.html
'
Dim grpBox2 As GroupBox
Dim optBtn2 As OptionButton
Dim maxBtns2 As Long
Dim myCell2 As Range
Dim myRange2 As Range
Dim wks As Worksheet
Dim iCtr2 As Long
Dim OptBtnCell2 As Range
Dim NumberOfQuestions2 As Long
Dim myBorders As Variant
myBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, _
xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
maxBtns2 = 5
NumberOfQuestions2 = 16
Set wks = ActiveSheet
With wks
Set OptBtnCell2 = .Range("n26")
With OptBtnCell2.Offset(-1, 0).Resize(1, maxBtns2)
.Value = Array("Not Well", "A little", _
"OK", "Well", "Very Well")
.Orientation = 90
.HorizontalAlignment = xlCenter
End With
Set myRange2 = OptBtnCell2.Resize(NumberOfQuestions2, 1)
With myRange2.Offset(0, 9)
.FormulaR1C1 = "=rc[1]*1"
End With
myRange2.EntireRow.RowHeight = 28
myRange2.Resize(, maxBtns2).EntireColumn.ColumnWidth = 4
End With
For Each myCell2 In myRange2
With myCell2.Resize(1, maxBtns2)
Set grpBox2 = wks.GroupBoxes.Add _
(Top:=.Top, Left:=.Left, Height:=.Height, _
Width:=.Width)
With grpBox2
.Caption = ""
.Visible = True 'False
End With
End With
For iCtr2 = 0 To maxBtns2 - 1
With myCell2.Offset(0, iCtr2)
Set optBtn2 = wks.OptionButtons.Add _
(Top:=.Top, Left:=.Left, Height:=.Height, _
Width:=.Width)
optBtn2.Caption = ""
If iCtr2 = 0 Then
With myCell2.Offset(0, 9)
optBtn2.LinkedCell = .Address(external:=True)
End With
End If
End With
Next iCtr2
Next myCell2
End Sub
Bookmarks