Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-31-2012, 08:28 AM
tinfanide tinfanide is offline Excel VBA: Worksheet_Change Cells.Protect Windows 7 64bit Excel VBA: Worksheet_Change Cells.Protect Office 2010 32bit
Expert
Excel VBA: Worksheet_Change Cells.Protect
 
Join Date: Aug 2011
Posts: 311
tinfanide is on a distinguished road
Default Excel VBA: Worksheet_Change Cells.Protect

Application Data.xlsm

I want the Date Column to be locked once the date is auto added (added once and stay forever)

In my codes, I've tried using Cells.Protect (deleted later) and at present using the codes (see in the file)

Could anyone figure out why the codes cannot protect Column Date values?
Reply With Quote
  #2  
Old 04-01-2012, 04:35 PM
davesexcel's Avatar
davesexcel davesexcel is offline Excel VBA: Worksheet_Change Cells.Protect Windows XP Excel VBA: Worksheet_Change Cells.Protect Office 2007
Novice
 
Join Date: Mar 2006
Posts: 7
davesexcel is on a distinguished road
Default

You would have to unlock all the cells first, and then lock the cells you want protected before you protect the sheet.
Reply With Quote
  #3  
Old 04-04-2012, 04:15 AM
tinfanide tinfanide is offline Excel VBA: Worksheet_Change Cells.Protect Windows 7 64bit Excel VBA: Worksheet_Change Cells.Protect Office 2010 32bit
Expert
Excel VBA: Worksheet_Change Cells.Protect
 
Join Date: Aug 2011
Posts: 311
tinfanide is on a distinguished road
Default

Quote:
Originally Posted by davesexcel View Post
You would have to unlock all the cells first, and then lock the cells you want protected before you protect the sheet.
Well, nothing to do with Cells.Locked = False (I guess this is what you meant) in the above file. Please kindly open the file and see. I've used

Code:
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
to protect the column to be protected. But it stops working when I try to delete the cell in that column.
Reply With Quote
  #4  
Old 04-05-2012, 03:19 AM
tinfanide tinfanide is offline Excel VBA: Worksheet_Change Cells.Protect Windows 7 64bit Excel VBA: Worksheet_Change Cells.Protect Office 2010 32bit
Expert
Excel VBA: Worksheet_Change Cells.Protect
 
Join Date: Aug 2011
Posts: 311
tinfanide is on a distinguished road
Default

Application Data.xlsm

Please take a look at the attached file.
I have tried some codes but it fails.
I just want the 'Date' Column to be protected without having the sheet protected (otherwise, new values cannot be added to the table)
Reply With Quote
  #5  
Old 04-06-2012, 06:21 PM
caholmes caholmes is offline Excel VBA: Worksheet_Change Cells.Protect Windows Vista Excel VBA: Worksheet_Change Cells.Protect Office 2007
Advanced Beginner
 
Join Date: Dec 2008
Location: Sydney, Australia
Posts: 54
caholmes is on a distinguished road
Default

Hi Tinfanide,

unfortunately you can't have your cake and eat it too. The only way to protect data on a sheet using the excel tools is to protect the sheet. This causes the locked property to be enforced for each cell. You could possibly protect and unprotect the sheet if it meets a condition using the Worksheet_SelectionChange event. It could look something like this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(Target) Then
ActiveSheet.Unprotect Password:="dog"
Else
ActiveSheet.Protect Password:="dog", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

Alternatively you could password protect a range (Allow uses to edit ranges) located on the Review Tab however you would still need to protect and unprotect the data based on a condition. This method may just give you a bit more control over the data.

I hope this helps!
Reply With Quote
  #6  
Old 04-06-2012, 10:37 PM
Catalin.B Catalin.B is offline Excel VBA: Worksheet_Change Cells.Protect Windows Vista Excel VBA: Worksheet_Change Cells.Protect Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Another approach i to protect all completed cells when closing the workbook, this way the code will not run at every change, slowing down work.
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow, LastColumn
Dim LastCell As String

Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="1234"

With ActiveSheet
        If WorksheetFunction.CountA(Cells) > 0 Then
          LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
        End If
  If WorksheetFunction.CountA(Cells) > 0 Then
    LastColumn = Cells.Find(What:="*", After:=[A1], _
       SearchOrder:=xlByColumns, _
       SearchDirection:=xlPrevious).Column
  End If
    
     LastCell = "A1:" & Split(Columns(LastColumn).Address, "$")(2) & LastRow
   
   .Range(LastCell).Cells.Locked = False

    For Each cell In .Range(LastCell).Cells
         If Not IsEmpty(cell) Then
             cell.Locked = True
         End If
    Next cell

