PDA

View Full Version : More option button help



allison
03-06-2008, 02:17 PM
Have an audit spreadsheet that is looking at 10 different categories - each category has it's own group of option buttons that grade that category. The option buttons are linked to cells; there is no code attached to them.

Ideally, if the first category (optionbutton1) is TRUE, the audit shouldn't continue. Is there a way to lock down all other categories based on that one value?

Bob Phillips
03-06-2008, 02:47 PM
I think you would need code. Is that a goer?

allison
03-06-2008, 02:51 PM
I'm fine with putting in code to lock down the other categories - I suspect that's the only way to do it. Right now, there isn't code associated with any of the option buttons (at least what I consider code).

I figured that if I had to put code in, it would be associated with the click event for OptionButton1 and it would say something like:

if optionbutton1 = True
do not let any other field be updated

or am I really off base? This is the first time that I've used the option buttons, so what you told me earlier today was my first try at making things work.

Bob Phillips
03-06-2008, 03:11 PM
That is one way, another is to monitor the linked cell and have event code reacting to that.

Whichever way we go, one question that needs answering is once the others are locked down how do they ever get released again? Oh, and are you meaning lock down the others in that category, or lock down all categories?

How have you grouped them?

allison
03-06-2008, 06:56 PM
I linked them to cells because the checkboxes that I replaced were set up to use the linked cells in formulae.

I grouped them using the group line in the properties window. I apologize for not being terribly specific with that right now, but I'm at home on 2007 (I'm using 2003 at work) and can't find the same window because I can't find the controls toolbar!

Category One has 5 option buttons - one of which is linked to a cell that gives a value of zero. If the "zero" button is marked in the first category, all other categories should be locked down. If the user changes the zero to something else, all other categories should be released. It's only when the zero is chosen should everything else be locked down.

The zero value is the only time that any of the categories are tied together. If a non zero value is chosen in the first category, all the other categories are completely independent of each other.

Does that make sense?

Bob Phillips
03-07-2008, 03:13 AM
I grouped them using the group line in the properties window. I apologize for not being terribly specific with that right now, but I'm at home on 2007 (I'm using 2003 at work) and can't find the same window because I can't find the controls toolbar!

You need to make the Developer tab visible in the ribbon. Office Button>Excel Options>Popular, there is a checkbox there, Show Developer tab in the ribbon.


Category One has 5 option buttons - one of which is linked to a cell that gives a value of zero. If the "zero" button is marked in the first category, all other categories should be locked down. If the user changes the zero to something else, all other categories should be released. It's only when the zero is chosen should everything else be locked down.

This code assumes that you have grouped all of the categories with a GroupBox, and that you have captioned the controlling buttons GroupBox Control.

You also have to use a cell to reference the zero values option button linked c ell, to trigger the calculate event. So, if the button is linked to Z1, add =Z1 into some other cell.

Then in the active sheet code module, add


Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "Z1" '<== change to suit
Dim opt As Object

For Each opt In Me.OptionButtons

If opt.GroupBox.Caption <> "Control" Then

opt.Enabled = Not (Me.Range(WS_RANGE).Value = 1)

End If
Next opt

End Sub