Rows not columns

E

Elfego Baca

It almost seems to be working but I need the rows locked when entering
something in the first column rather than the columns locked. I also
need to use columns from C through O rather than A through C. I almost
understand what you are doing but not quite. Thank you for your enormous
help here.
 
J

Joerg

This was your description:"I want B1, B2,
B3, B4 and B5 to be locked as soon as B1 is not blank. I then want I
want C1, C2, C3, C4 and C5 to be locked as soon as C1 is not blank"

What rows do you want to lock?

Joerg
 
J

Joerg

Your description is still confusing to me. You said that you need to lock
C1:C5 when someone inputs into C1. Then you said that you actually want to
lock C to O, rows 4 to 60. So what is the input cell that locks the other
ones? Still C1 or now C4? And even if an input into C4 would lock C4:C60,
an input in D4 would lock D4:D60 etc., you pointed out that you DON'T want
to do what you actually described, but that you would rather like to lock
rows. I don't get this. Which rows will lock if someone inputs into C4?
C4:O60? C4:O60?

Could you please make another attempt to define the correct locations?
Adapting the macro will then be a piece of cake.

Joerg
 
M

Manuel Murieta

For example: I would like to lock the following cells:
When C4 is filled in lock C4:L4
When C5 is filled in lock C5:L5
When C6 is filled in lock C6:L6
and so on up to and including
When C33 is filled in lock C33:L33.

I hope that this is less confusing.
Also for some reason I tried your subroutine and found that it didn't work
appropriately. I am using Excel 2003. I left 2 attachments in my last
newsgroup message to see what the problem is.

Butch
 
J

Joerg

Less confusing? At least you are describing rows now, not columns as in your
previous posts.

Talking about posts: Please stick to one (nick) name and one thread.

Not all newsservers allow attachments. Mine doesn't , so I don't know what
you posted.

I'm using Excel 2003 too and the code works fine here. Could you fix the
compile error (see my earlier post)? What exactly doesn't work?

Based on your new example I have adapted the macro. I have also preceded
each line of code with a short comment. So please make sure that in your
macro editor every second line starts with an apostroph ('), indicating a
comment line.

The example does not password protect your sheets (for testing this is just
easier). You can add a password later (ActiveSheet.Unprotect
"MySuperSecretPassword" and ActiveSheet.Protect "MySuperSecretPassword")

Hope that we are approaching the final stage
Cheers,

Joerg Mochikun


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Define worksheet and cells that cause locking of other cells
Set TriggerCells = Worksheets("Sheet1").Range("C4:C33")
'Exit macro if input was not made in one of the trigger cells
If Application.Intersect(ActiveCell, TriggerCells) Is Nothing Then Exit
Sub
'Unprotect sheet so that cell format can be changed to locked
ActiveSheet.Unprotect
'Lock all cells from active cell (=input cell) up to 9 columns to the
right
Range(ActiveCell, ActiveCell.Offset(0, 9)).Locked = True
'Protect sheet again
ActiveSheet.Protect
'Give feedback to user
MsgBox "Row is now protected. To unprotect call the manager!"
End Sub
 
E

Elfego Baca

Let me explain what I did. First I opened a new workbook. I then
highlighted all cells from C4 to K33. I then formatted these cells so
that they are unlocked. I then protected the sheet.

I then clicked ALT-F11 and then double-clicked ThisWorkBook to bring up
the code sheet. I then pasted the code as you have given it to me
without change:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Define worksheet and cells that cause locking of other cells
Set TriggerCells = Worksheets("Sheet1").Range("C4:C33")
'Exit macro if input was not made in one of the trigger cells
If Application.Intersect(ActiveCell, TriggerCells) Is Nothing Then
Exit Sub
'Unprotect sheet so that cell format can be changed to locked
ActiveSheet.Unprotect
'Unlock all cells from active cell (=input cell) up to 9 columns to
the Right
Range(ActiveCell, ActiveCell.Offset(0, 9)).Locked = True
'Protect sheet again
ActiveSheet.Protect
'Give feedback to user
MsgBox "Row is now protected. To unprotect call the manager!"
End Sub

I then saved the sheet.
I entered data in many of the cells from D4 to F10. No problem .It all
entered correctly.

I then entered an X in C4. Nothing happened. No cells locked. I then
entered an X in C5. As soon as I pressed "Enter" I got the message that
states "Row is now protected. To unprotect call the manager!". The
problem is the row that ends up protected is not row 4 but apparently
Row 5. I then entered an X in C7 and found that the cells D7, E7 and F7
etc were still unlocked, but the cells in D8, E8 and F8 etc were locked.
I understand your code and I cannot understand why it is working this
way. I even thought that it was my computer or my Excel. So I did the
same thing at work on a totally different copy of Excel 2003. I got the
same results. I cannot think of anything that I am doing that is
causing it not to work here, but work on your computer. Any help here? I
think that I am following your instructions to the "T".

Elfego Baca
 
G

Gord Dibben

When you type in C4 then hit ENTER, the ActiveCell is now C5 and the code runs
from there.

Either set your ENTER key to not move or alter the code as below where I have
moved up one row from the activecell with offset(-1 , 0) and offset(-1, 9)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Define worksheet and cells that cause locking of other cells
Set TriggerCells = Worksheets("Sheet1").Range("C4:C33")
'Exit macro if input was not made in one of the trigger cells
If Application.Intersect(ActiveCell, TriggerCells) Is Nothing Then Exit Sub
'Unprotect sheet so that cell format can be changed to locked
ActiveSheet.Unprotect
'Unlock all cells from (=input cell) up to 9 columns to
'the Right
Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1, 9).Resize).Locked = True
'Protect sheet again
ActiveSheet.Protect
'Give feedback to user
MsgBox "Row is now protected. To unprotect call the manager!"