ActiveSheet.Protect Password:="1234", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End With
End Sub
Reply With Quote
  #7  
Old 04-07-2012, 07:13 AM
tinfanide tinfanide is offline Excel VBA: Worksheet_Change Cells.Protect Windows 7 64bit Excel VBA: Worksheet_Change Cells.Protect Office 2010 32bit
Expert
Excel VBA: Worksheet_Change Cells.Protect
 
Join Date: Aug 2011
Posts: 311
tinfanide is on a distinguished road
Default

Thanks for all the input from you guys.
Really helpful and inspiring.
Here's the adapted version in my case:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Columns(2)) Is Nothing Then
    If Not IsEmpty(Cells(Target.Row, 2)) Then
        With Cells(Target.Row, 1)
            If .Value < Date Then GoTo Break
            .Value = Format(Date, "dd mmmm yyyy")
            .NumberFormat = "[$-809]dd mmmm yyyy;@"
        End With
    End If
End If

Break:
ActiveSheet.Unprotect
If Not Intersect(Target, Columns(1)) Is Nothing Then
    ActiveSheet.Cells.Locked = False
    Selection.Locked = True
    ActiveSheet.Protect
End If

End Sub
Book2.xlsm
Please look at the codes after the Break label in the attachment.
It works like
when any cells in Column 1 is selected / active,
they are protected.
But when it's not active,
other cells are free to edit.
Of course when you type a formula in areas except Column 1 and that formula will affect cells in Column 1, Column 1 will still be affected.
It's not perfect, but it achieves what I want, mostly.
Reply With Quote
  #8  
Old 04-08-2012, 10:43 AM
davesexcel's Avatar
davesexcel davesexcel is offline Excel VBA: Worksheet_Change Cells.Protect Windows XP Excel VBA: Worksheet_Change Cells.Protect Office 2007
Novice
 
Join Date: Mar 2006
Posts: 7
davesexcel is on a distinguished road
Default

Interesting,
Looks like you had to to unlock all the cells first, and then lock the cells you want protected before you protect the sheet.
Reply With Quote
  #9  
Old 04-08-2012, 09:58 PM
tinfanide tinfanide is offline Excel VBA: Worksheet_Change Cells.Protect Windows 7 64bit Excel VBA: Worksheet_Change Cells.Protect Office 2010 32bit
Expert
Excel VBA: Worksheet_Change Cells.Protect
 
Join Date: Aug 2011
Posts: 311
tinfanide is on a distinguished road
Default

Quote:
Originally Posted by davesexcel View Post
Interesting,
Looks like you had to to unlock all the cells first, and then lock the cells you want protected before you protect the sheet.
Yes, errors often occur when

Code:
Cells.Locked = False
Columns(1).Locked = True
ActiveSheet.Protect
Yes the first time it succeeds. But often I forgot to unprotect ActiveSheet before rerun the script
The second time when the sheet is protected, there should be an error when this code is run:

Code:
Cells.Locked = False
So I need:

Code:
If ActiveSheet.ProtectContents = True Then

ActiveSheet.Unprotect

End If

''''''''''''''''''''''
Cells.Locked = False
Columns(1).Locked = True
ActiveSheet.Protect
I've heard advanced coders always say
it's good practice first to check before you code.
So "If a condition occurs do this; if not, do that."
should be done always to avoid "strange" errors.
It may prevent me from always asking questions.
I can work it out sometimes if it is practised.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect / Lock Cells / Print Set-up? meggenm Excel 3 01-26-2012 09:57 PM
protect and unprotect in shared excel mark-gabb Excel 0 01-09-2012 07:30 PM
Excel 2010 Format cells to use as Gantt or Plan Kizzie Excel 0 12-13-2011 02:31 AM
Excel VBA: Worksheet_Change Cells.Protect password protect excel file? Dawg751 Excel 2 01-28-2010 06:23 AM
Excel VBA: Worksheet_Change Cells.Protect Importing individual cells from Excel to Word themangoagent Word 1 10-12-2009 08:14 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:20 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft