• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Protect Cells with VBA and Error Message.

V

VB_Noob

Member
Hi Guys,

I get following error message when I try to run the codes below. There are total 114 row in the actual sheet. I am only allowed to post some due to the limitation of 10000 characters per message.

First error message: Run Time Error 28, Out of Stack Space
Another error message: Run Time Error '-2147417848 (80010108)': Method 'Select' of object 'Range' failed


>>> use correct CODE -tags <<<

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Protection for the header fields
If Target.Address = "$A$1" Or Target.Address = "$B$1" Or Target.Address = "$C$1" Or Target.Address = "$D$1" Or Target.Address = "$E$1" Or Target.Address = "$L$1" Then Target.Offset(1, 0).Select
If Target.Address = "$A$2" Or Target.Address = "$C$2" Or Target.Address = "$E$2" Or Target.Address = "$F$2" Then Target.Offset(1, 0).Select
If Target.Address = "$A$3" Or Target.Address = "$B$3" Or Target.Address = "$C$3" Or Target.Address = "$D$3" Or Target.Address = "$E$3" Then Target.Offset(1, 0).Select
If Target.Address = "$F$3" Or Target.Address = "$G$3" Or Target.Address = "$H$3" Or Target.Address = "$I$3" Or Target.Address = "$J$3" Or Target.Address = "$K$3" Or Target.Address = "$L$3" Then Target.Offset(1, 0).Select


If Target.Address = "$A$4" Or Target.Address = "$C$4" Or Target.Address = "$D$4" Or Target.Address = "$L$4" Then Target.Offset(1, 0).Select
If Target.Address = "$F$4" And [$L$2].Value <> "Oph_Phase II_POC" Then Target.Offset(1, 0).Select

If Target.Address = "$A$5" Or Target.Address = "$C$5" Or Target.Address = "$C$5" Or Target.Address = "$F$5" Or Target.Address = "$G$5" Or Target.Address = "$H$5" Or Target.Address = "$I$5" Then Target.Offset(1, 0).Select
'Protection for row # 6
If Target.Address = "$A$7" Or Target.Address = "$C$7" Or Target.Address = "$D$7" Or Target.Address = "$E$7" Or Target.Address = "$F$7" Or Target.Address = "$G$7" Then Target.Offset(1, 0).Select

'Protection for row # 7
If Target.Address = "$A$6" Or Target.Address = "$B$6" Or Target.Address = "$C$6" Or Target.Address = "$D$6" Or Target.Address = "$E$6" Or Target.Address = "$F$6" Or Target.Address = "$G$6" Then Target.Offset(1, 0).Select


'Protection for row # 8
If Target.Address = "$A$8" Or Target.Address = "$B$8" Or Target.Address = "$C$8" Or Target.Address = "$D$8" Or Target.Address = "$E$8" Or Target.Address = "$F$8" Or Target.Address = "$G$8" Then Target.Offset(1, 0).Select

'Protection for row # 9
If Target.Address = "$F$9" Or Target.Address = "$G$9" Then Target.Offset(1, 0).Select

'Protection for row # 10
If Target.Address = "$F$10" Or Target.Address = "$G$10" Then Target.Offset(1, 0).Select

'Protection for row # 11
If Target.Address = "$F$11" Or Target.Address = "$G$11" Then Target.Offset(1, 0).Select

'Protection for row # 12
If Target.Address = "$F$12" Or Target.Address = "$G$12" Then Target.Offset(1, 0).Select

'Protection for row # 13
If Target.Address = "$F$13" Or Target.Address = "$G$13" Then Target.Offset(1, 0).Select

'Protection for row # 14
If Target.Address = "$F$14" Or Target.Address = "$G$14" Then Target.Offset(1, 0).Select

'Protection for row # 15
If Target.Address = "$F$15" Or Target.Address = "$G$15" Then Target.Offset(1, 0).Select

'Protection for row # 16
If Target.Address = "$F$16" Or Target.Address = "$G$16" Then Target.Offset(1, 0).Select

'Protection for row # 17
If Target.Address = "$F$17" Or Target.Address = "$G$17" Then Target.Offset(1, 0).Select

'Protection for row # 18
If Target.Address = "$F$18" Or Target.Address = "$G$18" Then Target.Offset(1, 0).Select

'Protection for row # 19
If Target.Address = "$F$19" Or Target.Address = "$G$19" Then Target.Offset(1, 0).Select

'Protection for row # 20
If Target.Address = "$F$20" Or Target.Address = "$G$20" Then Target.Offset(1, 0).Select

