PDA

View Full Version : Solved: Help with Checkboxes and OptionButton



PSL
11-20-2010, 01:46 PM
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:-
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

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

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


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

Sub deleteboxes()
For Each c In Sheets("TargetSheet").CheckBoxes
c.Delete
Next
End Sub

Any Solutions?
Confused.

Cheers,
PSL

p45cal
11-20-2010, 05:42 PM
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.
That's because in answer to the prompt for a linked column you're giving a range address instead (red above), when you should just be entering B.

So when the code reaches:
.LinkedCell = linkedColumn & myCell.Row
in your case linkedColumn (a string) is "B1:B2"
mycell.row is first 1, then 2 in the second iteration of the loop.
so you get
B1:B21
and
B1:B22
as linked cells (not B1:B22 for both cells).

You may find the following a little easier. It uses a different sort of inputbox (prefixed with 'Application.') which allows you to define the type of input needed. Type 8 is a range address, and you can select the cells to input the address, and it returns a range.
I've used this for both range inputs, the linked column input will allow selection of just a single cell, a whole column, or a block of cells; it simply takes the leftmost column of whatever is selected ad the linked column.

I'll take a look at the rest later, btu I thought I'd post preliminary findings.

Sub insertCheckboxes()
Dim myBox As CheckBox
Dim myCell As Range
Dim cellRange As Range
Dim cboxLabel As String
Dim linkedColumn As Range
Set cellRange = Application.InputBox(Prompt:="Cell Range", Title:="Cell Range", Type:=8)
Set linkedColumn = Application.InputBox(Prompt:="Linked Column", Title:="Linked Column", Type:=8)
cboxLabel = InputBox(Prompt:="Checkbox Label", Title:="Checkbox Label")
With ActiveSheet
For Each myCell In cellRange.Cells
With myCell
Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, Width:=.Width, Left:=.Left, Height:=.Height)
With myBox
.LinkedCell = Cells(myCell.Row, linkedColumn.Column).Address
.Caption = cboxLabel
.Name = "checkbox_" & myCell.Address(0, 0)
.Display3DShading = True
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub
Both our code suffer from problems when you select a multicolumn range for the Cell Range input, all checkboxes on a given row linking to the same cell. Easily put right if necessary.

p45cal
11-20-2010, 06:27 PM
Option buttons are a little different in that they're grouped, and a group of option buttons collectively have just one linked cell, since choosing one option button, unselects the others. As you add checkboxes to the sheet they all become members of the same group. With this type of option button (from the Forms toolbar) grouping can be done by surrounding option buttons with a group box. ActiveX (from the Control Toolbox toolbar) option buttons can be grouped by a property called GroupName, assigned while placing them on the sheet. But each of this type of option button has a linked cell of its own, returning true or false.
This might mean doing option boxes for one question at a time - quite onerous if you have so many questions.
On the other hand, you might having large numbers of questions with the same number of option for each, in which case it might be easier to write code to do x option buttons per question.
So I won't suggest any option button code until I hear back.
In the meantime, here's some code to delete option buttons and checkboxes from a selected range:Sub deletecontrols()
Set RangeToEraseFrom = Application.InputBox("Delete which checkboxes/option buttons?", "Delete checkboxes and option buttons", , , , , , 8)
For Each c In ActiveSheet.CheckBoxes
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then c.Delete
Next
For Each c In ActiveSheet.OptionButtons
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then c.Delete
Next
End Sub

PSL
11-21-2010, 02:07 AM
p45cal: Both the macros work like a charm :)

That's essentially 3 of the 4 things that I was hoping to do.

As far as the option buttons are concerned, I would look at creating them for one question at a time. The number of questions is very large, but the number of option differ for each question. Beats creating a optionbuttons one by one anyway. :D

Any suggestions on how I should go about that?

Thanks and cheers,
PSL

PSL
11-21-2010, 02:44 AM
And yes, I just noticed the multicolumn error. They appear to be linking to the same cell.

