editing a cell once

June 24, 2009 at 00:47:25
Specs: Windows XP
Hi all,

Why did the code below worked in the first worksheet and not in others.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.Protect Contents:=False
For Each cell In Range("C2:C15")
If cell <> "" Then cell.Locked = True
Next
ActiveSheet.Protect Contents:=True
End Sub


See More: editing a cell once

Report •


#1
June 24, 2009 at 05:13:45
Note the lines that say ActiveSheet.

The code, as written, only does it's thing to the whichever sheet is Active at the time of the save.

It can be modified to work on all sheets or just on specific sheets, but we would need to know your exact requirements.


Report •

#2
June 24, 2009 at 05:25:56
I have like 7 worksheets with the same cell numbers(i.e C2:C15) that i want to be locked after editing.Then in the 8th worksheet i need to lock different cells(i.e B2:B15).

And another thing i dont understand what you mean by activeSheet or how do I activate them.
I really appreciate your kind assisstance.

Thanks


Report •

#3
June 24, 2009 at 07:06:16
The ActiveSheet is the sheet currently being looked at/work on in Excel. In other words, whichever sheet tab is selected, assuming you haven't grouped any sheets.

re: I have like 7 worksheets

Do you have like 7 worksheets or do you have 7 worksheets?

Assuming you want to lock C2:C15 in the first 7 woksheets and B2:B15 in the 8th, try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Lock/Protect Sheets 1 - 7
 For shts = 1 To 7
  With Sheets(shts)
   .Protect Contents:=False
     For Each cell In .Range("C2:C15")
      If cell <> "" Then cell.Locked = True
     Next
   .Protect Contents:=True
  End With
 Next
'Lock/Protect Sheet 8
With Sheets(8)
   .Protect Contents:=False
     For Each cell In .Range("B2:B15")
      If cell <> "" Then cell.Locked = True
     Next
   .Protect Contents:=True
  End With
End Sub


Report •

Related Solutions


Ask Question