The clicked radio button in a UserForm does not clear so clicking the same
button again does not work, whereas clicking another radio button next does
work.



Is there an instruction I should incorporate to clear the previous clicking
of the button? If so what and where should it go?



Francis Hookham



--------------------------------------------------------------



Private Sub OptionButton6_Click()

'colour frame(s) orange

Userform1.Hide

ColourOrange

End Sub



Sub ColourOrange()

FindFrameRow

'set colour

ActiveWorkbook.Colors(21) = RGB(255, 245, 225)

FillColour = 21

OneShotColour

End Sub



Sub FindFrameRow()

If ActiveCell.Row = 1 Or ActiveCell.Row = 2 Then

RowNo = 3

Else

RowNo = ActiveCell.Row

While Cells(RowNo, 1) = ""

RowNo = RowNo - 1

Wend

End If

Cells(RowNo, 1).Select

End Sub



Sub OneShotColour()

'given 'RowNo', this performs the colour change of one shot

Range(Cells(RowNo, 2), Cells(RowNo + 15, 10)). _

Interior.ColorIndex = FillColour

Cells(RowNo, 7).Interior.ColorIndex = xlNone

Cells(RowNo, 9).Interior.ColorIndex = xlNone

End Sub

set it to false

userform1.optionbutton1=false

--

Gary
Excel 2003


"Francis Hookham" wrote in message
...
The clicked radio button in a UserForm does not clear so clicking the same
button again does not work, whereas clicking another radio button next
does work.



Is there an instruction I should incorporate to clear the previous
clicking of the button? If so what and where should it go?



Francis Hookham



--------------------------------------------------------------



Private Sub OptionButton6_Click()

'colour frame(s) orange

Userform1.Hide

ColourOrange

End Sub



Sub ColourOrange()

FindFrameRow

'set colour

ActiveWorkbook.Colors(21) = RGB(255, 245, 225)

FillColour = 21

OneShotColour

End Sub



Sub FindFrameRow()

If ActiveCell.Row = 1 Or ActiveCell.Row = 2 Then

RowNo = 3

Else

RowNo = ActiveCell.Row

While Cells(RowNo, 1) = ""

RowNo = RowNo - 1

Wend

End If

Cells(RowNo, 1).Select

End Sub



Sub OneShotColour()

'given 'RowNo', this performs the colour change of one shot

Range(Cells(RowNo, 2), Cells(RowNo + 15, 10)). _

Interior.ColorIndex = FillColour

Cells(RowNo, 7).Interior.ColorIndex = xlNone

Cells(RowNo, 9).Interior.ColorIndex = xlNone

End Sub

Thank you Gary

"Gary Keramidas" wrote in message
...
set it to false

userform1.optionbutton1=false

--

Gary
Excel 2003


"Francis Hookham" wrote in message
...
The clicked radio button in a UserForm does not clear so clicking the
same button again does not work, whereas clicking another radio button
next does work.



Is there an instruction I should incorporate to clear the previous
clicking of the button? If so what and where should it go?



Francis Hookham



--------------------------------------------------------------



Private Sub OptionButton6_Click()

'colour frame(s) orange

Userform1.Hide

ColourOrange

End Sub



Sub ColourOrange()

FindFrameRow

'set colour

ActiveWorkbook.Colors(21) = RGB(255, 245, 225)

FillColour = 21

OneShotColour

End Sub



Sub FindFrameRow()

If ActiveCell.Row = 1 Or ActiveCell.Row = 2 Then

RowNo = 3

Else

RowNo = ActiveCell.Row

While Cells(RowNo, 1) = ""

RowNo = RowNo - 1

Wend

End If

Cells(RowNo, 1).Select

End Sub



Sub OneShotColour()

'given 'RowNo', this performs the colour change of one shot

Range(Cells(RowNo, 2), Cells(RowNo + 15, 10)). _

Interior.ColorIndex = FillColour

Cells(RowNo, 7).Interior.ColorIndex = xlNone

Cells(RowNo, 9).Interior.ColorIndex = xlNone

End Sub

When radio buttons are together in a group, like in a Frame, then clicking
one will automatically clear the other. So it sounds like your Option
Buttons aren't properly grouped with each other.
--