p45cal
11-21-2010, 03:06 AM
Several possibilites. Some questions first.

A. For a given question:

how many option buttons might be needed?
how would those option buttons be arranged on the sheet for just that one question? B. How will you analyse the results?

I ask Q.B because of the different ways the two types of option button behave regarding linked cells.
A group of option buttons as used in the current code will have one linked cell common to all the buttons in that group with a single number in it, but will require programming the placing of a group box around each group of buttons (it can be made invisible) whereas...
if ActiveX option buttons are used each button will have its own linked cell with True/False in, so multiple cells per single question.

PSL
11-21-2010, 03:48 AM
Several possibilites. Some questions first.

A. For a given question:
how many option buttons might be needed?
how would those option buttons be arranged on the sheet for just that one question?B. How will you analyse the results?

I ask Q.B because of the different ways the two types of option button behave regarding linked cells.
A group of option buttons as used in the current code will have one linked cell common to all the buttons in that group with a single number in it, but will require programming the placing of a group box around each group of buttons (it can be made invisible) whereas...
if ActiveX option buttons are used each button will have its own linked cell with True/False in, so multiple cells per single question.
Well, each question could have a minimum of 2 option boxes (yes/no) or upto 15 option boxes each
Option Boxes would be arranged one below the other.

Analysis

The primary aim is to analyse the results once the survey is floated. Would be looking at all optionbuttons for a single question linking to a single cell. Would allow me to analyse the number of people selecting a certain option.

cheers

p45cal
11-21-2010, 06:10 AM
The code below first inserts a group box,then inserts option buttons. It asks for a starting cell and the number of option buttons wanted. The linked cell is on the same row as the starting cell.

Do you really want the option button captions/labels for a given question all the same? It could be tweaked so that it asks you to select a range of cells containing captions from which it will derive the number of option buttons needed and their labels. Particularly useful if you have many questions with the same/or similar options (you'd be able to select non-contiguous text cells).

There is a commented-out line (GB.Visible = False) which will hide the group boxes if that line is enabled. If you choose to make the group boxes invisible be careful you don't have overlapping ones - unpredictable results. This could easily happen if the row height that you're placing the buttons in is smaller than the minimum option button height and you place the next group in the cell directly below. Making your row heights more than the minimum option button height will solve this.

I've made a few changes to the deleting sub too, so that it deletes groupboxes associated with option buttons too. But don't, therefore, use it to delete just one or more option buttons within a group as it will delete the group box regardless. Delete those few manually instead.
Sub insertOptionButtons()
Dim myBox As OptionButton
Dim myCell As Range
Dim cellRange As Range
Dim cboxLabel As String
Dim linkedColumn As Range
Set cellRange = Application.InputBox(Prompt:="Select topmost cell", Title:="First Option Button", Type:=8)
Count = Application.InputBox(Prompt:="How many Option Buttons for this question?", Title:="How many Option Buttons", Type:=1)
Set cellRange = cellRange(1)
Set linkedColumn = Application.InputBox(Prompt:="Linked Column", Title:="Linked Column", Type:=8)
cboxLabel = InputBox(Prompt:="Checkbox Label", Title:="Checkbox Label")
Set cellRange = cellRange.Resize(Count)
With ActiveSheet
Set GB = .GroupBoxes.Add(Top:=cellRange.Top, Left:=cellRange.Left, Height:=cellRange.Height, Width:=cellRange.Width)
'GB.Visible = False
For Each myCell In cellRange
With myCell
Set myBox = .Parent.OptionButtons.Add(Top:=.Top, Width:=.Width, Left:=.Left, Height:=.Height)
With myBox
.LinkedCell = Cells(cellRange.Row, linkedColumn.Column).Address
.Caption = cboxLabel
.Name = "optButton_" & myCell.Address(0, 0)
.Display3DShading = True
End With
.NumberFormat = ";;;"
End With
Next myCell
'this next line is to extend the bottom of the groupbox downwards to include the bottommost option button.
'this will only happen if the row height is less than the minimum possible option button height
If myBox.Top + myBox.Height > GB.Top + GB.Height Then GB.Height = GB.Height + (myBox.Top + myBox.Height) - (GB.Top + GB.Height)
End With
End Sub

Sub deletecontrols()
Set RangeToEraseFrom = Application.InputBox("Delete which checkboxes/option buttons?", "Delete checkboxes and option buttons", , , , , , 8)
For Each c In ActiveSheet.CheckBoxes
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then c.Delete
Next
For Each c In ActiveSheet.OptionButtons
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then
Set GB = c.GroupBox
c.Delete
If Not GB Is Nothing Then GB.Delete
End If
Next
End Sub

PSL
11-21-2010, 06:49 AM
Hi,

There appears to be a couple of problems with this:

1. The first and the last option come out interlinked (Have attached a file)
2. Is it possible to have the group line to be transparent instead of a black line. Also the group name to be blank. If i try to remove the group box line, all the option buttons become linked to a single cell.

cheers, :)
PSL