'Protection for row # 21
If Target.Address = "$F$21" Or Target.Address = "$G$21" Then Target.Offset(1, 0).Select

'Protection for row # 22
If Target.Address = "$F$22" Or Target.Address = "$G$22" Then Target.Offset(1, 0).Select

'Protection for row # 23
If Target.Address = "$F$23" Or Target.Address = "$G$23" Then Target.Offset(1, 0).Select

'Protection for row # 24
If Target.Address = "$F$24" Or Target.Address = "$G$24" Then Target.Offset(1, 0).Select

'Protection for row # 25
If Target.Address = "$F$25" Or Target.Address = "$G$25" Then Target.Offset(1, 0).Select

'Protection for row # 26
If Target.Address = "$F$26" Or Target.Address = "$G$26" Then Target.Offset(1, 0).Select

'Protection for row # 27
If Target.Address = "$F$27" Or Target.Address = "$G$27" Then Target.Offset(1, 0).Select

'Protection for row # 28
If Target.Address = "$F$28" Or Target.Address = "$G$28" Then Target.Offset(1, 0).Select

'Protection for row # 29
If Target.Address = "$F$29" Or Target.Address = "$G$29" Then Target.Offset(1, 0).Select

'Protection for row # 30
If Target.Address = "$F$30" Or Target.Address = "$G$30" Then Target.Offset(1, 0).Select

'Protection for row # 31
If Target.Address = "$F$31" Or Target.Address = "$G$31" Then Target.Offset(1, 0).Select

'Protection for row # 32
If Target.Address = "$F$32" Or Target.Address = "$G$32" Then Target.Offset(1, 0).Select

'Protection for row # 33
If Target.Address = "$F$33" Or Target.Address = "$G$33" Then Target.Offset(1, 0).Select

'Protection for row # 34
If Target.Address = "$F$34" Or Target.Address = "$G$34" Then Target.Offset(1, 0).Select

'Protection for row # 35
If Target.Address = "$F$35" Or Target.Address = "$G$35" Then Target.Offset(1, 0).Select

'Protection for row # 36
If Target.Address = "$F$36" Or Target.Address = "$G$36" Then Target.Offset(1, 0).Select

'Protection for row # 37
If Target.Address = "$F$37" Or Target.Address = "$G$37" Then Target.Offset(1, 0).Select

'Protection for row # 38
If Target.Address = "$F$38" Or Target.Address = "$G$38" Then Target.Offset(1, 0).Select

'Protection for row # 39
If Target.Address = "$F$39" Or Target.Address = "$G$39" Then Target.Offset(1, 0).Select

'Protection for row # 40
If Target.Address = "$F$40" Or Target.Address = "$G$40" Then Target.Offset(1, 0).Select

'Protection for row # 41
If Target.Address = "$F$41" Or Target.Address = "$G$41" Then Target.Offset(1, 0).Select

'Protection for row # 42
If Target.Address = "$F$42" Or Target.Address = "$G$42" Then Target.Offset(1, 0).Select

'Protection for row # 43
If Target.Address = "$F$43" Or Target.Address = "$G$43" Then Target.Offset(1, 0).Select

'Protection for row # 44
If Target.Address = "$F$44" Or Target.Address = "$G$44" Then Target.Offset(1, 0).Select

'Protection for row # 45
If Target.Address = "$F$45" Or Target.Address = "$G$45" Then Target.Offset(1, 0).Select

'Protection for row # 46
If Target.Address = "$F$46" Or Target.Address = "$G$46" Then Target.Offset(1, 0).Select

'Protection for row # 47
If Target.Address = "$F$47" Or Target.Address = "$G$47" Then Target.Offset(1, 0).Select

'Protection for row # 48
If Target.Address = "$F$48" Or Target.Address = "$G$48" Then Target.Offset(1, 0).Select

'Protection for row # 49
If Target.Address = "$F$49" Or Target.Address = "$G$49" Then Target.Offset(1, 0).Select

'Protection for row # 50
If Target.Address = "$F$50" Or Target.Address = "$G$50" Then Target.Offset(1, 0).Select

'Protection for row # 51
If Target.Address = "$F$51" Or Target.Address = "$G$51" Then Target.Offset(1, 0).Select

'Protection for row # 52
If Target.Address = "$F$52" Or Target.Address = "$G$52" Then Target.Offset(1, 0).Select

'Protection for row # 53
If Target.Address = "$F$53" Or Target.Address = "$G$53" Then Target.Offset(1, 0).Select