Toby Erkson
http://excel.icbm.org/


"Francis Hookham" wrote:

The clicked radio button in a UserForm does not clear so clicking the same
button again does not work, whereas clicking another radio button next does
work.



Is there an instruction I should incorporate to clear the previous clicking
of the button? If so what and where should it go?



Francis Hookham



--------------------------------------------------------------



Private Sub OptionButton6_Click()

'colour frame(s) orange

Userform1.Hide

ColourOrange

End Sub



Sub ColourOrange()

FindFrameRow

'set colour

ActiveWorkbook.Colors(21) = RGB(255, 245, 225)

FillColour = 21

OneShotColour

End Sub



Sub FindFrameRow()

If ActiveCell.Row = 1 Or ActiveCell.Row = 2 Then

RowNo = 3

Else

RowNo = ActiveCell.Row

While Cells(RowNo, 1) = ""

RowNo = RowNo - 1

Wend

End If

Cells(RowNo, 1).Select

End Sub



Sub OneShotColour()

'given 'RowNo', this performs the colour change of one shot

Range(Cells(RowNo, 2), Cells(RowNo + 15, 10)). _

Interior.ColorIndex = FillColour

Cells(RowNo, 7).Interior.ColorIndex = xlNone

Cells(RowNo, 9).Interior.ColorIndex = xlNone

End Sub

Thanks for your comment Toby. Although an XL user (inc Multiplan) days it
has been as a self taught amateur, not good at reading complicated text
books. Can you point me to a simple explanation of the need for grouping
radio buttons.



I have grouped the buttons and using Gary's

userform1.optionbutton1=false

all is well.



Thank you also for the link to your website - it is now a Links bar
favourite named 'XL' so I can keep an eye on you and get to the very useful
Links page - recently, by mistake, I wiped all my links to those amazing
MVPs.



Thanks from Cambridge, UK



Francis




"Air_Cooled_Nut" wrote in message
...
When radio buttons are together in a group, like in a Frame, then clicking
one will automatically clear the other. So it sounds like your Option
Buttons aren't properly grouped with each other.
--

Toby Erkson
http://excel.icbm.org/


"Francis Hookham" wrote:

The clicked radio button in a UserForm does not clear so clicking the
same
button again does not work, whereas clicking another radio button next
does
work.



Is there an instruction I should incorporate to clear the previous
clicking
of the button? If so what and where should it go?



Francis Hookham



--------------------------------------------------------------



Private Sub OptionButton6_Click()

'colour frame(s) orange

Userform1.Hide

ColourOrange

End Sub



Sub ColourOrange()

FindFrameRow

'set colour

ActiveWorkbook.Colors(21) = RGB(255, 245, 225)

FillColour = 21

OneShotColour

End Sub



Sub FindFrameRow()

If ActiveCell.Row = 1 Or ActiveCell.Row = 2 Then

RowNo = 3

Else

RowNo = ActiveCell.Row

While Cells(RowNo, 1) = ""

RowNo = RowNo - 1

Wend

End If

Cells(RowNo, 1).Select

End Sub



Sub OneShotColour()

'given 'RowNo', this performs the colour change of one shot

Range(Cells(RowNo, 2), Cells(RowNo + 15, 10)). _

Interior.ColorIndex = FillColour

Cells(RowNo, 7).Interior.ColorIndex = xlNone

Cells(RowNo, 9).Interior.ColorIndex = xlNone

End Sub

Okay, I'll try to explain but there's one book I would recommend, "it's Excel
2002 Power Programming with VBA", ISBN: 0-7645-4799-2 (there's a 2003 version
but apparently there wasn't much extra added). This book alone taught me so
much; it's an EXCELLENT resource and very well written!

The radio button, technically known as the Option Button. Create a Frame
(click on Frame in the Toolbox in the VBA editor) on the user form and place
your option buttons inside it. This will group those option buttons so only
one will be True at any time when clicked. When another one is clicked the
other will one will automatically clear (become False). It does not matter
how many option buttons you have in a Frame (or group).