p45cal
11-21-2010, 07:05 AM
Hi,

There appears to be a couple of problems with this:

1. The first and the last option come out interlinked (Have attached a file)Your file seems normal and everything works as it should. What version of Excel are you working on?
Is it corrected if you make the rows taller (than the minimum height of an option button) before you add the option boxes?

2. Is it possible to have the group line to be transparent instead of a black line. Also the group name to be blank. If i try to remove the group box line, all the option buttons become linked to a single cell.Already addressed:
There is a commented-out line (GB.Visible = False) which will hide the group boxes if that line is enabled. If you choose to make the group boxes invisible be careful you don't have overlapping ones - unpredictable results. This could easily happen if the row height that you're placing the buttons in is smaller than the minimum option button height and you place the next group in the cell directly below. Making your row heights more than the minimum option button height will solve this.

PSL
11-21-2010, 07:52 AM
I am using Excel 2003. I've attached a picture of the error. It selects the first and the last cell. They appear to be connected. This error only occurs in one group. For example if I make 4 such groups. The error would only be in the last group. Not a major problem. I can just make an extra group outside the print area once I'm done with all the optionboxes

Everything else works perfectly. Thanks

Also, you had mentioned that in the earlier macro (creating checkboxes), the macro was linking to a single cell in case I tried inserting checkboxes in multiple columns. Any suggestions on how to solve that?

Thanks a ton

PSL

p45cal
11-21-2010, 09:04 AM
I am using Excel 2003. I've attached a picture of the error. It selects the first and the last cell. They appear to be connected. This error only occurs in one group. For example if I make 4 such groups. The error would only be in the last group. Not a major problem. I can just make an extra group outside the print area once I'm done with all the optionboxesCan you answer this please?:
Is it corrected if you make the rows taller (than the minimum height of an option button) before you add the option boxes?

Also, you had mentioned that in the earlier macro (creating checkboxes), the macro was linking to a single cell in case I tried inserting checkboxes in multiple columns. Any suggestions on how to solve that?Where would you want such linked cells to be in relation to the checkboxes - I suggest under the checkboxes if you don't need to see them.

PSL
11-21-2010, 09:19 AM
Yes! It is corrected if I increase the height of the row to more than the minimum. Odd that it would happen before. Anyway, that wasn't such a problem anyway :)

<quote>
Where would you want such linked cells to be in relation to the checkboxes </quote>

Say I want to insert checkboxes in the range "A1:C12" and I mention to link to "E1:G12". All the checkboxes in a single row get linked to the same cell. You had mentioned earlier that it was an easy fix.

Also, as you have written the code for deleted all checkboxes/optionbuttons in a specifed range, is it possible to write something that would select the same and not delete them?

You have no idea about how much time you've saved me at work! :)


Thanks a ton (again)

cheers!
PSL