'Protection for row # 54
If Target.Address = "$F$54" Or Target.Address = "$G$54" Then Target.Offset(1, 0).Select

'Protection for row # 55
If Target.Address = "$F$55" Or Target.Address = "$G$55" Then Target.Offset(1, 0).Select

'Protection for row # 56
If Target.Address = "$F$56" Or Target.Address = "$G$56" Then Target.Offset(1, 0).Select

'Protection for row # 57
If Target.Address = "$F$57" Or Target.Address = "$G$57" Then Target.Offset(1, 0).Select

'Protection for row # 58
If Target.Address = "$F$58" Or Target.Address = "$G$58" Then Target.Offset(1, 0).Select

'Protection for row # 59
If Target.Address = "$F$59" Or Target.Address = "$G$59" Then Target.Offset(1, 0).Select

'Protection for row # 60
If Target.Address = "$F$60" Or Target.Address = "$G$60" Then Target.Offset(1, 0).Select

'Protection for row # 61
If Target.Address = "$F$61" Or Target.Address = "$G$61" Then Target.Offset(1, 0).Select

'Protection for row # 62
If Target.Address = "$F$62" Or Target.Address = "$G$62" Then Target.Offset(1, 0).Select




End Sub
 
Last edited by a moderator:
VB_Noob
hmm ...
What was Your challenge?
There were few TYPOs ... or something.
Could You try to use this version?
 

Attachments

  • VB_Noob.xlsb
    35.6 KB · Views: 2
vletm said:
VB_Noob
hmm ...
What was Your challenge?
There were few TYPOs ... or something.
Could You try to use this version?
Click to expand...

Hi,

Thanks for your reply and providing the codes. However, your code would basically bump the selection to a cell in the row below. Unfortunately some of those cells in the row below are the ones that I want to protect as well.
 
Is there any other way to do this? I can see you put my codes into an array. My original codes bump the selection to the next unlocked cell in the sheet. But it is not working now due to the error message. It seems my code can only protect the cells in row 1 through row 95. But I have total 114 rows.
 
vletm said:
VB_Noob
to which direction bumps?
which is 'total range'? A1:G114 or something?
is there always some unlocked cells?
Click to expand...

I don't have any preference on the direction it should bump as long as all the protected cells are not being selected. That way user will not be able to edit the value of those cells.
My original codes pumps the selection downward. Then it seems there are too many rows. Therefore it gives me the error message I think.

I want to protect all the cells in (F7:F114) and cells in (G7:G114). Some cells need to be protected and some need to be open for data entry in other columns (B, C, D and E).
 
VB_Noob
You unprotect those cells manually which could select.
! No need to protect sheet !
With this, You can select 'yellow range'.
 

Attachments

  • VB_Noob.xlsb
    32.6 KB · Views: 4
You can try this:
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set RngToProtect = Range("A1:A8,C1:C8,B1,B3,B6,B8,D1,D3:D4,E1,L1,E2:F2,E3:L3,L4,D6:E8,H5:I5,F4:F114,G5:G114")
If Not Intersect(Target, RngToProtect) Is Nothing Then
  Set NewSelection = Target
  Do
    Set NewSelection = NewSelection.Offset(1)
  Loop Until Intersect(NewSelection, RngToProtect) Is Nothing
  NewSelection.Select