The other method doesn't require using a Frame. The option buttons are
placed on the user form. For each option button you want to be grouped
together (so when one is selected the other(s) are deselected), select it,
and in the Properties window set the GroupName to a common string
(descriptor). For example, if you have two option buttons on the form and
you want them to "see" each other, set the GroupName for both of them to the
same value, maybe "Gender" if one button is "Male" and the other button is
"Female".

I recommend using a Frame because it visually groups the option buttons --
or any other group of controls -- for the user. The Frame has a caption,
too. In our above example, you could set the Caption of the Frame to "What
is your gender?", thus instantly allowing the user to know what or why they
are selecting. Also, there's no need to set the GroupName for the option
buttons in a Frame :-) Does this help?

Yes, the MVP's are life-savers! I truely appreciate their dedicated
talents. The list of links I have is not complete but it contains the ones I
use most when I go from job to job.

--

Toby Erkson
http://excel.icbm.org/


"Francis Hookham" wrote:

Thanks for your comment Toby. Although an XL user (inc Multiplan) days it
has been as a self taught amateur, not good at reading complicated text
books. Can you point me to a simple explanation of the need for grouping
radio buttons.



I have grouped the buttons and using Gary's

userform1.optionbutton1=false

all is well.



Thank you also for the link to your website - it is now a Links bar
favourite named 'XL' so I can keep an eye on you and get to the very useful
Links page - recently, by mistake, I wiped all my links to those amazing
MVPs.



Thanks from Cambridge, UK



Francis




"Air_Cooled_Nut" wrote in message
...
When radio buttons are together in a group, like in a Frame, then clicking
one will automatically clear the other. So it sounds like your Option
Buttons aren't properly grouped with each other.
--

Toby Erkson
http://excel.icbm.org/


"Francis Hookham" wrote:

The clicked radio button in a UserForm does not clear so clicking the
same
button again does not work, whereas clicking another radio button next
does
work.



Is there an instruction I should incorporate to clear the previous
clicking
of the button? If so what and where should it go?



Francis Hookham



--------------------------------------------------------------



Private Sub OptionButton6_Click()

'colour frame(s) orange

Userform1.Hide

ColourOrange

End Sub



Sub ColourOrange()

FindFrameRow

'set colour

ActiveWorkbook.Colors(21) = RGB(255, 245, 225)

FillColour = 21

OneShotColour

End Sub



Sub FindFrameRow()

If ActiveCell.Row = 1 Or ActiveCell.Row = 2 Then

RowNo = 3

Else

RowNo = ActiveCell.Row

While Cells(RowNo, 1) = ""

RowNo = RowNo - 1

Wend

End If

Cells(RowNo, 1).Select

End Sub



Sub OneShotColour()

'given 'RowNo', this performs the colour change of one shot

Range(Cells(RowNo, 2), Cells(RowNo + 15, 10)). _

Interior.ColorIndex = FillColour

Cells(RowNo, 7).Interior.ColorIndex = xlNone

Cells(RowNo, 9).Interior.ColorIndex = xlNone

End Sub

Thanks again Toby - I hesitate to admit that I have Bullen, Green, Bovey and
Rosenberg's 'Excel 2002 VBA' (ISBN 1-861005-70-9) but is is so heavy going
that I seldom refer to it. I also have Walkenbach's 'Excel 2002 Formulas.
I'll look at his Power Programming.

Pity I have just upgraded to an iMac and Office 2008 and Bill has dropped
VBA on the Mac!! I'll plug away on the expiring PC until I install Parallels
and 2007 on the iMac. What is Bill up to - after all he wrote Multiplan toe
the Mac in the first place.

MVPs sometimes refer to Deborah's Excel Page -
http://www.dgtraining.net/Excel/Excel.html

Best wishes

Francis


"Air_Cooled_Nut" wrote in message
...
Okay, I'll try to explain but there's one book I would recommend, "it's
Excel
2002 Power Programming with VBA", ISBN: 0-7645-4799-2 (there's a 2003
version
but apparently there wasn't much extra added). This book alone taught me
so
much; it's an EXCELLENT resource and very well written!

The radio button, technically known as the Option Button. Create a Frame
(click on Frame in the Toolbox in the VBA editor) on the user form and
place
your option buttons inside it. This will group those option buttons so
only
one will be True at any time when clicked. When another one is clicked
the
other will one will automatically clear (become False). It does not
matter
how many option buttons you have in a Frame (or group).

