Sorry to pester so much, but I keep running against walls with this
form!

I have a series of option buttons in a frame box. They seem to work
correctly (thanks to some help I got here). What I need is to make
sure the user has selected one of them before the form moves on to the
next record.

I've got a Validation object set up--at least, I *think* I do. It's
xlValidateCustom, and I'm trying to set up a formula for it. According
to the documentation, I need to set up something that evaluates to True,
and from my attempts it looks like I can't use something with an equals
sign in it. The one reliable test for whether the button has been
clicked is whether there is text in a particular cell, so I tried
"IsEmpty(ActiveCell)=False," but the compiler didn't like that. There
seems no comparable expression I can find in the documentation that
would evaluate to True when there is text in the cell.

Of course, there may be some other way of going about this that would
be much simpler. Currently I have all the buttons set up in an
If-ElseIf sequence, and the check comes just before the End, being an
Else rather than an ElseIf.

Any help would be appreciated! Thanks!


--
cheaperThanAPro
------------------------------------------------------------------------
cheaperThanAPro's Profile: http://www.excelforum.com/member.php...o&userid=28241
View this thread: http://www.excelforum.com/showthread...hreadid=478243

How about just a giant Or statement:

if me.optionbutton1.value = true _
or me.optionbutton2.value = true _
or ....

===
Or loop through the controls in that frame looking for optionbuttons that are
chosen.

Option Explicit
Private Sub CommandButton1_Click()
Dim myCtrl As Control
Dim AtLeastOneChosen As Boolean

AtLeastOneChosen = False
For Each myCtrl In Me.Frame1.Controls
If TypeOf myCtrl Is MSForms.OptionButton Then
If myCtrl.Object.Value = True Then
AtLeastOneChosen = True
Exit For
End If
End If
Next myCtrl

MsgBox AtLeastOneChosen
End Sub



cheaperThanAPro wrote:

Sorry to pester so much, but I keep running against walls with this
form!

I have a series of option buttons in a frame box. They seem to work
correctly (thanks to some help I got here). What I need is to make
sure the user has selected one of them before the form moves on to the
next record.

I've got a Validation object set up--at least, I *think* I do. It's
xlValidateCustom, and I'm trying to set up a formula for it. According
to the documentation, I need to set up something that evaluates to True,
and from my attempts it looks like I can't use something with an equals
sign in it. The one reliable test for whether the button has been
clicked is whether there is text in a particular cell, so I tried
"IsEmpty(ActiveCell)=False," but the compiler didn't like that. There
seems no comparable expression I can find in the documentation that
would evaluate to True when there is text in the cell.

Of course, there may be some other way of going about this that would
be much simpler. Currently I have all the buttons set up in an
If-ElseIf sequence, and the check comes just before the End, being an
Else rather than an ElseIf.

Any help would be appreciated! Thanks!

--
cheaperThanAPro
------------------------------------------------------------------------
cheaperThanAPro's Profile: http://www.excelforum.com/member.php...o&userid=28241
View this thread: http://www.excelforum.com/showthread...hreadid=478243

--

Dave Peterson

Thanks for answering, Dave!

Well, I want to generate an error message telling the user that the
must select one of those buttons. As far as I can tell, I have to hav
a Validation object to do that.

Am I wrong? Certainly the giant IF statement is the easiest.

Thanks again

--
cheaperThanAPr
-----------------------------------------------------------------------
cheaperThanAPro's Profile: http://www.excelforum.com/member.php...fo&userid=2824
View this thread: http://www.excelforum.com/showthread.php?threadid=47824

I'm not sure what you mean by a validation object.

I assumed that you had a userform (created in the VBE--not a worksheet made to
get user input).

If that's correct, you could do something on the "Ok/continue" button on the
userform.

That giant if statement (or the loop).

if me.optionbutton1.value = true _
or me.optionbutton2.value = true _
or me.optionbutton3.value = true then
'keep going, one was selected
'put a label on the userform and make sure it's clear
me.label1.caption = ""
else
'msgbox "Please select one of those options"
'or put it in the label
me.label1.caption = "Please select one of those options"
exit sub
end if

'continue your code.



cheaperThanAPro wrote:

Thanks for answering, Dave!

Well, I want to generate an error message telling the user that they
must select one of those buttons. As far as I can tell, I have to have
a Validation object to do that.

Am I wrong? Certainly the giant IF statement is the easiest.

Thanks again!

--
cheaperThanAPro
------------------------------------------------------------------------
cheaperThanAPro's Profile: http://www.excelforum.com/member.php...o&userid=28241
View this thread: http://www.excelforum.com/showthread...hreadid=478243

--

Dave Peterson

It's just something I read in the online help for Excel. I guess
didn't understand it very well.

The important thing is that you solved my problem! It work
beautifully. Thanks so much!

cta

--
cheaperThanAPr
-----------------------------------------------------------------------
cheaperThanAPro's Profile: http://www.excelforum.com/member.php...fo&userid=2824
View this thread: http://www.excelforum.com/showthread.php?threadid=47824