I'm wondering how to control cell entry [data entry generically] based on an
option control.

I have two option buttons. Option Button 1 should allow data entry into
cell A1 for example and Option Button 2 should allow data entry into cells
A3:A7. However, when Option Button 1 is selected I want to disable entry for
cells A3:A7 and when Option Button 2 is selected I want to disable entry for
cell A1.

I'm open to using some sort of control [please explain as I am a VBA rookie]
instead of the spreadsheet cell if that's the only way to make this work.

I've done this with Option Buttons controlling Check Boxes, but I'm not sure
how to control data entry.

Thank you.

B

Here is how I would do it. It does nore require a macro. There are 2 types of
option buttons. For this I would use the option buttons off of the Control
Toolbox and not the ones from the forms toolbar. Place 2 buttons on your
sheet. Right click the buttons and select Properties. In the dialog that
opens for button 1 make the linked cell B1 and for button 2 make the linked
cell B2.
Now on the control toolbox click the icon taht looks like a triangle and
ruler to exit design mode.
Clicking the option buttons should toggle True and False in Cells B1 and B2.
Select Cell A1 and select Data - Validation - Custom and in the formula
box add =$B$1
Select Cell A3:A7 and select Data - Validation - Custom and in the formula
box add =$B$2
Your cells should now be locked down based on the option button values.

You can make the Error dialogs more meaningful by modifying the Validation.
--
HTH...

Jim Thomlinson


"BJ" wrote:

I'm wondering how to control cell entry [data entry generically] based on an
option control.

I have two option buttons. Option Button 1 should allow data entry into
cell A1 for example and Option Button 2 should allow data entry into cells
A3:A7. However, when Option Button 1 is selected I want to disable entry for
cells A3:A7 and when Option Button 2 is selected I want to disable entry for
cell A1.

I'm open to using some sort of control [please explain as I am a VBA rookie]
instead of the spreadsheet cell if that's the only way to make this work.

I've done this with Option Buttons controlling Check Boxes, but I'm not sure
how to control data entry.

Thank you.

B

Jim

That worked great but created another problem. One that I should have
forseen I guess.

What the user can do [assuming I left the programming as you described] is
select one option button and input the data and then select the other option
button and input THAT data. So basically now I've got the data in both
places. I can only have it in one place.

What I need to do is program the spreadsheet so that when one option button
is selected, it sets the data in the alternate option button's data entry
area to all zeros.

Any thoughts?

"Jim Thomlinson" wrote:

Here is how I would do it. It does nore require a macro. There are 2 types of
option buttons. For this I would use the option buttons off of the Control
Toolbox and not the ones from the forms toolbar. Place 2 buttons on your
sheet. Right click the buttons and select Properties. In the dialog that
opens for button 1 make the linked cell B1 and for button 2 make the linked
cell B2.
Now on the control toolbox click the icon taht looks like a triangle and
ruler to exit design mode.
Clicking the option buttons should toggle True and False in Cells B1 and B2.
Select Cell A1 and select Data - Validation - Custom and in the formula
box add =$B$1
Select Cell A3:A7 and select Data - Validation - Custom and in the formula
box add =$B$2
Your cells should now be locked down based on the option button values.

You can make the Error dialogs more meaningful by modifying the Validation.
--
HTH...

Jim Thomlinson


"BJ" wrote:

I'm wondering how to control cell entry [data entry generically] based on an
option control.

I have two option buttons. Option Button 1 should allow data entry into
cell A1 for example and Option Button 2 should allow data entry into cells
A3:A7. However, when Option Button 1 is selected I want to disable entry for
cells A3:A7 and when Option Button 2 is selected I want to disable entry for
cell A1.

I'm open to using some sort of control [please explain as I am a VBA rookie]
instead of the spreadsheet cell if that's the only way to make this work.

I've done this with Option Buttons controlling Check Boxes, but I'm not sure
how to control data entry.

Thank you.

B