Disable Cell

[reza]

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

 

[JLatham]

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]

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

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

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

 

[JLatham]

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

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

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

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

 

[reza]

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

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

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

 

[JLatham]

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.

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

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

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