Help on Macros Unlocking Selected Locked Cells

[cbryan15]

Hi!

I would like to seek some help on macros unlocking selected locked cells using macro. By default, all cells in my worksheet are protected/locked because, I would like my colleague to use the macro feature of my file. But the problem is, I do not have any background on macros.

Can anybody help me with the code?

I appreciate your help. Thanks!

 

[Andrew Poulsom]

Have you tried recording a macro while doing it manually?

 

[cbryan15]

Have you tried recording a macro while doing it manually?

Hi Andrew!

I just tried recording it while doing it manually. It just gave me this in the module.

Sub bryan()
'
' bryan Macro
' unlock locked cells
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("T10:T14,T17:T19,AD10:AD11").Select
Range("AD10").Activate
Selection.Locked = False
Selection.FormulaHidden = False
End Sub

Actually, I do not have any background on macros or vba. That is why it would really be hard for me to do this. I am sorry for the trouble.

When I enter the shortcut to unlock it (the one entered - Ctrl Shift L), it just gives me this error - "Unable to set the Locked property of the Range class."

Thanks for the help!

 

[Andrew Poulsom]

You need to Unprotect the sheet to unlock cells. So turn on the macro recorder, unprotect the worksheet, unlock the cells and reprotect the worksheet. That should give you all the code you need unless the worksheet has a password.

 

[ps1]

Are the cells locked before running the macro, from what I gather you would require two pieces of code - one to unlock, and one to lock. Might make it easier if you could name your range
This unlocks locked cells

Sub unlock_cells()
Range("T10:T14,T17:T19,AD10:AD11").Select
Selection.Locked = False
End Sub

This locks cells

Sub lock_cells()
Range("T10:T14,T17:T19,AD10:AD11").Select
Selection.Locked = True
End Sub

 

[cbryan15]

Are the cells locked before running the macro, from what I gather you would require two pieces of code - one to unlock, and one to lock. Might make it easier if you could name your range
This unlocks locked cells

Sub unlock_cells()
Range("T10:T14,T17:T19,AD10:AD11").Select
Selection.Locked = False
End Sub

This locks cells

Sub lock_cells()
Range("T10:T14,T17:T19,AD10:AD11").Select
Selection.Locked = True
End Sub

Thanks Andrew and PS1! I am almost on the peak of that. But, this gives me a problem.

The file is originally protected/locked. This is the VBA code that I got by recording the macro:

Sub Macro1()
'
' Macro1 Macro
' unlock and lock
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Range("T10:T14,T17:T19,AD10:AD11").Select
Range("AD10").Activate
ActiveSheet.Unprotect
Selection.Locked = False
Selection.FormulaHidden = False
Range("X24").Select
ActiveWorkbook.Save
Range("T10:T14,T17:T19,AD10:AD11").Select
Range("AD10").Activate
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
Range("V26").Select
ActiveWorkbook.Save
End Sub


I would like to program this file as:

1. Originally locked.
2. When the command is entered (Ctrl Shift L), the only cells that will be unlocked are those given cells.
3. After saving, the file will be back to original state which is locked.

It really gives me a headache. I very thankful for your help and support. I am eager to learn this macro thing in excel. Thank you very much guys!

 

[ps1]

Something along the lines of..

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("a1").Select
Selection.Locked = True
End Sub

This would go in the THIS WORKBOOK section in vba. KIV though that the cells will lock when the workbook is saved.

 

[cbryan15]

Something along the lines of..

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("a1").Select
Selection.Locked = True
End Sub

This would go in the THIS WORKBOOK section in vba. KIV though that the cells will lock when the workbook is saved.

Hi guys! Thanks for the help. I am still figuring out how to do it. Here is the code that I used instead:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect Password:="Secret"
Else
Me.Unprotect Password:="Secret"
End If
End Sub

I am still exploring the possibilities. I appreciate your help and response. Thank you so much!

 

[Kamolga]

The easier way is to protect the sheets to users only (not to the macro) at workbook opening even:

Private Sub Workbook_Open()
'Protect sheets (but get macro running)
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        wSheet.Protect Password:="[COLOR=#0000ff]Secret[/COLOR]", UserInterFaceOnly:=True
Next wSheet
End Sub