Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Cell
Dear Guys,
need your help to disable some cells. i.e. in cell A5, A6, A7, A8, till A15... scenario: 1. If i write Yes in cell A5, then A6-A10 has enable and for A11-A15 has disable, but 2. If i write No in cell A5, then cell A6-A10 has disable and for A11-A15 has enable for writing. and for this scenario, i will very grateful if you give me a way, if i write No in cell A5, then directly go to A11, and skip Cell A6-A10. can you tell me how to do that? so many thanks for your help reza |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Cell
The code below goes into the worksheet's event processing code module. To
get there, select that sheet and Right-click on its name tab and choose [View Code] from the popup list. Copy and paste the code below into the code module and close the VB Editor. You may have to change some of the code. As written, it assumes the worksheet does not have a password assigned, but the code needed if you do have a password for the sheet is provided as comments. Just comment out (by putting a ' in front of the line of code) the simple ActiveSheet.Unprotect and ActiveSheet.Protect statements and removing the ' from in front of the ones that use the password:="password" statement, and of course change that to have the real password between the double-quote marks. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$5" Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "YES" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = False Range("A11:A15").Locked = True ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Case Is = "NO" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = True Range("A11:A15").Locked = False ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Range("A11").Activate ' goto A11 Case Else 'if they did not enter 'yes or no, then do nothing 'or you could put up a 'message box and force them to 'be locked into the cell until 'they enter yes or no ' MsgBox "You must enter Yes or No" ' Target.Select ' Exit Sub End Select End Sub "reza" wrote: Dear Guys, need your help to disable some cells. i.e. in cell A5, A6, A7, A8, till A15... scenario: 1. If i write Yes in cell A5, then A6-A10 has enable and for A11-A15 has disable, but 2. If i write No in cell A5, then cell A6-A10 has disable and for A11-A15 has enable for writing. and for this scenario, i will very grateful if you give me a way, if i write No in cell A5, then directly go to A11, and skip Cell A6-A10. can you tell me how to do that? so many thanks for your help reza |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Cell
JLatham...
thanks for ur quick response. but i already try... i copy ur code and paste into excel (right click--view code--then paste ur code without change anything). and when i try input "YES" or "NO" nothing happen... i still can write in that cells... maybe i skip something...still need ur guidance... many thanks reza "JLatham" wrote: The code below goes into the worksheet's event processing code module. To get there, select that sheet and Right-click on its name tab and choose [View Code] from the popup list. Copy and paste the code below into the code module and close the VB Editor. You may have to change some of the code. As written, it assumes the worksheet does not have a password assigned, but the code needed if you do have a password for the sheet is provided as comments. Just comment out (by putting a ' in front of the line of code) the simple ActiveSheet.Unprotect and ActiveSheet.Protect statements and removing the ' from in front of the ones that use the password:="password" statement, and of course change that to have the real password between the double-quote marks. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$5" Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "YES" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = False Range("A11:A15").Locked = True ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Case Is = "NO" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = True Range("A11:A15").Locked = False ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Range("A11").Activate ' goto A11 Case Else 'if they did not enter 'yes or no, then do nothing 'or you could put up a 'message box and force them to 'be locked into the cell until 'they enter yes or no ' MsgBox "You must enter Yes or No" ' Target.Select ' Exit Sub End Select End Sub "reza" wrote: Dear Guys, need your help to disable some cells. i.e. in cell A5, A6, A7, A8, till A15... scenario: 1. If i write Yes in cell A5, then A6-A10 has enable and for A11-A15 has disable, but 2. If i write No in cell A5, then cell A6-A10 has disable and for A11-A15 has enable for writing. and for this scenario, i will very grateful if you give me a way, if i write No in cell A5, then directly go to A11, and skip Cell A6-A10. can you tell me how to do that? so many thanks for your help reza |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Cell
It definitely works for me.
Your Macro Security setting may be set to HIGH and that would stop the macro from running. Here is how to change your Macro Security setting: Excel 2003 and earlier: Tools -- Macro -- Security In the dialog that appears, choose the "Medium" setting. [OK] to close the dialog. Close Excel and then reopen it and test your workbook. You must shut down Excel after making the change to the security setting for it to take affect. When you open any workbooks in the future that have macros in them, you will be asked if you want to allow the macros to run (enable) or to stop them from running (disable). You decide based on how much you trust the source of the workbook. For this one to work, you will have to choose [Enable]. In Excel 2007 and later: Click the "Office" button, then click the [Excel Options] button at the lower right of the window that opens. In the Excel Options window, choose "Trust Center" then click on the [Trust Center Settings] button. In the next dialog, choose the "Macro Settings" group. Choose the "Disable all macros with notification" option (2nd from the top). Click [OK] until all the windows are closed. Just as before, you will have to close Excel and re-open it for the new setting to work properly. When you open a workbook with macros in it, a "Security Warning" bar will appear above the worksheet with an {Options...} button. Click that button to choose to enable macros or not. I hope this helps. I'll check back later to see. If you don't hear from me and need more help, feel free to contact me at this email addy (remove spaces) Help From @jlatham site. com "reza" wrote: JLatham... thanks for ur quick response. but i already try... i copy ur code and paste into excel (right click--view code--then paste ur code without change anything). and when i try input "YES" or "NO" nothing happen... i still can write in that cells... maybe i skip something...still need ur guidance... many thanks reza "JLatham" wrote: The code below goes into the worksheet's event processing code module. To get there, select that sheet and Right-click on its name tab and choose [View Code] from the popup list. Copy and paste the code below into the code module and close the VB Editor. You may have to change some of the code. As written, it assumes the worksheet does not have a password assigned, but the code needed if you do have a password for the sheet is provided as comments. Just comment out (by putting a ' in front of the line of code) the simple ActiveSheet.Unprotect and ActiveSheet.Protect statements and removing the ' from in front of the ones that use the password:="password" statement, and of course change that to have the real password between the double-quote marks. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$5" Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "YES" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = False Range("A11:A15").Locked = True ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Case Is = "NO" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = True Range("A11:A15").Locked = False ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Range("A11").Activate ' goto A11 Case Else 'if they did not enter 'yes or no, then do nothing 'or you could put up a 'message box and force them to 'be locked into the cell until 'they enter yes or no ' MsgBox "You must enter Yes or No" ' Target.Select ' Exit Sub End Select End Sub "reza" wrote: Dear Guys, need your help to disable some cells. i.e. in cell A5, A6, A7, A8, till A15... scenario: 1. If i write Yes in cell A5, then A6-A10 has enable and for A11-A15 has disable, but 2. If i write No in cell A5, then cell A6-A10 has disable and for A11-A15 has enable for writing. and for this scenario, i will very grateful if you give me a way, if i write No in cell A5, then directly go to A11, and skip Cell A6-A10. can you tell me how to do that? so many thanks for your help reza |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Cell
JLatham....
yes it works, but a new problems occurs. when i written "YES" only cell A6-A10 and Cell A16-A21 has enable, for others was disable, even for others column. can you modify ur code (please...). what i want to achieve if i written "YES" only for Cell A11-A15 has disable and for others has enable. and if i written "NO" only for cell C6-C10 has disable and others has enable. so many thanks for your kindness reza "JLatham" wrote: The code below goes into the worksheet's event processing code module. To get there, select that sheet and Right-click on its name tab and choose [View Code] from the popup list. Copy and paste the code below into the code module and close the VB Editor. You may have to change some of the code. As written, it assumes the worksheet does not have a password assigned, but the code needed if you do have a password for the sheet is provided as comments. Just comment out (by putting a ' in front of the line of code) the simple ActiveSheet.Unprotect and ActiveSheet.Protect statements and removing the ' from in front of the ones that use the password:="password" statement, and of course change that to have the real password between the double-quote marks. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$5" Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "YES" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = False Range("A11:A15").Locked = True ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Case Is = "NO" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = True Range("A11:A15").Locked = False ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Range("A11").Activate ' goto A11 Case Else 'if they did not enter 'yes or no, then do nothing 'or you could put up a 'message box and force them to 'be locked into the cell until 'they enter yes or no ' MsgBox "You must enter Yes or No" ' Target.Select ' Exit Sub End Select End Sub "reza" wrote: Dear Guys, need your help to disable some cells. i.e. in cell A5, A6, A7, A8, till A15... scenario: 1. If i write Yes in cell A5, then A6-A10 has enable and for A11-A15 has disable, but 2. If i write No in cell A5, then cell A6-A10 has disable and for A11-A15 has enable for writing. and for this scenario, i will very grateful if you give me a way, if i write No in cell A5, then directly go to A11, and skip Cell A6-A10. can you tell me how to do that? so many thanks for your help reza |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable Cell
reza,
Here is how locking cells and protecting sheets works - you need to understand this before I go on to offer advice on how to set up your worksheet so it is usable with the code I provided. Initially all cells on a worksheet are marked as "Locked", but the worksheet is not protected. In this condition, it does not matter that the cells are locked, they will respond just as if they were unlocked. But if you were to immediately put a worksheet into protected status (Tools -- Protection -- Protect Sheet), then you would not be able to enter anything into the cells on it. Now we can start setting up your worksheet for use. Begin by making sure that it is not protected. Select all of the cells by clicking the gray square just to the left of the "A" column indicator and just above the "1" row indicator. With all the cells selected, choose Format -- Cells -- [Protection] Now at this point what you do depends on whether you want most of the cells to be Locked or Unlocked when you normally use the sheet. Either Lock them all or Unlock them all, your choice. After you have all cells in the same condition, locked or not-locked, then go choose the ones that need to be exactly the opposite and use Format -- Cells -- [Protection] to set them the way you want. You don't have to choose them all at once, it may be easier to select small groups of them and set them the way you want and then grab another group and change their settings and keep on doing that until they're all the way you want. You can select cells that are separated from one another by selecting one and then holding the [Ctrl] key while selecting others and then set their Locked/Not-Locked property. The most obvious ones you will want to be locked are ones that have text/labels in them that you don't want people changing, and ones with formulas in them that you also don't want people accidentally typing over. Naturally, the ones that need to be unlocked are the ones that people need to type information into (such as A5). Don't worry too much about A6:A15, as those are going to be changed back and forth by the code when you type Yes or No into A5. "reza" wrote: JLatham.... yes it works, but a new problems occurs. when i written "YES" only cell A6-A10 and Cell A16-A21 has enable, for others was disable, even for others column. can you modify ur code (please...). what i want to achieve if i written "YES" only for Cell A11-A15 has disable and for others has enable. and if i written "NO" only for cell C6-C10 has disable and others has enable. so many thanks for your kindness reza "JLatham" wrote: The code below goes into the worksheet's event processing code module. To get there, select that sheet and Right-click on its name tab and choose [View Code] from the popup list. Copy and paste the code below into the code module and close the VB Editor. You may have to change some of the code. As written, it assumes the worksheet does not have a password assigned, but the code needed if you do have a password for the sheet is provided as comments. Just comment out (by putting a ' in front of the line of code) the simple ActiveSheet.Unprotect and ActiveSheet.Protect statements and removing the ' from in front of the ones that use the password:="password" statement, and of course change that to have the real password between the double-quote marks. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$5" Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "YES" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = False Range("A11:A15").Locked = True ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Case Is = "NO" ActiveSheet.Unprotect 'or if the sheet has a password: ' change "password" to the actual password ' ActiveSheet.Unprotect password:="password" Range("A6:A10").Locked = True Range("A11:A15").Locked = False ActiveSheet.Protect 'or if sheet has a password ' change "password" to the actual password ' ActiveSheet.Protect password:="password" Range("A11").Activate ' goto A11 Case Else 'if they did not enter 'yes or no, then do nothing 'or you could put up a 'message box and force them to 'be locked into the cell until 'they enter yes or no ' MsgBox "You must enter Yes or No" ' Target.Select ' Exit Sub End Select End Sub "reza" wrote: Dear Guys, need your help to disable some cells. i.e. in cell A5, A6, A7, A8, till A15... scenario: 1. If i write Yes in cell A5, then A6-A10 has enable and for A11-A15 has disable, but 2. If i write No in cell A5, then cell A6-A10 has disable and for A11-A15 has enable for writing. and for this scenario, i will very grateful if you give me a way, if i write No in cell A5, then directly go to A11, and skip Cell A6-A10. can you tell me how to do that? so many thanks for your help reza |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable cell focus | Excel Discussion (Misc queries) | |||
Disable cell reference insertion with PageUp in cell edit mode. | Excel Discussion (Misc queries) | |||
Is there a way to disable a macro based on the value of a cell? | Excel Discussion (Misc queries) | |||
Code to disable a cell in excel | Excel Discussion (Misc queries) | |||
disable cell | Excel Discussion (Misc queries) |