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