End If
End Sub
which does something very similar to your code (except for the If Target.Address = "$F$4" And [$L$2].Value <> "Oph_Phase II_POC" Then Target.Offset(1, 0).Select which I'll leave you to re-incorporate.
Another diffference is the way it deals with multi-cell selections.

See attachment.

Sheet2 of the attached offers a different way; you simply unlock all cells, then lock the cells you don't want the user to select, then protect the sheet but not allowing the user to select locked cells. Observe behaviour when you press the Enter key, likewise the Tab key.
 

Attachments

  • Chandoo40068.xlsm
    17.9 KB · Views: 3
Last edited:
vletm said:
VB_Noob
You unprotect those cells manually which could select.
! No need to protect sheet !
With this, You can select 'yellow range'.
Click to expand...

I will not be able to use the lock cell feature that comes with Excel because the protected field contains calculation. Hence its value changes as the user enters data into other yellow field. I've tried and it doesn't work.

The array you provide still doesn't work for me. I am still able to select and edit the fields that I want to protect.
 
VB_Noob
Not able to use ... hmm? Why not?

Yellow ... as written ... You can select 'yellow range
... as well You can edit ... modify
... but other than 'yellow' which still has 'locked'-option
... can You select/edit those?

Upload Your file here,
then no need to guess
Your needs nor Your file!
 
VB_Noob said:
lock cell feature that comes with Excel because the protected field contains calculation. Hence its value changes as the user enters data into other yellow field.
Click to expand...
This is not true, this is exactly what cell protection is mostly about; preventing people from changing formulae, but still allowing the cell to calculate…
VB_Noob said:
I've tried and it doesn't work.
Click to expand...
You must have tried something else.
 
p45cal said:
You can try this:
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set RngToProtect = Range("A1:A8,C1:C8,B1,B3,B6,B8,D1,D3:D4,E1,L1,E2:F2,E3:L3,L4,D6:E8,H5:I5,F4:F114,G5:G114")
If Not Intersect(Target, RngToProtect) Is Nothing Then
  Set NewSelection = Target
  Do
    Set NewSelection = NewSelection.Offset(1)
  Loop Until Intersect(NewSelection, RngToProtect) Is Nothing
  NewSelection.Select
End If
End Sub
which does something very similar to your code (except for the If Target.Address = "$F$4" And [$L$2].Value <> "Oph_Phase II_POC" Then Target.Offset(1, 0).Select which I'll leave you to re-incorporate.
Another diffference is the way it deals with multi-cell selections.

See attachment.

Sheet2 of the attached offers a different way; you simply unlock all cells, then lock the cells you don't want the user to select, then protect the sheet but not allowing the user to select locked cells. Observe behaviour when you press the Enter key, likewise the Tab key.
Click to expand...


Hi,

I tried to add more cells and range in the code below and it gives me an error message. It seems it has a cap on how many cells can be added into it?
Run-Time Error '1004':
Method 'Range' of object'_Worksheet'failed'

>>> as written >>> use correct CODE -tags <<<

Code: 
Set RngToProtect = Range("A1:A114,C1:C48,C51:C54,C56:C80,C82,C85:C91,C93:C114,B1,B3,B6,B8:B11,B13,B21:B22,B26,B28,B30,B32:B33,B35:B37,B46,B48:B53,B55:B60,B65,B68,B74,B82,B91,B96,B101,B113,B114,D1,D3:D4,E1,L1,E2:F2,E3:L3,L4,D6:D18,D26:D28,D30:D37,D46:D55,D57:D58,D60:D61,H5:I5,F5:F114,G5:G114")
 
Last edited by a moderator:
VB_Noob said:
It seems it has a cap on how many cells can be added into it?
Click to expand...
Not a cap on the number of cells (there is a cap on the number of areas (not cells) (but that cap is way above what you need), but it seems a cap on the length of string in the definition (255 probably).
You can do the likes of this instead:
Code: 
Set RngToProtect = Range("A1:A114,C1:C48,C51:C54,C56:C80,C82,C85:C91,C93:C114,B1,B3,B6,B8:B11,B13,B21:B22,B26,B28,B30,B32:B33,B35:B37,B46,B48:B53,B55:B60,B65,B68,B74,B82,B91,B96,B101,B113,B114,D1,D3:D4,E1,L1,E2:F2,E3:L3,L4,D6:D18,D26:D28,D30:D37,D46:D55,D57:D58,D60:D61,H5:I5")
Set RngToProtect = Union(RngToProtect, Range("F5:F114,G5:G114"))
And note that the likes of "F5:F114,G5:G114" can be expressed "F5:G114"
 
p45cal said:
Not a cap on the number of cells (there is a cap on the number of areas (not cells) (but that cap is way above what you need), but it seems a cap on the length of string in the definition (255 probably).
You can do the likes of this instead:
Code: 
Set RngToProtect = Range("A1:A114,C1:C48,C51:C54,C56:C80,C82,C85:C91,C93:C114,B1,B3,B6,B8:B11,B13,B21:B22,B26,B28,B30,B32:B33,B35:B37,B46,B48:B53,B55:B60,B65,B68,B74,B82,B91,B96,B101,B113,B114,D1,D3:D4,E1,L1,E2:F2,E3:L3,L4,D6:D18,D26:D28,D30:D37,D46:D55,D57:D58,D60:D61,H5:I5")
Set RngToProtect = Union(RngToProtect, Range("F5:F114,G5:G114"))
And note that the likes of "F5:F114,G5:G114" can be expressed "F5:G114"
Click to expand...

I just created two more arrays to finish off rest of the cells in the sheet. Just had to create new RngToProtect for each array. Your array works. Thanks so much for your help!
 
You must log in or register to reply here.
Back
Top