End Sub


Gord Dibben MS Excel MVP
 
J

Joerg

I have told my Excel not to move after ENTER, so that's why it worked on my
system. Thanks Gord for pointing this out.

To fix this problem (I think) I have a better idea, which doesn't have to
take into account the cursor movements after ENTER (which anyway cannot be
anticipated because they depend on each individual user's setup). Instead of
running the code from the cell where Excel jumps after ENTER (ActiveCell)
the code runs from the cell where the input was made (Target). I've
modified the code, added 2 lines for better maintenance and readability and
made the message clearer.

As I wrote before: The code needs finetuning, e.g. a proper error handling.
In any case you should make sure to password protect your macro, otherwise
your worksheet password can be picked up by anybody clever enough to hit
Alt+F11.

Here is the new version. Let me know if it works.

Cheers,
Joerg Mochikun

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Define worksheet and cells that cause locking of other cells
Set TriggerCells = Worksheets("Sheet1").Range("C4:C33")
'Define row to be locked. From Target (=input cell) to 9 columns to the
right
Set LockedRow = Range(Target, Target.Offset(0, 9))
'Exit macro if input was not made in one of the trigger cells
If Application.Intersect(Target, TriggerCells) Is Nothing Then Exit Sub
'Unprotect sheet so that cell format can be changed to locked
ActiveSheet.Unprotect
'Lock all cells from Target (=input cell) up to 9 columns to the Right
LockedRow.Locked = True
'Protect sheet again
ActiveSheet.Protect
'Give feedback to user
MsgBox LockedRow.Address(False, False) & " is now protected. To
unprotect call the manager!"
End Sub






Gord Dibben said:
When you type in C4 then hit ENTER, the ActiveCell is now C5 and the code runs
from there.

Either set your ENTER key to not move or alter the code as below where I have
moved up one row from the activecell with offset(-1 , 0) and offset(-1, 9)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Define worksheet and cells that cause locking of other cells
Set TriggerCells = Worksheets("Sheet1").Range("C4:C33")
'Exit macro if input was not made in one of the trigger cells
If Application.Intersect(ActiveCell, TriggerCells) Is Nothing Then Exit Sub
'Unprotect sheet so that cell format can be changed to locked
ActiveSheet.Unprotect
'Unlock all cells from (=input cell) up to 9 columns to
'the Right
Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1, 9).Resize).Locked = True
'Protect sheet again
ActiveSheet.Protect
'Give feedback to user
MsgBox "Row is now protected. To unprotect call the manager!"

End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
E

Elfego Baca

Thanks. It looks like that fix worked great. I'll keep testing it for a
few days and put something on the newsgroup later if there is any
problem. Again thanks.
I have told my Excel not to move after ENTER, so that's why it worked on my
system. Thanks Gord for pointing this out.

To fix this problem (I think) I have a better idea, which doesn't have to
take into account the cursor movements after ENTER (which anyway cannot be
anticipated because they depend on each individual user's setup). Instead of
running the code from the cell where Excel jumps after ENTER (ActiveCell)
the code runs from the cell where the input was made (Target). I've
modified the code, added 2 lines for better maintenance and readability and
made the message clearer.

As I wrote before: The code needs finetuning, e.g. a proper error handling.
In any case you should make sure to password protect your macro, otherwise
your worksheet password can be picked up by anybody clever enough to hit
Alt+F11.

Here is the new version. Let me know if it works.

Cheers,
Joerg Mochikun

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Define worksheet and cells that cause locking of other cells
Set TriggerCells = Worksheets("Sheet1").Range("C4:C33")
'Define row to be locked. From Target (=input cell) to 9 columns to the
right
Set LockedRow = Range(Target, Target.Offset(0, 9))
'Exit macro if input was not made in one of the trigger cells
If Application.Intersect(Target, TriggerCells) Is Nothing Then Exit Sub
'Unprotect sheet so that cell format can be changed to locked
ActiveSheet.Unprotect
'Lock all cells from Target (=input cell) up to 9 columns to the Right
LockedRow.Locked = True
'Protect sheet again
ActiveSheet.Protect
'Give feedback to user
MsgBox LockedRow.Address(False, False) & " is now protected. To
unprotect call the manager!"
End Sub
Click to expand...
 
Top