p45cal
11-21-2010, 12:21 PM
I'd appreciate a comment on an earlier suggestion:
Do you really want the option button captions/labels for a given question all the same? It could be tweaked so that it asks you to select a range of cells containing captions from which it will derive the number of option buttons needed and their labels. Particularly useful if you have many questions with the same/or similar options (you'd be able to select non-contiguous text cells). Otherwise:

Yes! It is corrected if I increase the height of the row to more than the minimum. Odd that it would happen before. Anyway, that wasn't such a problem anyway :)If you're happy with increasing the row height as a solution I won't bother trying to supply a coded solution.



Say I want to insert checkboxes in the range "A1:C12" and I mention to link to "E1:G12". All the checkboxes in a single row get linked to the same cell. You had mentioned earlier that it was an easy fix. change:.LinkedCell = Cells(myCell.Row, linkedColumn.Column).Address to:.LinkedCell = Cells(myCell.Row, linkedColumn.Column + (myCell.Column - cellRange.Column)).Address


Also, as you have written the code for deleted all checkboxes/optionbuttons in a specifed range, is it possible to write something that would select the same and not delete them?Sub SelectControls()
Set RangeToEraseFrom = Application.InputBox("Delete which checkboxes/option buttons?", "Delete checkboxes and option buttons", , , , , , 8)
For Each c In ActiveSheet.CheckBoxes
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then c.Select False
Next
For Each c In ActiveSheet.OptionButtons
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then
Set GB = c.GroupBox
c.Select False
If Not GB Is Nothing Then GB.Select False
End If
Next
For Each c In ActiveSheet.GroupBoxes
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then c.Select False
Next c
End Sub

p45cal
11-21-2010, 02:05 PM
Oops, regarding the SelectControls sub I posted in the last message, it will fail if you've hidden the group boxes so an update:Sub SelectControls()
Set RangeToEraseFrom = Application.InputBox("Select which checkboxes/option buttons?", "Select checkboxes, option buttons & groupboxes", , , , , , 8)
For Each c In ActiveSheet.CheckBoxes
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then c.Select False
Next
For Each c In ActiveSheet.OptionButtons
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then
Set GB = c.GroupBox
c.Select False
If Not GB Is Nothing Then GB.Visible = True: GB.Select False
End If
Next
For Each c In ActiveSheet.GroupBoxes
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then c.Select False
Next c
End Sub
In order to be able to select these invisible GroupBoxes it has to make them visible. Presumably you want to select them to move them around a bit, so later you're going to want to hide these groupboxes again so here's a utility for that:Sub HideGroupBoxes()
Set RangeToEraseFrom = Application.InputBox("Hide which GroupBoxes?", "Hide GroupBoxes", , , , , , 8)
For Each c In ActiveSheet.GroupBoxes
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then c.Visible = False
Next
End Sub
along with a similar routine that shows any invisible groupboxes:Sub ShowGroupBoxes()
Set RangeToEraseFrom = Application.InputBox("Hide which GroupBoxes?", "Hide GroupBoxes", , , , , , 8)
For Each c In ActiveSheet.GroupBoxes
If Not Intersect(c.TopLeftCell, RangeToEraseFrom) Is Nothing Then c.Visible = True
Next
End Sub

ps. I think I've cracked what you called interlinking, so you'll be able to add option buttons to rows which are not as tall as the optionbuttons. If you want I'll post it. Incidentally, it also remembers a default linked cell column (the last one you used) so if you're adding a bunch of questions and you want them all to have linked cells in the same column you'll just have to press Enter/click OK to keep the same column (I got tired of clicking while testing). I could do the same for numbers of checkboxes too, if my previous suggestion of automatically adding option button labels from a range of text containing cells is of no interest.

PSL
11-22-2010, 01:19 AM
Brilliant!

Have tested all the codes and they work perfectly :D

The interlinking problem isn't too much of a bother.

I should probably mark this one as solved. Thanks again. The next time you're in town, the drink's on me :)

cheers!
PSL