OptionButton not included in a GroupBox

[Flexx]

Hello

I have a problem with my OptionButtons and my GroupBoxes.

In a form, which is a kind of survey, GroupBoxes and OptionButtons ar
automatically generated, depending on the number of questions.

Each OptionButton must fit in a cell, and a GroupBox has to include man
OptionButtons.

But there is a problem. Some OptionButtons are not included in th
GroupBox
So, it is possible to select more than one option button into the grou
!

You can see it on the following capture :
http://img15.hostingpics.net/pics/473486PbGroupBoxes.png

Or this one :
http://img15.hostingpics.net/pics/364359PbExcel.png


You can play with the attached file to see the problem.

Or use the following code :

Sub Bouton1_Clic()

Dim myCell As Range
Dim numCell As Integer
Dim myGroupBox As GroupBox
Dim myOptionButton As OptionButton

ActiveSheet.OptionButtons.Delete
ActiveSheet.GroupBoxes.Delete

numCell = 0
For Each myCell In ActiveSheet.Range("E2:E12").Cells
If numCell = 0 Then
Set myGroupBox = ActiveSheet.GroupBoxes.Add(myCell.Left
myCell.Top, myCell.Width, (myCell.Height * 3))
myGroupBox.Caption = ""
End If
numCell = numCell + 1
If numCell = 3 Then numCell = 0
Next

numCell = 0
For Each myCell In ActiveSheet.Range("E2:E13").Cells
Set myOptionButton = ActiveSheet.OptionButtons.Add(myCell.Left
myCell.Top, 5, 5)
myOptionButton.Caption = ""
Next

End Sub


Thanks a lot for your help

+-------------------------------------------------------------------
|Filename: PbOptionButton.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=317
+-------------------------------------------------------------------

 

[Dave Peterson]

First, I didn't download your attachment.

But when I looked at your code, you're using the height of the cell times 3.
That looks like it would be more than your single rowheight (overlapping the
row(s) below).

I'd change that to be exactly the height of the row to start my testing.

And I'd look at Debra Dalgleish's site to see if there's any hints/tips that I
could use:

http://contextures.com/xlForm01.html

ps.

Is there a reason you use E2:E12 in one loop, but E2:E13 in the other?

 

[Flexx]

In fact, the reason of the problem is that the groupbox didn't totall
enclose the last OptionButton
So this button was assigned in a new groupbox, automatically created t
manage the OptionButtons not included in a groupBox

That's why I could select more than 1 optionButton in a group, an
that's also why if I click on an optionButton of a group it coul
uncheck an optionButton of another group (these optionButton wer
assigned to the default groupBox because they were not enclosed in th
groupBoxes I created)

I don't know if it's clear enough :

So the solution was to make a little bigger my groupBoxes to be sure t
enclose all the optionButton I want, but not too big to not include th
optionButtons of another group

It works pretty well, excepted with some small zoom. But it's ok for me

Before posting my problem, I tried the Debra Dalgleish's survey
http://contextures.com/xlForm01.htm
But she has the same problem :
If you configure the sheet zoom to 55%, and then execute the macr
"setupSurvey", you can see that you can select the 2 last optionButton
of each row
And if you select the last OptionButton of another row, it will unchec
another one, because they are not enclose in the groupBox created by th
macro, but there are in a groupBox automatically created to manage th
"orphan" OptionButtons

Thank you for your help

;1600440']First, I didn't download your attachment

But when I looked at your code, you're using the height of the cel
times 3.
That looks like it would be more than your single rowheight
(overlapping the
row(s) below)

I'd change that to be exactly the height of the row to start my testing

And I'd look at Debra Dalgleish's site to see if there's any hints/tip
that I
could use

http://contextures.com/xlForm01.htm

ps

Is there a reason you use E2:E12 in one loop, but E2:E13 in the other

Hell

I have a problem with my OptionButtons and my GroupBoxes

In a form, which is a kind of survey, GroupBoxes and OptionButtons ar
automatically generated, depending on the number of questions

Each OptionButton must fit in a cell, and a GroupBox has to includ man

But there is a problem. Some OptionButtons are not included in th
GroupBox :
So, it is possible to select more than one option button into th grou

You can see it on the following capture
http://img15.hostingpics.net/pics/473486PbGroupBoxes.pn

Or this one
http://img15.hostingpics.net/pics/364359PbExcel.pn

You can play with the attached file to see the problem

Or use the following code