The other method doesn't require using a Frame. The option buttons are
placed on the user form. For each option button you want to be grouped
together (so when one is selected the other(s) are deselected), select it,
and in the Properties window set the GroupName to a common string
(descriptor). For example, if you have two option buttons on the form and
you want them to "see" each other, set the GroupName for both of them to
the
same value, maybe "Gender" if one button is "Male" and the other button is
"Female".

I recommend using a Frame because it visually groups the option buttons --
or any other group of controls -- for the user. The Frame has a caption,
too. In our above example, you could set the Caption of the Frame to
"What
is your gender?", thus instantly allowing the user to know what or why
they
are selecting. Also, there's no need to set the GroupName for the option
buttons in a Frame :-) Does this help?

Yes, the MVP's are life-savers! I truely appreciate their dedicated
talents. The list of links I have is not complete but it contains the
ones I
use most when I go from job to job.

--

Toby Erkson
http://excel.icbm.org/


"Francis Hookham" wrote:

Thanks for your comment Toby. Although an XL user (inc Multiplan) days it
has been as a self taught amateur, not good at reading complicated text
books. Can you point me to a simple explanation of the need for grouping
radio buttons.



I have grouped the buttons and using Gary's

userform1.optionbutton1=false

all is well.



Thank you also for the link to your website - it is now a Links bar
favourite named 'XL' so I can keep an eye on you and get to the very
useful
Links page - recently, by mistake, I wiped all my links to those amazing
MVPs.



Thanks from Cambridge, UK



Francis




"Air_Cooled_Nut" wrote in
message
...
When radio buttons are together in a group, like in a Frame, then
clicking
one will automatically clear the other. So it sounds like your Option
Buttons aren't properly grouped with each other.
--

Toby Erkson
http://excel.icbm.org/


"Francis Hookham" wrote:

The clicked radio button in a UserForm does not clear so clicking the
same
button again does not work, whereas clicking another radio button next
does
work.



Is there an instruction I should incorporate to clear the previous
clicking
of the button? If so what and where should it go?



Francis Hookham



--------------------------------------------------------------



Private Sub OptionButton6_Click()

'colour frame(s) orange

Userform1.Hide

ColourOrange

End Sub



Sub ColourOrange()

FindFrameRow

'set colour

ActiveWorkbook.Colors(21) = RGB(255, 245, 225)

FillColour = 21

OneShotColour

End Sub



Sub FindFrameRow()

If ActiveCell.Row = 1 Or ActiveCell.Row = 2 Then

RowNo = 3

Else

RowNo = ActiveCell.Row

While Cells(RowNo, 1) = ""

RowNo = RowNo - 1

Wend

End If

Cells(RowNo, 1).Select

End Sub



Sub OneShotColour()

'given 'RowNo', this performs the colour change of one shot

Range(Cells(RowNo, 2), Cells(RowNo + 15, 10)). _

Interior.ColorIndex = FillColour

Cells(RowNo, 7).Interior.ColorIndex = xlNone

Cells(RowNo, 9).Interior.ColorIndex = xlNone

End Sub

No VBA for 2008 on the Mac? Say what?! Odd indeed. The last time I touched
a Macintosh was back in the Apple II days...and me and my buddies knew more
about computers than the teacher! Needless to say, I'm outta touch when it
comes to Mac. :-o

