Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Entry Dependent on Option Control
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Entry Dependent on Option Control
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Entry Dependent on Option Control
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control Data Entry - push entry to next cell | Excel Discussion (Misc queries) | |||
Formula dependent on a cell entry | Excel Discussion (Misc queries) | |||
Cell value dependent on another cell entry | Excel Discussion (Misc queries) | |||
Control Cell Link for Option Button based on value in a cell | Excel Programming | |||
Control Cell Link for Option Button based on value in a cell | Excel Programming |