Hi,
I am trying to write a macro to help me with checkboxes and OptionButtons that I need to create for a office project.
A worksheet has a lot of questions with multiple options. Some of these options have to be answered using checkboxes, while some have option buttons.
Since the question are nearly thousand with differing number of options, I'm trying write a few macros to simplify the process.
Here is what I'm trying to do:
1. Insert Multiple Checkboxes by specifying a given range and linking them
2. Delete Checkboxes in a given range (if something goes wrong)
3. Inserting Multiple OptionsButtons and linking them
4. Deleting OptionButtons in a given range.
Here is what I am using till now
For the Checkbox:-
[VBA]Sub insertCheckboxes()
Dim myBox As CheckBox
Dim myCell As Range
Dim cellRange As String
Dim cboxLabel As String
Dim linkedColumn As String
cellRange = InputBox(Prompt:="Cell Range", _
Title:="Cell Range")
linkedColumn = InputBox(Prompt:="Linked Column", _
Title:="Linked Column")
cboxLabel = InputBox(Prompt:="Checkbox Label", _
Title:="Checkbox Label")
With ActiveSheet
For Each myCell In .Range(cellRange).Cells
With myCell
Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
Width:=.Width, Left:=.Left, Height:=.Height)
With myBox
.LinkedCell = linkedColumn & myCell.Row
.Caption = cboxLabel
.Name = "checkbox_" & myCell.Address(0, 0)
.Display3DShading = True
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub[/VBA]
This macro works fine for adding the boxes. However the linking is faulty while adding multiple checkboxes. Eg. If I add checkboxed giving range A1:A2 and link range B1:B2, the boxes appear, but with link range B1:B22 for both checkboxes. Since I would need to analyse the results I need the linking to be spot on.
Similarly for OptionButtons
[VBA]Sub insertOptionButtons()
Dim myBox As OptionButton
Dim myCell As Range
Dim cellRange As String
Dim cboxLabel As String
Dim linkedColumn As String
cellRange = InputBox(Prompt:="Cell Range", _
Title:="Cell Range")
linkedColumn = InputBox(Prompt:="Linked Column", _
Title:="Linked Column")
cboxLabel = InputBox(Prompt:="Checkbox Label", _
Title:="Checkbox Label")
With ActiveSheet
For Each myCell In .Range(cellRange).Cells
With myCell
Set myBox = .Parent.OptionButtons.Add(Top:=.Top, _
Width:=1.25, Left:=.Left, Height:=.Height)
With myBox
.LinkedCell = linkedColumn & myCell.Row
.Caption = cboxLabel
.Name = "checkbox_" & myCell.Address(0, 0)
.Display3DShading = True
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub
[/VBA]
basically a modification of the macro for chexboxes as above.
Could not come up with anything to delete optionbuttons or checkboxes in a given range. Though did write a simple code to delete all checkboxes on a given page. That however does not solve my purpose. Here it is anyway
[VBA]Sub deleteboxes()
For Each c In Sheets("TargetSheet").CheckBoxes
c.Delete
Next
End Sub[/VBA]
Any Solutions?
Confused.
Cheers,
PSL