Sub Bouton1_Clic(

Dim myCell As Rang
Dim numCell As Intege
Dim myGroupBox As GroupBo
Dim myOptionButton As OptionButto

numCell =
For Each myCell In ActiveSheet.Range("E2:E12").Cell
If numCell = 0 The
Set myGroupBox = ActiveSheet.GroupBoxes.Add(myCell.Left
myCell.Top, myCell.Width, (myCell.Height * 3)
myGroupBox.Caption = "
End I
numCell = numCell +
If numCell = 3 Then numCell =
Nex

numCell =
For Each myCell In ActiveSheet.Range("E2:E13").Cell
Set myOptionButton = ActiveSheet.OptionButtons.Add(myCell.Left
myCell.Top, 5, 5
myOptionButton.Caption = "
Nex

End Su

Thanks a lot for your help

+-------------------------------------------------------------------
|Filename: PbOptionButton.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=317
+-------------------------------------------------------------------

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Dave Peterson]

I've had trouble when the zoom wasn't set to 100%.

My solution was to change the zoom to 100%, run the code to add the
buttons/groupboxes and then change the zoom back.

I can't remember when it hasn't worked.

In fact, the reason of the problem is that the groupbox didn't totally
enclose the last OptionButton.
So this button was assigned in a new groupbox, automatically created to
manage the OptionButtons not included in a groupBox.

That's why I could select more than 1 optionButton in a group, and
that's also why if I click on an optionButton of a group it could
uncheck an optionButton of another group (these optionButton were
assigned to the default groupBox because they were not enclosed in the
groupBoxes I created).

I don't know if it's clear enough

So the solution was to make a little bigger my groupBoxes to be sure to
enclose all the optionButton I want, but not too big to not include the
optionButtons of another group.

It works pretty well, excepted with some small zoom. But it's ok for me.

Before posting my problem, I tried the Debra Dalgleish's survey :
http://contextures.com/xlForm01.html
But she has the same problem
If you configure the sheet zoom to 55%, and then execute the macro
"setupSurvey", you can see that you can select the 2 last optionButtons
of each row !
And if you select the last OptionButton of another row, it will uncheck
another one, because they are not enclose in the groupBox created by the
macro, but there are in a groupBox automatically created to manage the
"orphan" OptionButtons.

Thank you for your help.

;1600440']First, I didn't download your attachment.

But when I looked at your code, you're using the height of the cell
times 3.
That looks like it would be more than your single rowheight
(overlapping the
row(s) below).

I'd change that to be exactly the height of the row to start my testing.

And I'd look at Debra Dalgleish's site to see if there's any hints/tips
that I
could use:

http://contextures.com/xlForm01.html

ps.

Is there a reason you use E2:E12 in one loop, but E2:E13 in the other?

On 04/02/2012 08:56, Flexx wrote:-

Hello

I have a problem with my OptionButtons and my GroupBoxes.

In a form, which is a kind of survey, GroupBoxes and OptionButtons are
automatically generated, depending on the number of questions.

Each OptionButton must fit in a cell, and a GroupBox has to include many
OptionButtons.

But there is a problem. Some OptionButtons are not included in the
GroupBox
So, it is possible to select more than one option button into the group
!

You can see it on the following capture :
http://img15.hostingpics.net/pics/473486PbGroupBoxes.png

Or this one :
http://img15.hostingpics.net/pics/364359PbExcel.png


You can play with the attached file to see the problem.

Or use the following code :

Sub Bouton1_Clic()

Dim myCell As Range
Dim numCell As Integer
Dim myGroupBox As GroupBox
Dim myOptionButton As OptionButton

ActiveSheet.OptionButtons.Delete
ActiveSheet.GroupBoxes.Delete

numCell = 0
For Each myCell In ActiveSheet.Range("E2:E12").Cells
If numCell = 0 Then
Set myGroupBox = ActiveSheet.GroupBoxes.Add(myCell.Left,
myCell.Top, myCell.Width, (myCell.Height * 3))
myGroupBox.Caption = ""
End If
numCell = numCell + 1
If numCell = 3 Then numCell = 0
Next

numCell = 0
For Each myCell In ActiveSheet.Range("E2:E13").Cells
Set myOptionButton = ActiveSheet.OptionButtons.Add(myCell.Left,
myCell.Top, 5, 5)
myOptionButton.Caption = ""
Next

End Sub


Thanks a lot for your help.


+-------------------------------------------------------------------+
|Filename: PbOptionButton.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=317|
+-------------------------------------------------------------------+


-

+-------------------------------------------------------------------+
+-------------------------------------------------------------------+

 

[GS]

I've often experienced many different (and unpredictable) results using
controls/shapes on worksheets when rows/cols need to be hidden or zoom
is changed. I've swithched to using worksheet events that manage
specific cells so as they look/behave like buttons or checkboxes. I use
conditional DV dropdowns for option selections where options are listed
but not enabled. Otherwise, the DV dropdowns are dependant on a list
which may or may not be dependant on another dropdown. Lots of work to
figure it all out but worth it in the end <IMO> because my worksheet
forms behave as expected when I don't need to use Form/ActiveX
controls. Works very nice when you need a worksheet to behave like a
userform wizard!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion