Disable option button on user form??????

Hello,

Is this possible??

I have two option buttons on a user form, one for a temperature of <250 & one for a temperature >250. I want the option button >250 to be greyed out or have it so that you cannot choose it & option button <250 selected if a certain criteria in a list box is selected. The list box is called Valve_Model & the criteria i want it to work on is if the valve model HPBV Soft is selected & HPBV Soft AC.

Thanks,

Tony

Originally Posted by LJMetzger

Hi Tony,

You might want to try various combinations of the following:
.visible = true/false (completely hides control when false)
.locked = true/false (everything looks normal including tabs, but can't modify control)
.enable = true/false (no tabs, can't modify, greyed out)

For example:

Please Login or Register  to view this content.

Lewis

Hi Lewis & thanks.

I see what you mean but i want optionbutton 1 to be greyed out & optionbutton 2 selected if a previous option is selected in a combo box on the same userform.


For example. If HPBV Soft is selected in the combobox named "Valve_Model" i then want optionbutton 1 to be greyed out or even hidden & optionbutton 2 automatically selected.


Is this possible?

Thanks,

Tony.

Psuedo Code follows:

Please Login or Register  to view this content.

NOTE: If OptionButtons have an event handler, you might want to turn the event handler off when you set the value to true.

Userform Pseudo Code:

Please Login or Register  to view this content.

Lewis

Hi Lewis,

Thank you but i have no idea where i place the above code also i cannot see any reference to the conditions i mentioned i.e the combobox named "Valve_Model"

I will attach a sample workbook.

Thanks,

Tony.

test.xlsm

Originally Posted by LJMetzger

See attached workbook. I was able to test the code (Excel 2003) and it seems to work OK. You should copy the following code to your workbook and not use mine, because some of your formatting may have been lost by Excel 2003. You can run my code and see how the UserForm changes when the value of "HPBV Soft" is entered in the model.

The following is in ModShowUserForm (formerly Module5).

Please Login or Register  to view this content.

The following is in UserForm1 code:

Please Login or Register  to view this content.

'Option Explicit' should be used at the top of each VBA module to require VARIABLE DECLARATION. This prevents errors caused by missspellings. See http://www.cpearson.com/excel/DeclaringVariables.aspx

Lewis

Hi Lewis,

I ran your excel file & it worked brilliantly so i copied the code into mine & it started to work fantasticaly but when i clicked on Ok on the userform i got an error on the following code at line 1:

Please Login or Register  to view this content.

You ran into one of the problems when making a control NOT VISIBLE. You have to test for visibility. There's always one more thing with Excel.

You probably want something like (untested):

Please Login or Register  to view this content.

Lewis

Originally Posted by LJMetzger

You ran into one of the problems when making a control NOT VISIBLE. You have to test for visibility. There's always one more thing with Excel.

You probably want something like (untested):

Please Login or Register  to view this content.

Lewis

That works fine if i chose the HPBV Soft but if i click on option button 1 manually i get an error on line 2 of the following:

Please Login or Register  to view this content.

My fault. I had a typo above.

Please Login or Register  to view this content.

One way to know if you typed in the correct thing is to always type in all lower case. When you have the correct spelling, VBA will automatically convert one or more characters to UPPER CASE. That's why I always have mixed Upper and Lower case in my variable names.

Lewis

Originally Posted by LJMetzger

My fault. I had a typo above.

Please Login or Register  to view this content.

One way to know if you typed in the correct thing is to always type in all lower case. When you have the correct spelling, VBA will automatically convert one or more characters to UPPER CASE. That's why I always have mixed Upper and Lower case in my variable names.

Lewis

Hi again, nearly there!!!!!!!!!!!!

I now have no error & option button 1 is not vissable when HPBV Soft is selected.............yaaaaaaaaaaayyyy.........BUT

it does not put >250 in cell D8 instead it shows <250. Also if i select >250 manually it still doesnt change cell D8????

Originally Posted by Bflare

Hi again, nearly there!!!!!!!!!!!!

I now have no error & option button 1 is not vissable when HPBV Soft is selected.............yaaaaaaaaaaayyyy.........BUT

it does not put >250 in cell D8 instead it shows <250. Also if i select >250 manually it still doesnt change cell D8????

Just to make clear.

If i chose HPBV Soft it correctly makes <250 disappear & puts >250 in cell D8 but if i select anything other than HPBV Soft then the dot remains in >250 but it actually changes cell D8 to <250??? Also if i select between <250 & >250 it will only show <250 in cell D8????

Let's try a different approach and use the power of the Option Buttons.

1. Remove the following code:

Please Login or Register  to view this content.

2. Put the Option buttons in a group.

3. In the Userform Code implement Option button event handlers:

Please Login or Register  to view this content.

I tested it and it seems to work.

Lewis

Originally Posted by LJMetzger

Let's try a different approach and use the power of the Option Buttons.

1. Remove the following code:

Please Login or Register  to view this content.

2. Put the Option buttons in a group.

3. In the Userform Code implement Option button event handlers:

Please Login or Register  to view this content.

I tested it and it seems to work.

Lewis

Hi Lewis,

Yes that seems to work great the only small problem I see is that once the HPBV Soft is selected & therefore the temp >250 selected the information is visible on the sheet rather than visible only when the Ok button is clicked on the userform. Can this easily be changed? If not it ok the main thing is that its operating as I wanted.

Thanks,

Tony.

If you want the Sheet updated only after the OK button is selected then:
1. Delete Sub OptionButton1_Click()
2. Delete Sub OptionButton2_Click()
3. Add the following in CommandButton1_Click (tested and seems to be working).

Please Login or Register  to view this content.

Please note that it was confusing for me when I selected a Temperature Button and the Temperature on the Sheet did not change.

Lewis

Originally Posted by LJMetzger

If you want the Sheet updated only after the OK button is selected then:
1. Delete Sub OptionButton1_Click()
2. Delete Sub OptionButton2_Click()
3. Add the following in CommandButton1_Click (tested and seems to be working).

Please Login or Register  to view this content.

Please note that it was confusing for me when I selected a Temperature Button and the Temperature on the Sheet did not change.

Lewis

Fantastic, your a star thanks

Tony.