I plan on taking some business intelligence courses this summer and I'll be
purchasing a new system with some of the loan money (my current one is
Intel's first 1.0GHz system back in 2000) so I will get Vista and eventually
start working in Office 12 -- I like the new Excel 12. I was an HP call
center supervisor for their notebook line and I know all about that OS (oyi,
what a pain!) but it will get better...many people forget that when XP came
out it wasn't that great :-)

Thanks for the link, I'll go check that out.
--

Toby Erkson
http://excel.icbm.org/


"Francis Hookham" wrote:

Thanks again Toby - I hesitate to admit that I have Bullen, Green, Bovey and
Rosenberg's 'Excel 2002 VBA' (ISBN 1-861005-70-9) but is is so heavy going
that I seldom refer to it. I also have Walkenbach's 'Excel 2002 Formulas.
I'll look at his Power Programming.

Pity I have just upgraded to an iMac and Office 2008 and Bill has dropped
VBA on the Mac!! I'll plug away on the expiring PC until I install Parallels
and 2007 on the iMac. What is Bill up to - after all he wrote Multiplan toe
the Mac in the first place.

MVPs sometimes refer to Deborah's Excel Page -
http://www.dgtraining.net/Excel/Excel.html

Best wishes

Francis


"Air_Cooled_Nut" wrote in message
...
Okay, I'll try to explain but there's one book I would recommend, "it's
Excel
2002 Power Programming with VBA", ISBN: 0-7645-4799-2 (there's a 2003
version
but apparently there wasn't much extra added). This book alone taught me
so
much; it's an EXCELLENT resource and very well written!

The radio button, technically known as the Option Button. Create a Frame
(click on Frame in the Toolbox in the VBA editor) on the user form and
place
your option buttons inside it. This will group those option buttons so
only
one will be True at any time when clicked. When another one is clicked
the
other will one will automatically clear (become False). It does not
matter
how many option buttons you have in a Frame (or group).

The other method doesn't require using a Frame. The option buttons are
placed on the user form. For each option button you want to be grouped
together (so when one is selected the other(s) are deselected), select it,
and in the Properties window set the GroupName to a common string
(descriptor). For example, if you have two option buttons on the form and
you want them to "see" each other, set the GroupName for both of them to
the
same value, maybe "Gender" if one button is "Male" and the other button is
"Female".

I recommend using a Frame because it visually groups the option buttons --
or any other group of controls -- for the user. The Frame has a caption,
too. In our above example, you could set the Caption of the Frame to
"What
is your gender?", thus instantly allowing the user to know what or why
they
are selecting. Also, there's no need to set the GroupName for the option
buttons in a Frame :-) Does this help?

Yes, the MVP's are life-savers! I truely appreciate their dedicated
talents. The list of links I have is not complete but it contains the
ones I
use most when I go from job to job.

--

Toby Erkson
http://excel.icbm.org/


"Francis Hookham" wrote:

Thanks for your comment Toby. Although an XL user (inc Multiplan) days it
has been as a self taught amateur, not good at reading complicated text
books. Can you point me to a simple explanation of the need for grouping
radio buttons.



I have grouped the buttons and using Gary's

userform1.optionbutton1=false

all is well.



Thank you also for the link to your website - it is now a Links bar
favourite named 'XL' so I can keep an eye on you and get to the very
useful
Links page - recently, by mistake, I wiped all my links to those amazing
MVPs.



Thanks from Cambridge, UK



Francis




"Air_Cooled_Nut" wrote in
message
...
When radio buttons are together in a group, like in a Frame, then
clicking
one will automatically clear the other. So it sounds like your Option
Buttons aren't properly grouped with each other.
--

Toby Erkson
http://excel.icbm.org/


"Francis Hookham" wrote:

The clicked radio button in a UserForm does not clear so clicking the
same
button again does not work, whereas clicking another radio button next
does
work.



Is there an instruction I should incorporate to clear the previous
clicking
of the button? If so what and where should it go?



Francis Hookham



--------------------------------------------------------------



Private Sub OptionButton6_Click()

'colour frame(s) orange

Userform1.Hide

ColourOrange

End Sub



Sub ColourOrange()

FindFrameRow

'set colour

ActiveWorkbook.Colors(21) = RGB(255, 245, 225)

FillColour = 21

OneShotColour

End Sub



Sub FindFrameRow()

If ActiveCell.Row = 1 Or ActiveCell.Row = 2 Then

RowNo = 3

Else

RowNo = ActiveCell.Row

While Cells(RowNo, 1) = ""

RowNo = RowNo - 1

Wend

End If

Cells(RowNo, 1).Select

End Sub



Sub OneShotColour()

'given 'RowNo', this performs the colour change of one shot

Range(Cells(RowNo, 2), Cells(RowNo + 15, 10)). _

Interior.ColorIndex = FillColour

Cells(RowNo, 7).Interior.ColorIndex = xlNone

Cells(RowNo, 9).Interior.ColorIndex = xlNone

End Sub